topic Re: How to design dynamic data step to generate multiple datasets using macro varialbes? in SAS Programming

How to design dynamic data step to generate multiple datasets using macro varialbes?

applemonster — Wed, 29 Jul 2020 10:35:36 GMT

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!

Re: How to design dynamic data step to generate multiple datasets using macro varialbes?

yabwon — Wed, 29 Jul 2020 10:39:11 GMT

Maybe:

data lsa.%unquote(&code.)_COVID19_treated;

will help?

Bart

Re: How to design dynamic data step to generate multiple datasets using macro varialbes?

yabwon — Wed, 29 Jul 2020 10:40:28 GMT

or maybe this:

%macro covid19trimming(code);
%let code = &code.; /* to remove spaces */
data lsa.&code._COVID19_treated;

Re: How to design dynamic data step to generate multiple datasets using macro varialbes?

Kurt_Bremser — Wed, 29 Jul 2020 10:44:14 GMT

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;

Re: How to design dynamic data step to generate multiple datasets using macro varialbes?

applemonster — Wed, 29 Jul 2020 12:10:06 GMT

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!

Re: How to design dynamic data step to generate multiple datasets using macro varialbes?

yabwon — Wed, 29 Jul 2020 12:22:20 GMT

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

Re: How to design dynamic data step to generate multiple datasets using macro varialbes?

applemonster — Wed, 29 Jul 2020 12:34:06 GMT

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!

Re: How to design dynamic data step to generate multiple datasets using macro varialbes?

yabwon — Wed, 29 Jul 2020 13:17:28 GMT

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&docsetVersion=9.4&locale=en

All the best

Bart

Re: How to design dynamic data step to generate multiple datasets using macro varialbes?

Kurt_Bremser — Wed, 29 Jul 2020 14:23:55 GMT

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.