SAS macro for creating multiple proq sql tables. Error: numeric opperand required

Question

I have a list of IDs that contain multiple "XO codes". I want to create a macro that will loop through these IDs and create a table for each using a where statement that corresponds to the appropriate XO code. EX:

%let ID_77= '35X02','35X04';
%let DnO_IDs= &ID_77; /intends to add more &ID_ numbers/

%macro loop;
   proc sql;
      %do k=1 %to %sysfunc(countw(&DnO_IDs_Ids.));
         %let ID= %scan(&DnO_IDs.,&k.);
         create table EP_&ID as
         select * from table
         where XO in ("&ID.") and AY>=(&CurrY-14);
      %end;
   quit;
%mend;
%loop;

I am receiving this error: ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: '35X04' ERROR: Argument 2 to macro function %SCAN is not a number. ERROR: The macro LOOP will stop executing.

Your list of values has quotes. So when you scan the list you are getting the quotes into the macro variable. So you are attempting to create a table named EP_'35X02'. You should either remove the quotes from the original list or remove them from the new ID macro variable. — Tom
What is &DnO_IDs_Ids. doing? That's an extra &ids compared to what you have in your %let statement. — Joe

Tom Tom · Accepted Answer · 2015-10-22T16:23:36

The quotes are an issue and should not be needed. Using comma as your delimiter can also cause trouble. It would work better to use spaces.

%let ID_77= 35X02 35X04;
%let DnO_IDs= &ID_77; /intends to add more &ID_ numbers/
%let CurrY=2015;

%macro loop;
%local k id ;
   proc sql;
%do k=1 %to %sysfunc(countw(&DnO_IDs));
  %let ID= %scan(&DnO_IDs,&k);
     create table EP_&ID as 
       select * from table
       where XO in ("&ID") and AY>=(&CurrY-14)
     ;
%end;
  quit;
%mend;
%loop;

If you do want to use the values as quoted comma separated lists then you need to modify your COUNTW() and %SCAN() function calls appropriately and add a call to DEQUOTE() to remove the quotes.

%let ID_77= '35X02','35X04';

... %sysfunc(countw(%superq(DnO_IDs),%str(,)));
  %let ID= %sysfunc(dequote(%scan(%superq(DnO_IDs),&k,%str(,))));

SAS macro for creating multiple proq sql tables. Error: numeric opperand required

2 Answers