How to get an accumulating time period?

[zhangda]

Hello,

My dataset has three columns, one is apps, the second is approvals, and the third is wantdate, I want to calculate the total apps and total approvals, and then group by wantdate. But I want the date as an accumulating time period. for example,  if the startdate=01/01/2016, and enddate=02/27/2016. The wandate should cover the data as wantdate1, which is 01/01/2016, wantdate2, which is betweem 01/01/2016 and 01/02/2016, wantdate3, which is between 01/01/2016 and 01/03/2016,wantdate4, which is between 01/01/2016 and 01/04/2016.....and the last one is wantdate57, which is   between 01/01/2016 and 02/27/2016. Thanks a lot!

[Reeza]

Sample data and sample output.

[PGStats · Accepted solution]

I think you are looking for something like this:

%let startDate=01jan2016;
%let enddate=27feb2016;

data periods;
startdate = "&startdate."d;
do enddate = startdate to "&enddate."d;
    period + 1;
    output;
    end;
format startdate enddate yymmdd10.;
run;

proc sql;
create table want as
select b.period, sum(a.apps) as sumApps, sum(a.approval) as sumApprovals 
from have as a inner join 
    periods as b on a.wantdate between b.startdate and b.enddate
group by b.period;
quit;

[zhangda]

Thank you very much!