topic Re: how to add keyword parameters into a command/string in SAS Programming

how to add keyword parameters into a command/string

somebody — Sun, 28 Oct 2018 08:08:45 GMT

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.

Re: how to add keyword parameters into a command/string

Jagadishkatam — Sun, 28 Oct 2018 09:36:05 GMT

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;

Re: how to add keyword parameters into a command/string

LinusH — Sun, 28 Oct 2018 13:52:19 GMT

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

Re: how to add keyword parameters into a command/string

somebody — Mon, 29 Oct 2018 08:40:41 GMT

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.

Re: how to add keyword parameters into a command/string

somebody — Mon, 29 Oct 2018 08:56:04 GMT

can you please elaborate? for example, how do I use this in my example?

Re: how to add keyword parameters into a command/string

LinusH — Mon, 29 Oct 2018 19:50:17 GMT

Directly where you want it (untested, but that's your task).