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
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));
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.