BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

8 REPLIES 8
Tom
Super User Tom
Super User

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
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Calcite | Level 5

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
Rhodochrosite | Level 12

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
Super User Tom
Super User

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
Calcite | Level 5

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

Reeza
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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