how to perform calculation in rolling time period?

Reply
New Contributor
Posts: 2

how to perform calculation in rolling time period?

Hello,

I'm really new to SAS and would appreciate any help I can get.  Based on multiple years data such as below

eff_date            pay_status

01Jan1992       A

02Jan1992       A

05Jan1992       A

05Jan1992       T

09Jan1992       T

12Jan1992       A

14Jan1992       T

thru

11Nov2016      A

14Nov2016      A

14Nov2016      T

 

How would I generate a report that shows rolling weekly calculations of ((count of T for previous 4 wks/count of A for previous 4 wks)*13) from a prompted date range?

 

Report to look something like below if date range is 04Oct2016 to 14Nov2016

Date                   Turnover

10Oct2016         15%

17Oct2016         22%

24Oct2016         19%

31Oct2016         21%

07Nov2016        18%

14Nov2016        16%      (count of T/count of A) for previous 4 wks (18Oct2016 to 14Nov2016) times 13

 

Thank you.

 

Super User
Posts: 790

Re: how to perform calculation in rolling time period?

[ Edited ]

Imagine you had an array NT with an element giving the number of T's for every date - not just every date in your file, but every date between 01jan1992 and 16nov2016.  And a simular array NA giving day-by-day counts of A's.

 

Then you could step through the two arrays, day-by-day, adding the T counts and A counts for the new days and subtracting the counts for 28 days prior.  Let's say you kept those rolling counts in SUMT and SUMA.    It would look something like this:

 

   sumT=0;  sumA=0;

   do date='01jan1992'd to today();

      sumT=sumT+nt{date} - nt{date-28};

      sumA=sumA+na{date}- na{date-28};

      .... other stuff here ...

   end;

 

** Now you want to output these counts and turnover only for mondays.  Well, you can use the WEEKDAY function, which (given a date value or date variable) returns a 1,2,3,... or 7.   Monday is a 2.   So up where the "other stuff here" appears, you can have

 

    if weekday(date)=2 then do;

      turnover=sumt/(sumA+sumT);

      output;

    end;

 

So now the main business is how to build the arrays NT and NA.  Given that the internal SAS date value for 01jan1960 is 0, that means that the internal date value of 01jan1992 is 11688, and the internal date value for today (14nov2016) is 20772.  So prior to the code above you need to declare arrays with those bounds  (actually I'll move the lower bound down by 28, so when I add NT and NA for 01jan1992 I can legally refer to a date 28 days prior to subtract).  That amounts to 9113 days.  So something like this should appear somewhere prior to the code above:

 

   array NT{11660:20772}  _temporary_  (9113*0);

   array NA{11660:20772}  _temporary_  (9113*0);

 

These are _TEMPORARY_ arrays which means that (1) they are not output to the data set, and (2) their values are not reset to missing with every new incoming observation.   The "(9113*0)" tells sas to initialize all 9,113 elements to zeroes.

 

So the remaing part is to read the entire data set to build these arrays.  That is done by:

 

   set have end=end_of_day;

   if pay_status='A' then na{eff_date}=na{eff_date}+1;  else

   if pay_status='T' then nt{eff_date}=nt{eff_date}+1;

 

The "end=end_of_day" tells sas to set a dummy variable when the record-in-hand is the end of the data set.  That will be a signal to generate output from the arrays.

 

Before looking at the code below,  there's one furter problem.  It's a pain to get the internal values for 01jan1992 and 14nov2016 only to put them in the array bounds.  So this macro code generate those numbers into macrovars which can be used as array bounds.

 

%let LBOUND=%eval(%sysfunc(inputn(01jan1992,date9.))  -28);

%let UBOUND=%sysfunc(today());

 

Here the code:

 

Notes:

 

  1. This code defines turnover as SUMT/(SUMA+SUMT);
  2. The monday reports includes the current MONDAY.  If you want monday reports NOT to include current monday, then move the "if weekday(date)=2" block to precede the update of SUMT and SUMA.

regards,

Mark

 

%let LBOUND=%eval(%sysfunc(inputn(01jan1992,date9.))-28);
%let UBOUND=%sysfunc(today());
%let NDAYS=%eval(&UBOUND + 1 - &LBOUND);

data want (keep=date sumt suma turnover);
  array na {&LBOUND:&UBOUND} _temporary (&ndays*0);
  array nt {&LBOUND:&UBOUND} _temporary (&ndays*0);  format date date9.;

  set have end=end_of_days;
  if pay_status='A' then na{eff_date}=na{eff_date}+1; else
  if pay_status='T' then nt{eff_date}=nt{eff_date}+1;

  if end_of_days then do;
    sumt=0;  suma=0;
    do date='01jan1992'd to today();
      sumt=sumt+nt{date}-nt{date-28};
      suma=suma+na{date}-na{date-28};
      if weekday{date}=2 then do;
        turnover=sumt/(suma+sumt);
        output;
      end;
    end;
  end;
run;

 

 

Additional editted notes:  This is a relatively simple structure, but if you have HUNDREDS of variables and millions of dates, building arrays of all vars for all dates could be resource intensive.  One would then just keep an array of 28 elements, one element for the current date, and one for each of the 27 preceding dates.  I.e. the rows would be recycled.  Less memory, but more programming.

 

MK

Grand Advisor
Posts: 9,596

Re: how to perform calculation in rolling time period?

If you don't have a big table, SQL is a good choice.

 

data have;
input eff_date     : date9. pay_status $;
format eff_date date9.;
cards;
01Jan1992       A
02Jan1992       A
05Jan1992       A
05Jan1992       T
09Jan1992       T
12Jan1992       A
14Jan1992       T
11Nov2016      A
14Nov2016      A
14Nov2016      T
;
proc sql;
select *,
(select count(*) from have where pay_status='T' 
and eff_date between intnx('week',a.eff_date,-3,'s') and a.eff_date)
/
(select count(*) from have where pay_status='A' 
and eff_date between intnx('week',a.eff_date,-3,'s') and a.eff_date)
as Turnover
 from have as a;
quit;
New Contributor
Posts: 2

Re: how to perform calculation in rolling time period?

Thank you mkeintz and ksharp! 

Ask a Question
Discussion stats
  • 3 replies
  • 203 views
  • 4 likes
  • 3 in conversation