DATA Step, Macro, Functions and more

How to use a macro to do a rolling

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 122
Accepted Solution

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
Respected Advisor
Posts: 4,173

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=_Smiley Happy;
  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;

    View solution in original post


    All Replies
    Respected Advisor
    Posts: 4,173

    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
    Respected Advisor
    Posts: 4,173

    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=_Smiley Happy;
      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.

    Need further help from the community? Please ask a new question.

    Discussion stats
    • 4 replies
    • 1102 views
    • 0 likes
    • 2 in conversation