Numeric Format Changing In Macro List

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Numeric Format Changing In Macro List

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!


Accepted Solutions
Solution
‎04-11-2018 06:31 PM
Super User
Posts: 6,785

Re: Numeric Format Changing In Macro List

Posted in reply to PatricktLeon

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;

View solution in original post


All Replies
Solution
‎04-11-2018 06:31 PM
Super User
Posts: 6,785

Re: Numeric Format Changing In Macro List

Posted in reply to PatricktLeon

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;

Occasional Contributor
Posts: 5

Re: Numeric Format Changing In Macro List

Posted in reply to Astounding

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!

Super User
Posts: 13,583

Re: Numeric Format Changing In Macro List

Posted in reply to PatricktLeon

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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 112 views
  • 0 likes
  • 3 in conversation