SAS, converting numbers, from character format to numeric format, keeping all leading zeros, but length of numbers is NOT uniform

Question

I'm working in SAS EG and I'm trying to convert a column that's in character format to numeric format, EXACTLY as they appear in their character format. The numbers vary in length and some have one or two leading zeros.

If I do it one way, it gets rid of all leading zeros. Another way I tried, it adds leading zeros to the point that it's as long as the longest number in the column, e.g., a 9-digit number with one leading zero now has four leading zeros because the longest number in the column is 12 digits. (I hope this description makes sense).

I'm working in SAS EG. When I run proc contents, it tells me my existing variable is a character variable of length 26. It is blank for both 'format' and 'informat.'

I need to convert it so that a new column is a numeric variable, with length 8, and 'F12.' for 'format' and 'BEST12.' for 'informat,' as I plan to use it to match two data sets.

I created the following test data set in 'regular' SAS, but I'm not sure if fully recreates the issue I'm working on in SAS EG:

data have;
input mrn $1-12;
cards;
118283586928
003875807
038087875
0385709873
0038576830
;
run;

As you can see, I have one number that's 12 digits long (no leading zeros); two that are 9 digits (with one or two leading zeros); and two that are 10 digits (with one or two leading zeros).

Any help would be greatly appreciated.

Thanks

You say "I plan to use it (the char value converted to number value) to match two data sets". The number format is not considered when matching numeric values, so you probably can pull yourself out of this rabbit hole. — Richard
In the table with the character version of the values are the leading zeros significant? That is would '00012' and '12' mean two different cases? — Tom
Leading zeros have no meaning for numbers, they're just for display, what is the underlying issue? Where did you get this data from originally? — Reeza

Tom Tom · Accepted Answer · 2020-10-28T12:38:20

You cannot store 26 digit strings exactly as a number in SAS. SAS stores numbers as floating point values. You can use the CONSTANT() function to see the end of the contiguous integers that can be stored exactly.

73    data _null_;
74     x=constant('exactint');
75     put x= comma30.;
76    run;

x=9,007,199,254,740,992

So if you actually have values longer than 15 digits in the character variable you will not be able to convert them to numbers.

But if they are only 12 digits long then just convert the strings into numbers and compare the numbers.

proc sql;
  create table want as 
    select * 
    from a, b
    where a.mrn = input(b.mrn_string,32.)
  ;
quit;

SAS, converting numbers, from character format to numeric format, keeping all leading zeros, but length of numbers is NOT uniform

2 Answers