DATA Step, Macro, Functions and more

Storing a list of values in a macro variable

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 123
Accepted Solution

Storing a list of values in a macro variable

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.


Accepted Solutions
Solution
‎06-07-2016 12:21 PM
Super User
Posts: 5,498

Re: Storing a list of values in a macro variable

Posted in reply to JediApprentice

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.

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

Re: Storing a list of values in a macro variable

Posted in reply to JediApprentice

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.

 

Super User
Posts: 11,343

Re: Storing a list of values in a macro variable

Posted in reply to JediApprentice

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.

Solution
‎06-07-2016 12:21 PM
Super User
Posts: 5,498

Re: Storing a list of values in a macro variable

Posted in reply to JediApprentice

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.

Frequent Contributor
Posts: 123

Re: Storing a list of values in a macro variable

Posted in reply to Astounding

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 288 views
  • 0 likes
  • 4 in conversation