Changing the format of the data

[Nimish_Vaddiparti]

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

[Tom]

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?

[djbateman]

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.

[Nimish_Vaddiparti]

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

[Nimish_Vaddiparti]

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

[Doc_Duke]

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

[Tom]

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.

[Nimish_Vaddiparti]

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

[Reeza · Accepted solution]

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;