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