BookmarkSubscribeRSS Feed
Catherine212
Calcite | Level 5

Dear SAS community,

 

I had a Macro to cut every 4000 records into a new macro which later I can use them as filters in my proc sql query, my macro is as following:

 

%Macro test(test);

%do i=1 %to &test;

 

proc sql;

select distinct "'"||ID||"'" into :group&i separated by ','

from BPLCheck.padrug (firstobs=%eval(4000*(&i-1)+1) obs=%eval(4000*&i) )

 

quit;

%end;

%mend test;

%test(2)

 

However, while I call the &group1 and &group 2 within my proc sql query in the where statement,

where (id in (&group1) or (&group2))

 

SAS can not recognize those two macros. What did I do wrong? How can I make this work.

 

Any help is greatly appreciated.

 

Thanks

1 REPLY 1
Astounding
PROC Star

When SQL creates macro variables, it creates them in the local symbol table.  But you need them to be global, so they will exist after %TEST finishes executing.  To do that, add this statement after %DO but before PROC SQL:

 

%global group&i;

 

Also note, the later WHERE statement is abbreviated.  In practice  you would be using:

 

where (id in (&group1) or id in (&group2));

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 438 views
  • 0 likes
  • 2 in conversation