Macro Statements within proc sql

[Leo9]

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 ?

[ballardw]

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]

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.

[ChrisNZ]

No quotes when using strings in macro functions.

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