SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SeanZ
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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;

    View solution in original post

    4 REPLIES 4
    Patrick
    Opal | Level 21

    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.

    SeanZ
    Obsidian | Level 7

    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.

    Patrick
    Opal | Level 21

    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;

    SeanZ
    Obsidian | Level 7

    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.

    sas-innovate-wordmark-2025-midnight.png

    Register Today!

    Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


    Register now!

    How to Concatenate Values

    Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

    Find more tutorials on the SAS Users YouTube channel.

    SAS Training: Just a Click Away

     Ready to level-up your skills? Choose your own adventure.

    Browse our catalog!

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