How to pass multiple values in single parameter Using Macros

[ashc25901]

This my macro cust. I need to pass multiple values for the macro variable state.

If I am calling a macro like this

%cust(dsn = Profile1, vars = Acct_ID Name Age Balance State, age_range= "18-40",  state = "NY");

It is working fine.

But if I pass multiple values in state. It is throwing an error. How to resolve this. Example below.

%cust(dsn = Profile1, vars = Acct_ID Name Age Balance State, age_range= "18-40",  state = "NY", "CA");

%macro cust(dsn = , vars = , age_range= , state=);
  %if &state = "NY" %then
  %do;
    proc print data = &dsn;
    var &vars;
    where State = &state and Age_Range = &age_range;
    title "Detail Listing of Account in &state and current date &sysdate";
    run;
   
    title "Total Balance in &state and in age &age_range current date &sysdate";
    proc sql;
    select %sysfunc(tranwrd(%sysfunc(compbl(&vars)),%str( ),%str(,))), sum(Balance) as Total_Balance
    from &dsn
    where State = &state and Age_Range = &age_range;
    quit;
   
 %end;
 
 %else %if &state = "CA" %then
 %do;
    proc print data = &dsn;
    var &vars;
    where State = &state and Age_Range = &age_range;
    title "Detail Listing of Account in &state and current date &sysdate";
    run;
   
    title "Total Balance in &state and in age &age_range current date &sysdate";
    proc sql;
    select %sysfunc(tranwrd(%sysfunc(compbl(&vars)),%str( ),%str(,))), sum(Balance) as Total_Balance
    from &dsn
    where State = &state and Age_Range = &age_range;
    quit;
   
 %end;
 
 %else %if &state = "WA" %then
 %do;
   proc print data = &dsn;
    var &vars;
    where State = &state and Age_Range = &age_range;
    title "Detail Listing of Account in &state and current date &sysdate";
    run;
   
    title "Total_Balance in &state and in age &age_range current date &sysdate";
    proc sql;
    select %sysfunc(tranwrd(%sysfunc(compbl(&vars)),%str( ),%str(,))), sum(Balance) as Total_Balance
    from &dsn
    where State = &state and Age_Range = &age_range;
    quit;
   
 %end;

%mend cust;

Thanks.

[PaigeMiller]

But if I pass multiple values in state. It is throwing an error. How to resolve this. Example below.

%cust(dsn = Profile1, vars = Acct_ID Name Age Balance State, age_range= "18-40",  state = "NY", "CA");

Help us out. Don't make us do the work you have already done. Tell us WHAT error. Or better yet, run your code with OPTIONS MPRINT; as the first line of the program and show us the SASLOG.

I'm guessing the call to the macro doesn't like STATE="NY","CA" because the comma indicates that is the end of the value of STATE, and whatever comes next is considered the next parameter in the call to the macro, but "CA" is not a valid parameter in this case. You could overcome this by using STATE="NY"%str(,)"CA"

However, this leads to other problems so I am not recommending you use STATE="NY"%str(,)"CA". When STATE="NY"%str(,)"CA" then none of your %IF conditions are met. Your %IF conditions are %IF &STATE="NY", and based on your inputs, the value of &STATE is "NY"%str(,)"CA" and this doesn't match when tested by %IF &STATE="NY". Likewise, it doesn't match any other of your %IF conditions. 

So please tell us what you want the macro to do when STATE="NY"%str(,)"CA", or when multiple states are included in the value of STATE.

[VDD]

as @PaigeMiller says post the error, but maybe this will work replace this

%cust(dsn = Profile1, vars = Acct_ID Name Age Balance State, age_range= "18-40",  state = "NY", "CA");

%macro cust(dsn = , vars = , age_range= , state=);

with this

%cust(dsn = Profile1, vars = Acct_ID Name Age Balance State, age_range= "18-40",  state in( "NY", "CA"));

%macro cust(dsn = , vars = , age_range= , state in);