Well a pretty basic question for savvy data step folks. I have a finite set of character values in a variable and I want to run a procedure over and over cycling through the character list. So it will input the value into TITLE statement and WHERE statement. A generic version of my attempt is below. Ideally this code would run quantreg twice, once for variable = 'IM' and once for 'PD'
ods graphics on;
%let valuelist = 'IM' 'PD';
title "&valuelist Data";
proc quantreg data=res_costs ci=sparsity/iid algorithm=interior(tolerance=1.e-4);
where program = &valuelist
;
model charges = /
quantile = 0.25 to 0.75 by 0.25
plot=quantplot;
run;
%put &valuelist;
ods graphics off;
@novinosrin - Thx
The following code worked for me, I have no idea what the option part did, but activated something 🙂
options mlogic symbolgen mprint;
ods graphics on;
%macro h;
%local valuelist;
%let valuelist = IM PD;
%do i=1 %to %sysfunc(countw(&valuelist));
%let var=%scan(&valuelist,&i);
proc quantreg data=res_costs ci=sparsity/iid algorithm=interior(tolerance=1.e-4);
where program = "&var"
;
model charges = /
quantile = 0.25 to 0.75 by 0.25
plot=quantplot;
run;
title "&var";
%end;
%mend h;
%h
ods graphics off;
@ballardw - Thx. I was unable to get your suggestion to run. I added the ODS GRAPHICS ON for the full output I was looking for, but it only seemed to run the procedure for IM and not PD. Which theoretically your approach may have been more ideal for me since I would only need to insert the prefix part of code if I wanted to run comparable code later on in my program.
a quick pointers here with which i am sure you can modify
ods graphics on;
%macro h;
%local valuelist;
%let valuelist = IM PD;
title "&valuelist Data";
%do i=1 to %sysfunc(countw(&valuelist));
%let var=%scan(&valuelist,&i);
proc quantreg data=res_costs ci=sparsity/iid algorithm=interior(tolerance=1.e-4);
where program = "&var"
;
model charges = /
quantile = 0.25 to 0.75 by 0.25
plot=quantplot;
run;
%end;
%mend h;
%h
ods graphics off;
First of all, thanks for the reply. The code doesn't quite work (run). Probably my fault for not having an executable example. It first wanted a "%" on the 'to', then gave the following error message:
NOTE: No observations were selected from data set
WORK.RES_COSTS.
NOTE: The SAS System stopped processing this step because of
errors.
NOTE: PROCEDURE QUANTREG used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: No observations were selected from data set
WORK.RES_COSTS.
NOTE: The SAS System stopped processing this step because of
errors.
NOTE: PROCEDURE QUANTREG used (Total process time):
real time 0.00 seconds
cpu time 0.03 seconds
While staring at it I cannot think how to resolve the undefined issue. Perhaps, we can use the Sashelp.Heart set to get on common ground. An equivalent would be:
ods graphics on;
proc quantreg data=sashelp.heart ci=sparsity/iid algorithm=interior(tolerance=1.e-4);
where Sex = "Female"
/* "Male" */
;
model weight = /
quantile = 0.25 to 0.75 by 0.25
plot=quantplot;
run;
ods graphics off;
to to %to was a syntax error on my part. Sorry about that
Can you follow the logic with options mlogic symbolgen mprint; before the macro beginning that explains the processing of the program in the log
Your basic problem is attempting to use WHERE= with multiple values.
As you have found a statement like
Where program = 'A' 'B' ;
is a syntax error. The appropriate would be
Where program in ('A' 'B');
If you always have more than one value in your value list you could use:
Where program in (&valuelist);
But if you are going to mix single and double then you need to implement some logic to use either "Where =" or "where in".
Perhaps something like:
%macro wherelist (parm); %if %sysfunc(countw(&parm.)) > 1 %then %do; where program in (&parm.); %end; %else %do; where program = &parm.; %end; %mend; %let valuelist = 'IM' 'PD'; title "&valuelist Data"; proc quantreg data=res_costs ci=sparsity/iid algorithm=interior(tolerance=1.e-4); %wherelist ( &valuelist.); ; model charges = / quantile = 0.25 to 0.75 by 0.25 plot=quantplot; run;
Note that there are potential issues with your "valuelist" being blank or having mismatched quotes that will yield unexpected results.
You suggest a need to change from
where program in (&parm)
to
where program = &parm
when &parm has a single value instead of a list of values.
Is that for a performance-based reason?
@novinosrin - Thx
The following code worked for me, I have no idea what the option part did, but activated something 🙂
options mlogic symbolgen mprint;
ods graphics on;
%macro h;
%local valuelist;
%let valuelist = IM PD;
%do i=1 %to %sysfunc(countw(&valuelist));
%let var=%scan(&valuelist,&i);
proc quantreg data=res_costs ci=sparsity/iid algorithm=interior(tolerance=1.e-4);
where program = "&var"
;
model charges = /
quantile = 0.25 to 0.75 by 0.25
plot=quantplot;
run;
title "&var";
%end;
%mend h;
%h
ods graphics off;
@ballardw - Thx. I was unable to get your suggestion to run. I added the ODS GRAPHICS ON for the full output I was looking for, but it only seemed to run the procedure for IM and not PD. Which theoretically your approach may have been more ideal for me since I would only need to insert the prefix part of code if I wanted to run comparable code later on in my program.
@H I am glad that %macro h: worked. I will have a pint for you. Have fun and enjoy your day
@H wrote:
@novinosrin - Thx
@ballardw - Thx. I was unable to get your suggestion to run. I added the ODS GRAPHICS ON for the full output I was looking for, but it only seemed to run the procedure for IM and not PD. Which theoretically your approach may have been more ideal for me since I would only need to insert the prefix part of code if I wanted to run comparable code later on in my program.
From the accepted solution it was because your problem definition was not clear. You did not indicate you wanted, at least to me, that you wanted a single run for each value in the list. I though you wanted to run on records where any of the listed values of Program were present.
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.