ERROR 22-322 / WARNING: Apparent symbolic reference CODELIST not resolved

[mles]

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]

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;

[Astounding · Accepted solution]

@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);

[mles]

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.

[Shmuel]

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;

[Tom]

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;