topic Re: Pass in varying number of values for query in a Macro in SAS Programming

Pass in varying number of values for query in a Macro

SmcGarrett — Tue, 12 Sep 2017 00:14:42 GMT

I'm working with a stored procedure that pulls data via an ODBC connection using proc sql. In particular, there are 3 columns that hold values for which I would like to shape my query in either the proq sql step or a secondary data step. The columns are basically hierachical, Company, then Product, then Campaign. Sometimes there Company, Product, and Campaign values are all the same while other companys might have X amount of products and Y amount of campaigns.

I'd like to be able to pass values to the macro via "%let" statements, but sometimes it is unnecessary and/or redundant to give values for Product and Campaign. Other times I might want to specify one product and many times I will want to specify 1 or more campaigns.

The goal is to automate the query such that we can add in varying numbers of macro parameters such that if no value for a particular column is passed the query simply does not use it as a condition.

For example:

%let Company = ABCD;

%let Product = ;

%let Campaign = ;

proc sql;
connect to odbc (dsn='DW' uid='**********' pwd='*************');
create table work.table as
select *
from connection to odbc (execute GETDATA
"&Company","&Product","&Campaign") ;
disconnect from odbc;
quit;

In the case above, I would simply want the query to return me all data associated with Company ABCD. Since no Product of Campaign was assigned (or it's blank/missing), I would not want the query to subset the data based on those columns.

Additionally:

%let Company = EFGH;

%let Product = ZYX;

%let Campaign = DEF FED;

In the case above, I would simply want the query to return me all data associated with the EFGH Company for their product ZYX and the campaigns for that product of DEF and FED.

It seems like it might be a better idea to execute this in a data step after the full data set has been brought in. Does anyone have an suggestions on dealing with optional macro parameters?

Thanks in advance.

Michael

Re: Pass in varying number of values for query in a Macro

Tom — Tue, 12 Sep 2017 02:44:43 GMT

I am not sure what you are asking at all.

Do you need help with passing empty values to a SAS macro?

Your code doesn't seem to involve a macro however, just a few macro variables. Are you actually writing a macro or just trying to use macro variables?

Also your code seems to be all about calling some type of stored procedure in a database. Do you know how that stored procedure works? If so then it should be simple to generate the proper syntax in a SAS macro.

For example if the stored procedure might want you to leave the parameter empty when the value is not specified.

execute GETDATA "&Company",,"&Campaign"

Or perhaps type the keyword NULL instead.

execute GETDATA "&Company",NULL,"&Campaign"

One way to do this is to just use a little %IF/%THEN processing to set the macro variables properly.

%if %length(&company) %then %let company=%sysfunc(quote(&company));
%if %length(&product) %then %let product=%sysfunc(quote(&product));
%if %length(&Campaign) %then %let Campaign=%sysfunc(quote(&Campaign));
...
execute GETDATA &Company,&product,&Campaign

If you don't actually have SAS macro then you can use IF/THEN processing in a data step instead to modify the macro variables. For example to replace COMPANY with either NULL or the value of company in quotes you do use this IF/THEN/ELSE

data _null_;
  if not missing(symget('company')) then call symputx('company',quote(symget('company'));
  else call symputx('company','null');
...

Re: Pass in varying number of values for query in a Macro

ShiroAmada — Tue, 12 Sep 2017 03:44:55 GMT

%macro param(company,product,campaign);
data _null_;
exec=
tranwrd(
catx(",",
quote(coalescec("&company","")),
quote(coalescec("&product","")),
quote(coalescec("&campaign",""))
),
'""',"")
run;
%mend;

%param(you,,);
%param(you,wanna,);
%param(you,wanna,bet);
%param(,,);
%param(,wanna,bet);
%param(,,bet);
%param(,wanna,);
%param(you,,bet);

Re: Pass in varying number of values for query in a Macro

SmcGarrett — Tue, 12 Sep 2017 16:08:03 GMT

Thanks for the response. This gives me a lot to work with.

The part that I may have been unclear about is that sometimes I may want to query more than one of any of these parameters. I will always ONLY want one Company but sometimes 2 or more products and most of the time 2 or more campaigns. Therefore, I'd like information on how to potentially pass a "list of values" through a macro parameter so that they data set created contains all observations for which the values are true. Assuming we call ALL the data through the stored procedure and then use a dataset to subset the data that we do want, are you familiar with how to pass a list of values through a macro parameter?

A very ugly way of doing then might be (We'd macro this at some point):

%let LIST_PRODUCTS = "P_One","P_Two","P_Three";

%let LIST_CAMPAIGNS = "C_One","C_Two","C_Three";

data want;

set have;

where Product in (&LIST_PRODUCTS) and Campaign in (&List_CAMPAIGNS);

run;

However, it seems very inefficient and like there could be a better way.

Re: Pass in varying number of values for query in a Macro

Tom — Tue, 12 Sep 2017 16:53:03 GMT

@SmcGarrett wrote:

A very ugly way of doing then might be (We'd macro this at some point):

%let LIST_PRODUCTS = "P_One","P_Two","P_Three";

%let LIST_CAMPAIGNS = "C_One","C_Two","C_Three";

data want;

set have;

where Product in (&LIST_PRODUCTS) and Campaign in (&List_CAMPAIGNS);

run;

However, it seems very inefficient and like there could be a better way.

I don't know about your stored process, but in pure SAS code passing a list is a good method. I wouldn't add the commas at it will just make the macro variable harder to deal with. The IN () operator in SAS does not need the commas.