Help using Base SAS procedures

Changing the format of the data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Changing the format of the data

Hi,

I'm currently working on huge datasets and hence i cannot change the format of a specific column as its not possible to open this in excel. As the column in question, has numeric data & this exceeds the length limit of 8. Therefore while importing the data, the numeric data is converted into scientific format. I've used the 'put' command to change the data to character. But i need the numeric data for the future. I tried by creating a new column and multiplying the value by 1.But this hasnt helped, as the data format in this column changes back to the scientific option.

Can you please suggest a way to ensure that i have numeric data and it does not get converted to scientific format?

Regards,

Nimish


Accepted Solutions
Solution
‎12-10-2012 01:46 PM
Super User
Posts: 17,912

Re: Changing the format of the data

Can you run proc contents on the dataset first and post the results here?

Also can you include the code you used to change the format on SAS side?

proc contents data=YOURDATASET;

run;

View solution in original post


All Replies
Super User
Super User
Posts: 6,502

Re: Changing the format of the data

If you are talking about a SAS dataset then you can set the format associated with a variable using a FORMAT statement (or using PROC DATASETS to modify the existing dataset).

If not then you need to explain what you are doing in more detail.  You mention Excel.  Does that mean your data is stored in an Excel workbook? If so which version of Excel.  Are you running SAS on a Windows platform?  Do you have SAS/Access to PC files licensed?

Regular Contributor
Posts: 220

Re: Changing the format of the data

I am not sure how to display it outside of scientific notation, but you can check that the data is numeric by right-clicking on the dataset icon in the Explorer window and selecting "Properties".  Choose the "Columns" tab, and you will see that "Type" will say "Number".

I tried changing the format to something like BEST32., but that did not seem to fix it.

Occasional Contributor
Posts: 18

Re: Changing the format of the data

Hi,

I cannot open the file in excel due to the size and hence i cannot change the format. And FYI - yes, changing the format in excel is possible using the properties (by right clicking on the cell and selecting format cells)

Cheers,

Nimish

Occasional Contributor
Posts: 18

Re: Changing the format of the data

Hi Tom,

I tried the format option, but that doesnt help. And yes, my PC sas can read xls, csv files etc. Initially, when i imported the data into SAS (using proc import, file type csv), the data was converted to scientific as the length exceeded the default length in SAS windows which is 8. And due to the size i cannot  change the format in the file itself using excel etc. Hence after importing the file, i first tried to change it to character to ensure that the complete value in each cell was retained. Now i need it back in the numeric format and hence my question was that - as since i have it in character format, can i change the data in the column to the numeric format? 

Is their a simpler way? I tried to create a new column by multiplying the character data with 1, but this changed the data back to the scientific format.

Thanking you in advance.

Cheers,

Nimish

Trusted Advisor
Posts: 2,113

Re: Changing the format of the data

Nimish,

I suspect that SAS is reading the data correctly and just displaying it in scientific notation because the display width is too narrow.  See

SAS(R) 9.3 Language Reference: Concepts, Second Edition

for how SAS handles numeric precision and

http://support.sas.com/techsup/technote/ts654.pdf

for the largest integer that can be stored exactly.

Doc Muhlbaier

Duke

Super User
Super User
Posts: 6,502

Re: Changing the format of the data

What is the value that you are seeing in scientific notation? 

What is the value in the original file?

Are you sure that SAS has read the value as a number? or has it converted the whole column (variable) to character during the import?

How are you printing (or otherwise displaying) that value?  Are you using a PROC PRINT? or perhaps a PUT statement in a DATA step?

Can you print the value in question using the HEX16. format? Then we could see the exact value that SAS has stored in the 8 byte floating point that it uses to store numbers.

What format is the original file? I would assume that it is a CSV file because of you statement about not being able to open it in Excel.

In that case why not just skip the PROC IMPORT step and write the data step code to read the file directly.  Then you can control the input format used.

Occasional Contributor
Posts: 18

Re: Changing the format of the data

@Tom additionally you also mentioned Proc datasets. Could you elaborate on this? As in, how do I go about using this?

Solution
‎12-10-2012 01:46 PM
Super User
Posts: 17,912

Re: Changing the format of the data

Can you run proc contents on the dataset first and post the results here?

Also can you include the code you used to change the format on SAS side?

proc contents data=YOURDATASET;

run;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 986 views
  • 6 likes
  • 5 in conversation