topic Re: Where statement could not recognize Macro ID list? in SAS Programming

Where statement could not recognize Macro ID list?

ybz12003 — Fri, 16 Aug 2024 18:23:43 GMT

Hello,

I created an ID list below and ran a where statement afterward. However, the error was shown in the log. Please help.

data CoV_HA_IDs;
      infile datalines dsd;
  input  ID : $50. ;
datalines;
1110,
1107,
1106,
	;
run;

proc sql;
	select quote(strip(ID)) into : CoV_HA_ID_list separated by ','  from CoV_HA_IDs;
quit;

%put &CoV_HA_ID_list;

%Macro aaa (Panel);
proc tabulate data=NREVSS251_vw_&st.;
  class eor_spec_id eot_test_ce1 eot_test_ce2 erv_rslt_ce2;
  table eor_spec_id, eot_test_ce1*eot_test_ce2* (erv_rslt_ce2 all)* (N);
  where eor_spec_id in (&&Panel._ID_list.);
run;
%mend aaa;
%aaa (CoV_HA);

SYMBOLGEN: Macro variable ST resolves to UT
MPRINT(AAA): proc tabulate data=NREVSS251_vw_UT;
MPRINT(AAA): class eor_spec_id eot_test_ce1 eot_test_ce2 erv_rslt_ce2;
MPRINT(AAA): table eor_spec_id, eot_test_ce1*eot_test_ce2* (erv_rslt_ce2 all)* (N);
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable PANEL resolves to CoV_HA
NOTE: Line generated by the macro variable "PANEL".
1 CoV_HA
------
22
76
MPRINT(AAA): where eor_spec_id in (CoV_HA._ID_list.);
ERROR: Syntax error while parsing WHERE clause.
MPRINT(AAA): run;

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant,
a datetime constant, a missing value, -.

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE TABULATE used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

MLOGIC(AAA): Ending execution.

Re: Where statement could not recognize Macro ID list?

Astounding — Fri, 16 Aug 2024 18:36:25 GMT

Not enough ampersands. Use &&& instead of &&

Re: Where statement could not recognize Macro ID list?

Tom — Fri, 16 Aug 2024 18:43:45 GMT

Why do you have two ampersands?

If the name of the macro variable is Panel_ID_list then you only want one.

If the name is being built from the value a macro variable named PANEL then you need three ampersands.

&&&Panel._ID_list

On the first pass the macro process will convert && to & and remind itself to make a second pass. On the first pass it will convert &panel. into the value of PANEL so that when it gets to the second pass the name of the macro variable will be COV_HA_ID_LIST.

PS You usually do NOT want to use STRIP() the way you have when moving character variable values into macro variables. If the actual value has leading spaces then STRIP() will remove them and then the resulting quotes string will no longer match the actual value. Just use TRIM() instead. You also can make the macro variable easier to work with by using space instead of comma as the delimiter. SAS code does not care which you use (or if you combine them).

select quote(trim(ID))
   into : CoV_HA_ID_list separated by ' '  
from CoV_HA_IDs
;

That way you could just define the macro to expect the actual list of values.

%macro aaa (id_list);
proc tabulate data=NREVSS251_vw_&st.;
  class eor_spec_id eot_test_ce1 eot_test_ce2 erv_rslt_ce2;
  table eor_spec_id, eot_test_ce1*eot_test_ce2* (erv_rslt_ce2 all)* (N);
  where eor_spec_id in (&id_list);
run;
%mend aaa;
%aaa(&CoV_HA_ID_LIST);