How to use a macro to do a rolling

Solved
Frequent Contributor
Posts: 122

How to use a macro to do a rolling

i have a date variable from 1970/01/01 to 2010/12/31. And stock returns for each day. What I want to do is that use one year as a rolling window. Use full year's returns to calculate a new variable and merge that variable as the next month's date after one year window. Specifically, I will achieve two tasks. The first one, use returns from 1970/01/01 to 1970/12/31 to calculate one value and use this value as 1971/01's value(monthly), next, use returns from 1970/02/01 to 1970/01/31 to calculate the second Value and use this value as 1979/02's value, and so on. The second task would be: use returns from 1970/01/01 to 1970/12/31 to get one value as 1971/01/01's value(daily), next use returns from 1970/01/02 to 1971/01/01 to get the second value for 1970/01/02's value, and so on. Also, not all dates are available. Weekends and holidays are not in the data set. That means I only have around 250 days in each year. How to setup a macro to do this?

Accepted Solutions
Solution
‎11-14-2013 05:01 AM
Posts: 4,736

Re: How to use a macro to do a rolling

The following code will need good testing to verify that I've got the logic 100% correct - but it should give you the idea.

If you want the average instead of the sum then divide the sum by the number on non-missing array elements:   n(of _amt(*))

data have;
format date date9.;
do date='01jan1970'd to '31dec2012'd;
amt=round(ranuni(1)*1000,0.01);
if weekday(date) not in (1,7) then output;
end;
run;

data want(drop=_;
set have;
by date;

retain _start_date;
format _start_date date9.;

if _n_=1 then _start_date=date;

/* create an array to hold 1 year worth of data */
/*  array _amt {12,31} 8 ;*/
/*  retain _amt;*/
array _amt {12,31} 8 _temporary_;

_lag_date=lag(date);

if intck('year',_start_date,date) < 1 then
do;
/* processing for the first year of data */
_amt[month(date),day(date)]=amt;
return;
end;

retain amt_yearsum_daily amt_yearsum_monthly;

amt_yearsum_daily=sum(of _amt

• );
•     /* first available date of month */
if intck('month',_lag_date,date)=1 then
do;
amt_yearsum_monthly=sum(of _amt

• );
/* special logic to deal with leap years */
if month(_lag_date)=2 and day(intnx('month',_lag_date,-12,'b'))=29 then call missing(_amt[2,29]);
end;

/* populate array element with the new amt value from the input data set for the next iteration */
_amt[month(date),day(date)]=amt;
•     /* for gaps in the date: set all array elements to missing for which there is no new data available */
do _i=_lag_date+1 to date-1;
call missing(_amt[month(_i),day(_i)]);
end;

run;

All Replies
Posts: 4,736

Re: How to use a macro to do a rolling

Why would you need a macro for this task?

There might be a few people here with enough subject matter expertise to fully understand what you want to achieve. For the rest of us: I suggest you provide a data step creating some sample data and then you show us how the result should look like.

Frequent Contributor
Posts: 122

Re: How to use a macro to do a rolling

I am not quite sure how to use data step to achieve this. The reason I want to use macro is because I need to roll the data (pick different sample for each loop). As I mentioned, for example, for the first loop I would like to grab one year's data from 1970/01/01 to 1970/12/31, after I get these year's data I can code and get one value, which is what I need. For the second loop, I would like to move forward to choose one year's data from 1970/02/01 to 1970/01/31 to calculate the second value. I would like to continue to move forward until sometime like 2010/1/1 to 2010/12/31 to get the last value. I want to create a variable and use macro to do a loop. something like %do i=&st %to &end, where st is starting point and end is the end point. I am not sure how to give an example since it's kind of complicated. But I hope you can understand what I want to achieve. I am not sure how to use data step. YOu can see that this will probably loop for more than 50 times.

Solution
‎11-14-2013 05:01 AM
Posts: 4,736

Re: How to use a macro to do a rolling

The following code will need good testing to verify that I've got the logic 100% correct - but it should give you the idea.

If you want the average instead of the sum then divide the sum by the number on non-missing array elements:   n(of _amt(*))

data have;
format date date9.;
do date='01jan1970'd to '31dec2012'd;
amt=round(ranuni(1)*1000,0.01);
if weekday(date) not in (1,7) then output;
end;
run;

data want(drop=_;
set have;
by date;

retain _start_date;
format _start_date date9.;

if _n_=1 then _start_date=date;

/* create an array to hold 1 year worth of data */
/*  array _amt {12,31} 8 ;*/
/*  retain _amt;*/
array _amt {12,31} 8 _temporary_;

_lag_date=lag(date);

if intck('year',_start_date,date) < 1 then
do;
/* processing for the first year of data */
_amt[month(date),day(date)]=amt;
return;
end;

retain amt_yearsum_daily amt_yearsum_monthly;

amt_yearsum_daily=sum(of _amt

• );
•     /* first available date of month */
if intck('month',_lag_date,date)=1 then
do;
amt_yearsum_monthly=sum(of _amt

• );
/* special logic to deal with leap years */
if month(_lag_date)=2 and day(intnx('month',_lag_date,-12,'b'))=29 then call missing(_amt[2,29]);
end;

/* populate array element with the new amt value from the input data set for the next iteration */
_amt[month(date),day(date)]=amt;
•     /* for gaps in the date: set all array elements to missing for which there is no new data available */
do _i=_lag_date+1 to date-1;
call missing(_amt[month(_i),day(_i)]);
end;

run;

Frequent Contributor
Posts: 122

Re: How to use a macro to do a rolling

Hi Patrick. Thank you very much for your answer. That is enormous for me. I need some time to figure it out. But I do appreciate a lot for your work.

🔒 This topic is solved and locked.