topic Re: How to use a macro to do a rolling in SAS Programming

How to use a macro to do a rolling

SeanZ — Wed, 13 Nov 2013 09:36:24 GMT

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?

Re: How to use a macro to do a rolling

Patrick — Wed, 13 Nov 2013 09:54:45 GMT

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.

Re: How to use a macro to do a rolling

SeanZ — Thu, 14 Nov 2013 06:46:39 GMT

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.

Re: How to use a macro to do a rolling

Patrick — Thu, 14 Nov 2013 10:01:28 GMT

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;

Re: How to use a macro to do a rolling

SeanZ — Fri, 15 Nov 2013 05:28:00 GMT

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.