topic Re: Macro - parameter of parameters? in SAS Programming

Macro - parameter of parameters?

Solph — Wed, 21 Aug 2019 21:19:24 GMT

Hi I've a simple SQL work to read in several data sets and keep selective variables from the data, so I used macro. Since the variable list is long and etc. I thought I'd spell out using %let before the macro. When calling in the macro %get, it apparently doesn't recognize these parameters. What do I do, make them system variables? Hope it makes sense. Thanks in advance.

%let yr=2017;
%let yr2=2018;

%let DB1var=v1 v2 v3 v4;
%let DB2var=x5 x7 x14 x5;
%let DB1name=mylib1.dt_A&fy;  *mylib1.dt_A2017;
%let DB2name=mylib2.dt_B&fy2;  *mylib2.dt_B2018;

%macro get (db=, var=, dbname=);
proc sql; create table &db as select a.*, b.*
		from have (where= (&db=1)  ) as a 
		left join &&dbname (keep=&dbvav) as b 
		on a.ID=b/OD; quit;
%mend;
%get (db=DB1,  var=DB1var, dbname= DB1name) ) ;
%get (db=DB2,  var=DB2var, dbname= DB1name) ) ;

/*It is how it should be executed if I'm doing it right*/
proc sql; create table DB1 as select b.*  
	from have (where= (DB1=1)  ) as a 
	left join DT_A2017 (keep=v1 v2 v3 v4) as b on a.ID=b.ID; 
quit;
/*What it actually turns out*/
proc sql; create table DB1 as select b.*  
	from have (where= (DB1=1)  ) as a 
	left join DB1name (keep=DB1var) as b on a.ID=b.ID; 
quit;

Re: Macro - parameter of parameters?

Urban_Science — Wed, 21 Aug 2019 22:00:46 GMT

Take a look at my changes. I have not tested it, but eyeballing it I think it should work.

%let yr=2017;
%let yr2=2018;

%let DB1var=v1 v2 v3 v4;
%let DB2var=x5 x7 x14 x5;
%let DB1name=mylib1.dt_A&yr;  *mylib1.dt_A2017; /* Changed &fy to &yr */
%let DB2name=mylib2.dt_B&yr2;  *mylib2.dt_B2018; /* Changed &fy2 to &yr2 */

%macro get (db=, var=, dbname=);
proc sql; create table &db as select a.*, b.*
		from have (where= (&db=1)  ) as a 
		left join &dbname (keep=ID &var) as b /* Added ID to the keep statement, Removed & from &&dbname, changed &dbvav to &var */
		on a.ID=b.ID; quit; /* Changed b/OD to b.ID (I think that was a typo) */
%mend;
%get (db=DB1,  var=&DB1var, dbname= &DB1name) ; /* Removed extra close parenthesis, Added & to value in var and dbName */
%get (db=DB2,  var=&DB2var, dbname= &DB2name) ; /* Removed extra close parenthesis, Added & to value in var and dbName, changed DB1name to DB2name */

Re: Macro - parameter of parameters?

Reeza — Wed, 21 Aug 2019 21:30:26 GMT

Not an answer to your question, but you can use MPRINT to see exactly what SAS is seeing.

Use the following options to help you debug macros. You can choose which ones you need;
MLOGIC is usually a last resort for me.

options mprint symbolgen mlogic;

Re: Macro - parameter of parameters?

ballardw — Wed, 21 Aug 2019 21:41:15 GMT

@Urban_Science wrote:

Take a look at my changes. I have not tested it, but eyeballing it I think it should work.
%let yr=2017;
%let yr2=2018;

%let DB1var=v1 v2 v3 v4;
%let DB2var=x5 x7 x14 x5;
%let DB1name=mylib1.dt_A&fy;  *mylib1.dt_A2017;
%let DB2name=mylib2.dt_B&fy2;  *mylib2.dt_B2018;

I think that you may want start by addressing the year component of these variables. I don't see a definition for the macro variables FY or FY2.

Re: Macro - parameter of parameters?

Urban_Science — Wed, 21 Aug 2019 21:44:00 GMT

Good catch, I've updated my response. Thanks!

Re: Macro - parameter of parameters?

Solph — Wed, 21 Aug 2019 21:55:01 GMT

Thanks. I worked. I thought I tried it and it didn't work. But apparently it works well. Again thanks.

Re: Macro - parameter of parameters?

Urban_Science — Wed, 21 Aug 2019 21:58:50 GMT

No problem! I made a couple of updates along the way. My latest update a few minutes ago was to include ID in the keep statement; otherwise, the column wouldn't exist for the join. @ballardw noticed the typo in the let statements, which I missed because I was too focused on what was going on in the macro. Team effort! Glad it works!