I want to generate multiple datasets dynamically and automatically using macro variable reference.
The basic idea is to use the elements of below list as iterators:
libname lsa 'C:\Users\mayqy015\Desktop\SAS Holder\loan_size_analysis'; %let codelist = _beijing _tianjin ... _hongkong _macao ;
These iterators will become the key part of dynamic referencing, and here below are the following codes:
%macro covid19trimming(code); data lsa.&code._COVID19_treated; set lsa.&code; if project_opentime_cn<20200101 then delete; if project_opentime_cn>20200331 then delete; run; %mend; %macro try; %let word_cnt=%sysfunc(countw(&codelist)); %do i = 1 %to &word_cnt; %let code=%qscan(%bquote(&codelist),&i); %covid19trimming(&code); %end; %mend; %try;
Unfortunately, even though the code can be run without mistakes, the SAS system cannot recognize my data step correctly. For example:
NOTE: There were 54752 observations read from the data set LSA._BEIJING. NOTE: The data set LSA._BEIJING has 54752 observations and 80 variables. NOTE: The data set WORK._COVID19_TREATED has 54752 observations and 80 variables. NOTE: DATA statement used (Total process time): real time 2.40 seconds cpu time 0.57 seconds
My opinion is that the SAS system recognizes that there are two datasets to be created, LSA._BEIJING and _COVID19_TREATED.
But I actually want to create only ONE dataset named 'LSA._BEIJING_COVID19_TREATED'.
Can any kind and generous experts help me out?
Thanks!
Maybe:
data lsa.%unquote(&code.)_COVID19_treated;
will help?
Bart
Maybe:
data lsa.%unquote(&code.)_COVID19_treated;
will help?
Bart
or maybe this:
%macro covid19trimming(code); %let code = &code.; /* to remove spaces */ data lsa.&code._COVID19_treated;
Yes! It works! But I still do not know why. I cannot quite understand SAS masking. Could you please explain a little bit? Or do you have some material that I can learn? Thank you so much for your generous help!
Hi,
My favourite reading about macroquotting: https://stats.idre.ucla.edu/wp-content/uploads/2016/02/bt185.pdf
In your code you used %qscan() which not only scanned but also macroquotted scanned text and some "quitting leftovers" left attached to value of &code.
If I may suggest something, the golden rule of macroquotting is:
"If you can do something with macroquoting or without macroquoting then do it without macroquoting",
so consider solution proposed by @Kurt_Bremser
All the best
Bart
Store your codes in a dataset, and use that to call the macro repeatedly:
data control;
input code $20.;
datalines;
_beijing
_tianjin
_hongkong
_macao
;
data _null_;
set control;
call execute('%nrstr(%covid19trimming(' !! trim(code) !! '))');
run;
Hi, @Kurt_Bremser
Your code is clean and tidy, but it is so far away from what I have learnt than I cannot understand. So could you please suggest some reference to videos and articles that I can learn your way?
Thank you in advance!
To understand Kurt's code I would suggest to start with reading about call execute() routine:
1) a blog: https://blogs.sas.com/content/sgf/2017/08/02/call-execute-for-sas-data-driven-programming/
2) an article: https://support.sas.com/resources/papers/proceedings/proceedings/sugi22/CODERS/PAPER70.PDF (by guru Ian Whitlock)
3) the doc: https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n1q1527d51eivsn1ob5hnz0yd1hx.htm&docse...
All the best
Bart
Apart from the documentation for CALL EXECUTE, which @yabwon already linked to, the use of %NRSTR is important. It prevents premature execution of macro code when the instructions are fed to the execution queue, and is usually a good idea when calling a macro with CALL EXECUTE.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.