BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
H
Pyrite | Level 9 H
Pyrite | Level 9

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
H
Pyrite | Level 9 H
Pyrite | Level 9

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

View solution in original post

8 REPLIES 8
novinosrin
Tourmaline | Level 20

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;
H
Pyrite | Level 9 H
Pyrite | Level 9

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;

 

novinosrin
Tourmaline | Level 20

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

ballardw
Super User

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.

 

mkeintz
PROC Star

@ballardw

 

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?

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
H
Pyrite | Level 9 H
Pyrite | Level 9

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

novinosrin
Tourmaline | Level 20

@H I am glad that %macro h: worked. I will have a pint for you. Have fun and enjoy your day

ballardw
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1007 views
  • 1 like
  • 4 in conversation