Equivalent strategy for macro DO loop when DO-list is too large for macro variable

[kschott]

Hi SAS community,

I am a new-ish SAS programmer and need some help.  I need to step through obs in one data set (MyIDList) and "do stuff" to data in another data set (MyDat) based on the value that I read.  Here is a simple made-up example of code that hopefully helps describes the situation:

DATA Work.MyDat;
INPUT ID VarA $ VarB $ VarC $1.;
CARDS;
20058529 AAB Z2345 Y
10058529 BAA D2345 N
30058529 ACC C2345 N
40058529 DDD B2345 Y
50058529 EAH A2345 Y
;
RUN;

DATA Work.MyIDList;
INPUT ID ;
CARDS;
20058529 
10058529 
30058529 
40058529 
50058529
;
RUN;

%MACRO MyMac(IDp);	
	DATA Work.MyOut; 
	SET Work.MyDat;	
	IF ID=&IDp. THEN VarD=VarC||VarA;
				ELSE VarD=VarB;
	RUN;
%MEND;

%LET MyMacList=
20058529 
10058529 
30058529 
40058529 
50058529
;

%macro MacForSmallList;
%do i = 1 %to %sysfunc(countw(&MyMacList));
    %let current_word = %scan(&MyMacList,&i);
    %PUT &current_word.;
    %MyMac(IDp=&current_word.)
%end;
%mend;

%MacForSmallList;

I need to run MyMac for every ID in MyIDList, BUT MyIDList (in reality) contains over 300,000 IDs.  If it only contained 1000 IDs I could make a macro variable like MyMacList and be done no problem, but 300,000 puts me way over the macro variable size limit (and beyond my current programming capabilities).  How do I do the same type of operation in this simple example for a big ID list?

Thanks,

Kevin

[PaigeMiller]

I think there are two ways around this

1. Dynamically break the macro variable up into groups of 1000 (or some other number) IDs then iterate through all groups
2. Use CALL EXECUTE to call %mymac on each row in a data set; CALL EXECUTE works on the data in your data sets and has no limitation on the total number of characters across all IDs, and no limitation on the number of rows in the data set.

I think #2 is the best choice here.

[kschott]

Thanks Paige.  I agree on your ordering 😀 -- #1 would be a bear.   Looks like I'll need to add CALL EXECUTE to my repertoire.

Best,

-K

[Tom]

There are many ways to generate code.

Easiest is the SAS data step.

filename code temp;
data _null_;
  set myidlist;
  file code;
  put '%MyMac(' id ')';
run;
%include code / source2;

And if the variable names in the dataset match the parameter name in the macro you can use power of the PUT statement to generate the parameter name into the code.  So say you had a macro that has NAME and AGE as input parameters.

filename code temp;
data _null_;
  set sashelp.class (obs=5);
  file code;
  put '%mymacro(' name= ',' age= ')' ;
run;

Which generates these lines into the CODE file.

%mymacro(Name=Alfred ,Age=14 )
%mymacro(Name=Alice ,Age=13 )
%mymacro(Name=Barbara ,Age=13 )
%mymacro(Name=Carol ,Age=14 )
%mymacro(Name=Henry ,Age=14 )