BookmarkSubscribeRSS Feed
Leo9
Quartz | Level 8

Hi All,

 

I am trying to use following piece of code to conditionally execute select clause based on dataset.

 

proc sql ;

create table xyz as

%if %sysfunc(prxmatch("m/abc/oi",&stud)) > 0 %then %do ;

select a.*, cohort, regimen, dxloc as tumor

%end;

%else %if %sysfunc(prxmatch("m/yuh/oi",&stud)) > 0 %then %do;

select a.*, cohort, regimen, dxloc_std as tumor

%end;

 

 

I am getting this error

ERROR: Required operator not found in expression: prxmatch("m/abc/oi",&stud) > 0

 

 

what am I doing wrong here ?

3 REPLIES 3
ballardw
Super User

Where do you set the value of the macro variable &Stud?

What is the value of &stud?

And for extra added fun, often when using %sysfunc in a macro the parameter to a data step function that typically is in quotes no longer should be.

You might want to run this and then look in the log.

%macro dummy (stud);
   %let r=%sysfunc(prxmatch("m/abc/oi",&stud.));
   %put r is: &r.;
   %let q=%sysfunc(prxmatch(m/abc/oi,&stud.));
   %put q is: &q.;
%mend;

%dummy (abc);

 

If you are going to have a number of these with a common set of results like your a*, cohort, regimen I might factor those out to something like

proc sql ;
   create table xyz as
   select a.*, cohort, regimen
   %if <macro condition %then %do ;
      , dxloc as tumor
   %end;
   %else %if <macro condition>%then %do;
      , dxloc_std as tumor
   %end;
   from <rest of query>
   ;
quit;

That way when you end up with 10 or 15 conditions you don't have to copy the a*, cohort, regimen every time with the ever present copy error and makes it a bit easier to follow the program flow. Also it becomes easier to add multiple other variables to the select without having to rewrite the whole select for things like when "&stud is this value and &anothervar is a different value.

PaigeMiller
Diamond | Level 26

When you run macros, it is always good to turn on this option before you run the macro

 

options mprint mlogic symbolgen;

This adds useful information into your LOG that will help you diagnose the problem.

 

For your future benefit, do not show us the ERROR message detached from the code. Show us the LOG, including code, NOTES, ERRORs and WARNINGs, in the sequence that it is shown in the log. Paste the log as text into the window that appears when you click on the </> icon. Do not provide the log any other way.

--
Paige Miller
ChrisNZ
Tourmaline | Level 20

No quotes when using strings in macro functions.

 

%if %sysfunc(prxmatch(m/abc/oi,&stud)) > 0 %then %do ;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 3 replies
  • 448 views
  • 3 likes
  • 4 in conversation