Nested loop in MACRO with the inner loop of various iterations

[Gigiwen]

Hi, Helpers, 

I am working on a macro loop with a nested loop. The inner loop does not have a fixed iterations. It achieves the following:

1. read CSV files one by one, with their path and name recorded in the dataset FileName, say one CSV file contains stock price from 2018 - 2024;

2. subsetting the dataset by a list of date;

3. estimating the volatility model using the subsetted dataset,  say the first subsetted dataset contains stock price between 2018 - 2019;

4. repeate step 2 & 3 until all dates are used; say the second subsetted dataset is 2019-2020, and then 3rd contains 2020-2021 and so on;

5. output the results- estimates from datasets 2018-2019, 2019-2020, 2020-2021 et al. ;

Basically, the umbrella  is macro estNcomPHI which is the outerloop with the number of iterations = num.  Inside it, there are two macro:

1. I am sure the macro getDateList is correct because removing the other macro, the code works.  %getDateList(oneStock,dateList);

2. TestRolling - which I suspect is wrong.  It is a loop with the number of iterations = num2. It is the length of the datelist. It is not a fixed number because stocks have various history, time since being listed.  I am not sure how num and num2 interact within the macro and loop in this case.  

Could you please help me? Thanks. 

proc sql ; select count(fileID) into :num from FileName; quit;

/* Loop: 1. Read data; 2. Esimate using rolling windows; 3. Output */

%macro estNcompPHI;

    %do i=1 %to #

        proc sql noprint;
            select FileName into :FileName from FileName where fileID=&i;
			select outFileName into :outFileName from FileName where fileID=&i;
        quit;

/* Step 1: Read Data */
data WORK.raw ;
    %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
    infile "&filename" delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
       informat snapped_at $20. ;
       informat price best32. ;
       format snapped_at $20. ;
       format price best12. ;
    input
                snapped_at $
                price
    ;
    if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
 run;

data oneStock;
set raw;
format date mmddyy10.;
date = mdy(put(scan(snapped_at,2,'-'),8.),put(scan(scan(snapped_at,3,'-'),1,' '),8.),put(scan(snapped_at,1,'-'),8.));
run;

proc sort data = oneStock; by date;run;

/* Step 2: get date list for subsetting the sample and estimate parameters using the samples of rolling windows  */
%getDateList(oneStock,dateList);

data dateList;
set dateList;
SampleID=_n_;
if _n_ < 5; * make a shorter list for code test;
run;

proc sql; select count(SampleID) into :num2 from DateList; quit;

%TestRolling(oneStock);

PROC EXPORT DATA= WORK.DateList 
            OUTFILE= "&outfilename" 
            DBMS=XLSX LABEL REPLACE ;
     PUTNAMES=YES;
	 Sheet = "DateList";
RUN;

PROC EXPORT DATA= WORK.outEst 
            OUTFILE= "&outfilename" 
            DBMS=XLSX LABEL REPLACE ;
     PUTNAMES=YES;
	 Sheet = "outEst";
RUN;

	%end;


%mend;

%estNcompPHI;



%macro TestRolling(raw);
    %do i= 1 %to &num2; 

        proc sql noprint;
			select Date format=9.0 into :Date from DateList where SampleID=&i;
        quit;

/* Step 1: chop the sample */

	data temp;
	set &raw;
	if date < &Date;
	run;

/* Step 2: estimation, for the purpose of discussion, skipped */

/* Step 3: Append results to outEst, outFitsum  */
        proc append data=Temp base=outEst force; run;

    %end;
%mend TestRolling;

[PaigeMiller]

2. TestRolling - which I suspect is wrong.  It is a loop with the number of iterations = num2. It is the length of the datelist. It is not a fixed number because stocks have various history, time since being listed.  I am not sure how num and num2 interact within the macro and loop in this case. 

This requires a lot more specific information, especially since we cannot run this macro to test it. Explain why you suspect it is wrong, in detail, including what you expect to happen, and what does happen. Also please share with us a portion of the data in data set &RAW that would illustrate the problem.

PS: saying "I suspect is wrong" or "it doesn't work" or any similar phrase, without explaining in detail, never gets us anywhere. From now on, explain what is wrong, telling us what the code is actually doing and telling us what the desired output is. Please don't make us ask repeatedly for this.

[Tom · Accepted solution]

The most likely source of your problem is these two lines

(or similar lines in some other macro you are using).

%macro estNcompPHI;
%do i=1 %to #

Or these two lines

%macro TestRolling(raw);
%do i= 1 %to &num2; 

If you consistently use &I as an iteration counter without making sure it is LOCAL to the macro being defined then when you call that macro in some environment where &I is being used for something else.

%do i=1 to 10;
  %estncompphi;
%end;

The %DO loop in the ESTNCOMPPHI macro will modify the macro variable I being used by the %DO loop in the code that is calling it.

Make sure to always define your local macro variables as LOCAL so that you can call the macro without worrying that it will change your current environment.

Add a %LOCAL statement. 

%macro estNcompPHI;
%local i;
%do i=1 %to #

And when you return from calling %estncompphi the current value of &I will be unchanged.

[Gigiwen]

Thanks...

If i change the index i into j for the inner loop, it works too. But your way is more tiday.