Macro - parameter of parameters?

[Solph]

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;

[Urban_Science · Accepted solution]

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 */

[ballardw]

---

@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.

[Urban_Science]

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

[Solph]

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

[Urban_Science]

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!

[Reeza]

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;