Hi, I’m trying to run the following macro from this SAS support paper on flagging healthcare claims using SAS 9.4 (TS1M3)(https://support.sas.com/resources/papers/proceedings15/1340-2015.pdf 😞
%macro flag(codetype, flagtype);
proc sql;
select quote(code)
into :codelist
separated by "," from CODES
where
codetype="&codetype" and
flagtype="&flagtype";
quit;
data CLAIMS;
set CLAIMS;
if &codetype.1 in (&codelist)
then _&flagtype=1;
run;
%mend;
%flag(hcpcs, biopsy); *flag biopsy;
I have my datasets (CLAIMS and CODES) set up as specified in the paper with the only notable difference being my data contains multiple claims per patient; but I'm not sure this should matter.
I keep getting the following error when I try running the code and have tried the almost all suggestions for ERROR 22-322 listed in SAS community posts. None of them have resolved this error. Does anyone know what is wrong with this code?
SAS log after I run this macro:
2653 options MLOGIC;
2654 %flag(hcpcs, biopsy);
MLOGIC(FLAG): Beginning execution.
MLOGIC(FLAG): Parameter CODETYPE has value hcpcs
MLOGIC(FLAG): Parameter FLAGTYPE has value biopsy
SYMBOLGEN: Macro variable CODETYPE resolves to hcpcs
SYMBOLGEN: Macro variable FLAGTYPE resolves to biopsy
NOTE: No rows were selected.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds
SYMBOLGEN: Macro variable CODETYPE resolves to hcpcs
NOTE: Line generated by the invoked macro "FLAG".
2 (&codelist) then _flagtype=1; run;
-
22
76
WARNING: Apparent symbolic reference CODELIST not resolved.
ERROR 22-322: Syntax error, expecting one of the following: a quoted string,
a numeric constant, a datetime constant, a missing value, iterator, (.
ERROR 76-322: Syntax error, statement will be ignored.
Thank you for your help!
@gamotte has pointed you in the right direction. More specifically, note that your call to the macro will generate:
where codetype="hcpcs" and flagtype="biopsy";
It is likely that your CODES data set contains different values, such as "HCPCS" instead of "hcpcs" or "Biopsy" instead of "biopsy". SAS is looking for exact matches to the values in quotes. You can call the macro slightly differently, such as:
%flag(HCPCS, Biopsy);
There is no row in your dataset that satisfies the condition :
where codetype="&codetype" and flagtype="&flagtype";
As indicated in the log :
NOTE: No rows were selected.
You can add a test :
%if %symexist(codelist) %then %do;
...
%end;
%else %do;
%put WARNING: NO ROW SATISFIES THE CONDITION...
%end;
@gamotte has pointed you in the right direction. More specifically, note that your call to the macro will generate:
where codetype="hcpcs" and flagtype="biopsy";
It is likely that your CODES data set contains different values, such as "HCPCS" instead of "hcpcs" or "Biopsy" instead of "biopsy". SAS is looking for exact matches to the values in quotes. You can call the macro slightly differently, such as:
%flag(HCPCS, Biopsy);
Thank you! You and @gamotte's suggestions resolved this issue.
It seems to have been a matter of letter case in the CODES data set.
You can adapt the macro so that you will be independent of case letters:
%macro flag(codetype, flagtype);
%let codetyep = %upcase(&codetype); /* line added */
%let flagtype = %upcase(&flagtype); /* line added */
proc sql;
select quote(code)
into :codelist
separated by "," from CODES
where
codetype="&codetype" and
flagtype="&flagtype";
quit;
%let codelist = %upcase(&codelist); /* line added */
data CLAIMS;
set CLAIMS;
if &codetype.1 in (&codelist)
then _&flagtype=1;
run;
%mend;
The error is because your SQL query got no hits and so it did not create the macro variable.
You could code for that by setting some default value into the macro variable before the query. Or you could define the macro variable as LOCAL will insure that it exists.
If the cause was that your supplied values did not match the case of the data then you could update the macro slightly so that users do not need to provide exact case of text that you are searching for,
%macro flag(codetype, flagtype);
%local codelist;
proc sql;
select quote(trim(code))
into :codelist separated by ' '
from CODES
where upcase(codetype)=%upcaase("&codetype")
and upcase(flagtype)=%upcase("&flagtype")
;
quit;
data CLAIMS;
set CLAIMS;
if &codetype.1 in (&codelist) then _&flagtype=1;
run;
%mend flag;
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!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: