BookmarkSubscribeRSS Feed
lm12abh
Fluorite | Level 6

I am having an issue with converting a long numeric value (gt 16 dgitis) to a character variable without losing info.

The issue I am having is when exporting to excel I the value gets replaced with 0's at the end

4 REPLIES 4
Kurt_Bremser
Super User

Such long numbers are usually not numbers as such, but some kind of key. Keep them as character.

SAS provides precision up to 15 decimal digits (because of the 8 byte real format used for number storage).

ballardw
Super User

When did these values get turned into numeric values in SAS?

Likely culprits are incorrect reading of external files, proc import a possible candidate.

The original values should have been read and kept as character.

The precision in SAS is such that anything with more than 16 digits is very problematic as to whether they are stored correctly.

The largest value for an 8-bytel numeric is

9,007,199,254,740,992 which is 16 digits. Anything larger is problematic for correct storage and display

data example;
x=11111111111111111;
put x comma23.;
run; 

You best bet may be to go back in your process and ensure the value never gets treated as numeric at all. Once the conversion has been made the initial values may be corrupt.

 

Controlling variable types and lengths generally means reading from a text file with a character informat as long as the longest expected value.

Or if the value came from a database with longer integer values then create a version on the database case to character of appropriate length before reading with SAs.

 

lm12abh
Fluorite | Level 6

Thanks for this. The value is numerical from the source data and is either 13 or 16 digits. The issue occurs when I export the data to excel, the last 2 digits get replaced with trailing 0's. I need to keep all the information in excel and thus need to convert this to a string but I am having trouble doing this. Example:

Number                                 Desired string                     obtained string

1234567891234567              1234567891234567           1234567891234500

How do I get my desired string.

ballardw
Super User

@lm12abh wrote:

Thanks for this. The value is numerical from the source data and is either 13 or 16 digits. The issue occurs when I export the data to excel, the last 2 digits get replaced with trailing 0's. I need to keep all the information in excel and thus need to convert this to a string but I am having trouble doing this. Example:

Number                                 Desired string                     obtained string

1234567891234567              1234567891234567           1234567891234500

How do I get my desired string.


You might show exactly how your are getting the values into Excel.

Also check your Excel cell properties to see if display has been set to round or truncate values. Excel is known to set properties that you didn't expect.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 2693 views
  • 0 likes
  • 3 in conversation