topic Storing a list of values in a macro variable in SAS Programming

Storing a list of values in a macro variable

JediApprentice — Tue, 07 Jun 2016 15:55:08 GMT

Typically, I run my program with only one value assigned to a macro variable as such:

%let Client = 'BCBSFL';

Client is later referenced as such:

proc sql noprint;
	select distinct NDC_Num format = $quote13. into :Specialty separated by ','
		from pcom1.TRIESSENT_FEE_SCHEDULE (where= (Clnt_CD = &Client));
		run;

I want my code to be more versatile to handle a list of values (all Clients) rather than a single value. Note: there are many places where the macro variable Client is referenced, so I'd prefer not to simply have an in list wherever Client is referenced.

Re: Storing a list of values in a macro variable

RW9 — Tue, 07 Jun 2016 16:10:33 GMT

Personally my opinion is don't. Datasets are there to keep "data" items. For instance:

data params;
  val="Acura"; output;
  val="Audi"; output;
run;
proc sql;
  create table WANT as
  select  *
  from    SASHELP.CARS
  where   MAKE in (select VAL from PARAMS);
quit;

Your "list of data" is stored in a dataset, this is queried in the SQL clause as a subquery. Simple Base SAS code, easy to maintain/understand and expand upon, and you can use all Base SAS processing on your parameters.

Re: Storing a list of values in a macro variable

ballardw — Tue, 07 Jun 2016 16:14:36 GMT

If you don't want a "list" as used then perhaps a different variable that does contain the list of clients and then loop over them?

Or a dataset with the client values and then use a Datastep and call execute to generate the code for each value.

The first might be easier to adapt to your code.

Re: Storing a list of values in a macro variable

Astounding — Tue, 07 Jun 2016 16:15:56 GMT

Consider a slight change to your program, in the WHERE clause:

where = (Clnt_CD in (&client))

This works for either a single item or a list:

%let Client = 'BCBSFL';

%let Client = 'BCBSFL' 'BCBSNH';

Note that commas are not required.

The more difficult question is what else you want to do with &CLIENT. How else will your program be using it. You may need to learn a little more macro language to take a list of values and select one of them at a time.

Re: Storing a list of values in a macro variable

JediApprentice — Tue, 07 Jun 2016 16:21:43 GMT

Thank you, @Astounding. My program is only using Client in where conditions like in my example, so no need to loop through the list, so this solution seems to be the simplest. Thank you all for your help! Much appreciated.