topic Re: Numeric Format Changing In Macro List in SAS Programming

Numeric Format Changing In Macro List

PatricktLeon — Wed, 11 Apr 2018 22:18:52 GMT

Hello SAS Community,

I have a macro list that contains 14-digit numeric IDs. When I %put the list in the log I am not seeing the 14-digits, but rather:

NOTE: There were 29 observations read from the data set WORK.BIH.

WHERE MemID not in (1.9961106E13, 1.9961109E13, 1.9961109E13, 1.9961109E13, 1.9961109E13, 1.9961109E13, 1.9961109E13,1.9961109E13, 1.9961109E13, 1.9961109E13, 1.9961109E13, 1.996111E13, 1.996111E13, 1.996111E13, 1.996111E13, 1.996111E13,1.9961111E13,1.9961203E13, 1.9961203E13, 1.9961203E13, 1.9961203E13, 1.9961203E13, 1.9961203E13, 1.9961203E13, 1.9961203E13, 1.9961204E13...);

Can anyone advise why SAS is converting the ID to this format? I need to keep the 14 digit format in the macro list for further linking. Thanks!

Re: Numeric Format Changing In Macro List

Astounding — Wed, 11 Apr 2018 22:26:56 GMT

The problem occurred earlier, when creating the macro variable. You have to go back to that section of code to fix it.

Most likely, you had something like this:

proc sql;

select distinct id into : my_macrovar separated by ', ' from source_data;

quit;

Since macro variables are character strings, and ID is numeric, this forces SQL to make a numeric to character conversion. And SQL uses a 12-character format when doing that. You can control the process by changing the SELECT statement:

select distinct put(id, 14.) into : my_macrovar separated by ', ' from source_data;

Re: Numeric Format Changing In Macro List

PatricktLeon — Wed, 11 Apr 2018 22:31:29 GMT

Thank you so much for the swift response! This is the perfect solution for my issue. The previous format was 12-digit, so I never had a problem before. Thanks again!

Re: Numeric Format Changing In Macro List

ballardw — Wed, 11 Apr 2018 23:05:04 GMT

Since the variable has a name MEMID I am guessing it is some sort of an identifier. If so you should likely make it character and long enough to hold any expected growth 16 or 20 characters since most organizations do very little arithmetic with member identifiers. Then this issue would never arise and not run into potential issues with the precision of storage.

Example:

data junk;
   x = 12345678912345678;
   y = 12345678912345679;
run;
proc print data=junk;
format x y best32.;
run;

with a result of

Obs	x	y
1	12345678912345678	12345678912345680