So I am writing a macro that might have some keyword parameters. Here is an example of the action which I want to do. I want to take variables X and Y from dataset1 . But if there are some variables specified for the keyword parameters (e.g. vars=A B C), I want to take those variables too. How do I tell SAS to add these values
%macro merge_dataset(vars=);
proc sql;
create table output
as select distinct a.date, a.ID,
b.date, b.ID, b.X,b.Y, /*here is where I want to add "b.A, b.B, b.C,"/*
from dataset1 as a, dataset2 as b
where a.date=b.date
and a.ID=b.ID;
quit;
%mend;
%merge_dataset(vars=A B C);
One way to solve this is to put in a condition such as :
If %vars. ne then do; .......;.end;
else do; ......;end;
But this is too long.
you can simply try the b.*, here the * will consider all the variables from dataset2. So you don't actually require the macro.
but you cannot consider the same variables from dataset1 and dataset2 else we will get the warning. Here in the code since date and id are coming from dataset1 and if we are using b.* from dataset2, the same date and id variables will come from b as well hence we need to remove a.date and a.id, since they are in dataset2 as well.
proc sql;
create table output
as select distinct
b.*
from dataset1 as a, dataset2 as b
where a.date=b.date
and a.ID=b.ID;
quit;
I guess that the use case will not be limited to select * logic only.
I would try to use transtrn() function, to replace (or in this case insert table alias and commas).
Something like:
%sysfunc(transtrn(%str( &vars.),%str( ),%str( b.)))
can you please elaborate? for example, how do I use this in my example?
Directly where you want it (untested, but that's your task).
Yes, that works if I want all variables in b, but what if I don't want all variables? one solution is to create a DATA step before the SQL and create dataset b that contains all wanted variables.
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.