A character operand was found in the %IF condition where a numeric operand is required

[Jumboshrimps]

Below code works on it's own, producing  variable "EVENT" for every record. If pat_id has three records, then EVENT is 1 for first record of that PAT_ID, 2 for second record of that PAT_ID, 3 for third record of that PAT_ID.  Most pat_id's have less than 3 EVENT.  300,000 or more total records, 119,000 unique pat_id's.                                                                                                                                                                               

In this example, we will select OPIOIDS, (there are over forty different medication types, spread over 2,200 lines of code - same set of procedures was cut-and-pasted 40 times by the guy before me).                                                                                             

data OPIOID2 (Drop=n count);
format EVENT 6.;
set OPIOID1;
by PAT_ID;
n=_N_;
retain count;
if first.PAT_ID then count=n;
EVENT=n-count+1;
run;

Works fine, produces a much smaller data set with variable EVENT incremented plus 1 per set of PAT_ID records.

In my attempt to "macrotize" this code and just run this one macro 40 times, eliminating 2,200 lines of superfluous code,(this is the second step of four, the first attempt to make a macro of the proc sql runs fine, as do steps three - transpose the data, and four, make a big long string from  concatenating the variables in the transposed data,  passing just one parameter &MED.)                                                       

The macro code below generates the following error:   

" A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: first.PAT_ID"                                                                                                                                                                       

PAT_ID is a char variable of 18 len.  Some pat_id's are left padded with "0" - up to five of them. Half are are all numbers. Other half of the Pat_id's start with alpha characters.       I don't understand how the code above has no errors, but the code below does.  PAT_ID is the same variable.

%macro EVNT(MED);

data &MED.2 (Drop=n count);
LENGTH EVENT 6.;
set &MED.1;  /*macro for proc sql selection of the MED works fine.
by PAT_ID;
n=_N_;
retain count;
%if first.PAT_ID %then count=n;
EVENT=n-count+1;
%MEND;

%EVNT(OPIOID);

THX.

[maguiremq]

There's no need to use %IF-%THEN processing here. You just need to stick with IF-THEN.

%macro EVNT(MED);

data &MED.2 (Drop=n count);
LENGTH EVENT 6.;
set &MED.1;  /*macro for proc sql selection of the MED works fine.
by PAT_ID;
n=_N_;
retain count;
if first.PAT_ID then count=n;
EVENT=n-count+1;
%MEND;

 

%EVNT(OPIOID);

 Unless I'm seriously misunderstanding something. %IF-%THEN deals with macro processing and processing macro variables. You're not doing anything with the MED macro variable parameter other than setting it with the name OPIOID.

Taken from here:

Although they look similar, the %IF-%THEN/%ELSE statement and the IF-THEN/ELSE statement belong to two different languages. In general, %IF-%THEN/%ELSE statement, which is part of the SAS macro language, conditionally generates text. However, the IF-THEN/ELSE statement, which is part of the SAS language, conditionally executes SAS statements during DATA step execution.

[Jumboshrimps]

Yeah, that worked.  Not sure if I'll ever figure out the "%" and "." and "&" and "&&" syntaxes.                                                             But I've only been working in SAS 25 years......   Does help to turn on mprint mlogic symbolgen.

[PaigeMiller · Accepted solution]

My explanation:

IF-THEN-ELSE works on data set variables

%IF-%THEN-%ELSE works on macro variables, and doesn't know about data set variables.

So you cannot use %IF first.pat_id because %IF is looking for a macro variable and doesn't know what first.pat_id is.

When trying to debug macro issues, run this command

options mprint mlogic symbolgen;

before you run the macro. Then, show us the ENTIRE log for the macro, not just the error message and not selected portions of the log. Never separate error messages from the code in the log that produced the error.

[Tom]

Create two copies of your working data step for two different sets of input/output datasets.

ONE:

data OPIOID2 (Drop=n count);
format EVENT 6.;
set OPIOID1;
by PAT_ID;
n=_N_;
retain count;
if first.PAT_ID then count=n;
EVENT=n-count+1;
run;

TWO:

data NSAID2(Drop=n count);
format EVENT 6.;
set NSAID1;
by PAT_ID;
n=_N_;
retain count;
if first.PAT_ID then count=n;
EVENT=n-count+1;
run;

Compare with a text comparison tool.

Then when creating a macro only modify that parts that differ.