BookmarkSubscribeRSS Feed
kschott
Obsidian | Level 7

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

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
kschott
Obsidian | Level 7

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
Super User Tom
Super User

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 )

 

 

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 399 views
  • 1 like
  • 3 in conversation