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;
... View more