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));
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.