topic Re: Converting long number to character in New SAS User

Converting long number to character

lm12abh — Mon, 11 Mar 2019 10:50:45 GMT

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

Re: Converting long number to character

Kurt_Bremser — Mon, 11 Mar 2019 11:33:13 GMT

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).

Re: Converting long number to character

ballardw — Mon, 11 Mar 2019 17:07:57 GMT

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.

Re: Converting long number to character

lm12abh — Tue, 12 Mar 2019 08:17:53 GMT

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.

Re: Converting long number to character

ballardw — Tue, 12 Mar 2019 20:06:08 GMT

@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.