editing a macro variable in a data step do loop

[Huub]

Hi,

I am trying to create a loop which runs several programs multiple times using the %include function. These programs all use the macro variable CUST_ID and I would like to change this macro variable for every loop based on a list I specify. This is what I have so far but I can't manage to change the macro variable in the loop. Does anyone know how to do this?

/* specify list of values that have to be inserted into a macro variable*/
Data CUST_ID_LIST;
input CUST_ID;
datalines;
230419732
20476814
49500430
106808266
;
run;

/*calculate the length of the list to determine the number of iterations of the loop*/
proc sql;
Select count(*)
into: NO_CUST_ID
from CUST_ID_LIST;
run;

/* do the loop*/
data CUST_ID;
    do i = 1 to &NO_CUST_ID;
        set CUST_ID_LIST point = i;
		output CUST_ID ;
		%include "Program 1";
		%include "Program 2";
		%include "Program 3";
		%include "Program 4";
	end;
    stop;
run;

[PaigeMiller]

I would like to change this macro variable for every loop based on a list I specify.

Show us (a representative portion of) the list. Also, where is this list stored? (SAS data set, text file, piece of paper, other?)

[Huub]

Hi,

I create the list in the datastep mentioned in the code:

/* specify list of values that have to be inserted into a macro variable*/
Data CUST_ID_LIST;
input CUST_ID;
datalines;
230419732
20476814
49500430
106808266
;
run;

[PaigeMiller]

Then I'm lost. Your code takes this list, and computes the value of 4 for macro variable &NO_CUST_ID, and your code should work properly. What is the problem? Please be specific and detailed, instead of assuming we can all see the problem you are seeing.

[Huub]

Ah I'm sorry. I mean the macro variable in the do loop called CUST_ID. Not the variable &NO_CUST_ID. I use &NO_CUST_ID to determine the number of iterations, but i want to change variable CUST_ID for every iteration of the loop, where I replace the value of variable CUST_ID with the value of the list CUST_ID_LIST where the rownumber matches the iteration number. Is this clearer?

[PaigeMiller]

---

@Huub wrote:
Ah I'm sorry. I mean the macro variable in the do loop called CUST_ID. Not the variable &NO_CUST_ID. I use &NO_CUST_ID to determine the number of iterations, but i want to change variable CUST_ID for every iteration of the loop, where I replace the value of variable CUST_ID with the value of the list CUST_ID_LIST where the rownumber matches the iteration number. Is this clearer?

---

Not a bit clearer. I still don't know what is wrong with the code you have written, even though I specifically asked that question.

[AMSAS · Accepted solution]

I think this example is what you are looking for

First I have 2 simple programs that I'm going to %include (program1.sas & program2.sas):

data _null_ ;
	put "This is program 1 with CUSTID=&custId" ;
run ;

data _null_ ;
	put "This is program 2 with CUSTID=&custId" ;
run ; 

Then I have this

filename inc1 "<path>\program1.sas" ;
filename inc2 "<path>\program2.sas" ;

data customerIds ;
	infile cards ;
	input custId $ ;
	call symput("custId"!!left(putn(_n_,"2.")),custId) ;
	call symput("count",putn(_n_,"2.")) ;
cards ;
Cust#001
Cust#002
;

%macro repeat ;
	%do i=1 %to &count ;
		%let custId=&&custId&i ;
		%include inc1 ;
		%include inc2 ;
	%end ;
%mend repeat ;

%let custId=Customer#001 ;

data _null_ ;
	%repeat ;
run ;

Here's the log:

272  %let custId=Customer 001 ;
273
274  filename inc1 "<Path>\program1.sas" ;
275  filename inc2 "<Path>\program2.sas" ;
276
277  data customerIds ;
278      infile cards ;
279      input custId $ ;
280      call symput("custId"!!left(putn(_n_,"2.")),custId) ;
281      call symput("count",putn(_n_,"2.")) ;
282  cards ;

NOTE: The data set WORK.CUSTOMERIDS has 2 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


285  ;
286
287  %macro repeat ;
288      %do i=1 %to &count ;
289          %let custId=&&custId&i ;
290          %include inc1 ;
291          %include inc2 ;
292      %end ;
293  %mend repeat ;
294
295  %let custId=Customer#001 ;
296
297  data _null_ ;
298      %repeat ;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds



This is program 1 with CUSTID=Cust#001
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds



This is program 2 with CUSTID=Cust#001
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds



This is program 1 with CUSTID=Cust#002
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds



This is program 2 with CUSTID=Cust#002
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


317  run ;

[Tom]

If the number of values is small enough that they can fit into a single macro variable (65K bytes) then just modify your PROC SQL step.

Unless your %INCLUDE files all contain only statements that can run inside a the single data step you probably need to switch your DO loop to a %DO loop.

proc sql noprint;
select distinct cust_id 
  into :cust_id_list separated by '|'
  from cust_id_list
;
%let no_cust_id = &sqlobs;
quit;

%do index=1 to &no_cust_id;
  %let cust_id = %scan(&cust_id_list,&index,|);
		
%include "Program 1";
%include "Program 2";
%include "Program 3";
%include "Program 4";

%end;