SAS 8 & 9: Numbers getting turned into Notation
in [SAS]
|
From: Tom Abernathy on 14 Feb 2010 18:44 Steve - Sounds like a FORMAT issue. Just attach a format to the variable that is long enough to show the whole integer. - Tom On Feb 14, 6:05 pm, steve <tinker...(a)gmail.com> wrote: > Hi; > > I have a number of SAS tables that retrieved from MS SQLServer and > translated into *sas7bdat files via a DBMSCopy script ( yes, I know > there are better tools ). In these tables we have a unique ID for > each record. These IDs are integers. > > I noticed once these IDs start becoming large numbers they can no > longer be viewed in my SAS Viewer as numbers, but seem to be turned > into some kind of notation. > > For example 145563 might get turned into something like 15E4 > (roughly). > > I noticed a similar issue pasting columns of large integers into > Microsoft Excel. The problem went away when I set the number format > in Excel to be "text". > > I'm wondering if that could the issue with SAS Viewer. These tables > are generated/translated by DBMSCopy and some SAS scripts ( which I > don't have direct access too ). > > Can this problem be prevented by first converting my IDs to a string > data type before translating them into SAS ? > > Thanks much in advance > > Steve
From: Arthur Tabachneck on 14 Feb 2010 18:41 Steve, Since you are using SAS Viewer to look at the file, I'd try the following: click on view and change the setting from 'formatted' to 'unformatted'. Does that fix the problem? Art ------------ On Feb 14, 6:05 pm, steve <tinker...(a)gmail.com> wrote: > Hi; > > I have a number of SAS tables that retrieved from MS SQLServer and > translated into *sas7bdat files via a DBMSCopy script ( yes, I know > there are better tools ). In these tables we have a unique ID for > each record. These IDs are integers. > > I noticed once these IDs start becoming large numbers they can no > longer be viewed in my SAS Viewer as numbers, but seem to be turned > into some kind of notation. > > For example 145563 might get turned into something like 15E4 > (roughly). > > I noticed a similar issue pasting columns of large integers into > Microsoft Excel. The problem went away when I set the number format > in Excel to be "text". > > I'm wondering if that could the issue with SAS Viewer. These tables > are generated/translated by DBMSCopy and some SAS scripts ( which I > don't have direct access too ). > > Can this problem be prevented by first converting my IDs to a string > data type before translating them into SAS ? > > Thanks much in advance > > Steve
From: Savian on 14 Feb 2010 19:34 On Feb 14, 4:05 pm, steve <tinker...(a)gmail.com> wrote: > Hi; > > I have a number of SAS tables that retrieved from MS SQLServer and > translated into *sas7bdat files via a DBMSCopy script ( yes, I know > there are better tools ). In these tables we have a unique ID for > each record. These IDs are integers. > > I noticed once these IDs start becoming large numbers they can no > longer be viewed in my SAS Viewer as numbers, but seem to be turned > into some kind of notation. > > For example 145563 might get turned into something like 15E4 > (roughly). > > I noticed a similar issue pasting columns of large integers into > Microsoft Excel. The problem went away when I set the number format > in Excel to be "text". > > I'm wondering if that could the issue with SAS Viewer. These tables > are generated/translated by DBMSCopy and some SAS scripts ( which I > don't have direct access too ). > > Can this problem be prevented by first converting my IDs to a string > data type before translating them into SAS ? > > Thanks much in advance > > Steve Steve, What better tools are you referring to than DBMSCopy? I am not aware of hardly any that support the sas7bdat format. SPSS, WPS, DBMSCopy, StatTransfer. I think that is about it. If you know of any others, please let me know because I am very interested in this area. Alan
From: Mary on 16 Feb 2010 10:15 Steve, You may want to get away from taking SQL Server tables and transferring them into SAS data sets in order to give them to your statisticians. There are a number of ways for SAS programmers to access SQL Server tables directly, and thus you should start moving your statisticians in that direction. The ways are: 1. Pass-through SQL: executes via SAS proc SQL, users can send Microsoft SQL Server code across and get back the desired data (or do any MS SQL commmand) 2. SAS SQL: a SAS version of SQL, but users can still access Microsoft SQL database tables. 3. library engine with a data step- users can define where the MS SQL database is with an alias and then refer to that alias in their programs. Please write back if you'd like to see some examples of these. I don't believe that SAS Access to SQL Server is too expensive (and another option is SAS Access to ODBC), but I don't do pricing. -Mary --- tinker123(a)GMAIL.COM wrote: From: steve <tinker123(a)GMAIL.COM> To: SAS-L(a)LISTSERV.UGA.EDU Subject: Re: SAS 8 & 9: Numbers getting turned into Notation Date: Tue, 16 Feb 2010 06:36:49 -0800 On Feb 14, 7:34 pm, Savian <savian....(a)gmail.com> wrote: > What better tools are you referring to than DBMSCopy? I am not aware > of hardly any that support the sas7bdat format. SPSS, WPS, DBMSCopy, > StatTransfer. I think that is about it. If you know of any others, > please let me know because I am very interested in this area. Alan; I'm barely familiar with SAS. SAS is something the statisticians in our company use near exclusively. I'm just a first point of contact in assisting them with problems. My company is still moving its collected data from Microsoft SQLServer into *.sas7bdat files by first dropping the tables into an intermediary format and then programmatically generating a DBMSCopy script to translate the tables into *.sas7bdat files. We are now experimenting with a full version of SAS 9, with special extensions, that lets SAS contact Microsoft SQLServer directly and import the data directly into *sas7bdat files. This capacity has been around for a few years, but I understand that it is expensive. HTH Steve
From: steve on 16 Feb 2010 09:30 On Feb 14, 6:41 pm, art...(a)NETSCAPE.NET (Arthur Tabachneck) wrote: > Steve, > > Since you are using SAS Viewer to look at the file, I'd try the > following: click on view and change the setting from 'formatted' to > 'unformatted'. > > Does that fix the problem? It does. It turns my record IDs back into numbers, though instead of integers it tacks on a decimal point and some zeros. Not 100% correct, but a HUGE help as the people who use these tables can now ID the record they are referring to. I'm guessing we need to change something with how we use DBMSCopy to translate those integers into text. Thanks Steve
|
Next
|
Last
Pages: 1 2 Prev: SAS 8 & 9: Numbers getting turned into Notation Next: SAS/IntrNet not cleaning up sas.exe |