Solved
Contributor
Posts: 74

How to get an accumulating time period?

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!

Accepted Solutions
Solution
‎10-29-2016 01:43 PM
Posts: 5,529

Re: How to get an accumulating time period?

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;``````
PG

All Replies
Super User
Posts: 23,728

Re: How to get an accumulating time period?

Sample data and sample output.

Solution
‎10-29-2016 01:43 PM
Posts: 5,529

Re: How to get an accumulating time period?

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;``````
PG
Contributor
Posts: 74

Re: How to get an accumulating time period?

Thank you very much!

☑ This topic is solved.