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-2024.png

    Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

    Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

     

    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.

    Click image to register for webinarClick image to register for webinar

    Classroom Training Available!

    Select SAS Training centers are offering in-person courses. View upcoming courses for:

    View all other training opportunities.

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