How to calculate date ranges and collapse costs based on ranges

[shasank]

Hi SAS Community,

I need your help with a problem.

Have
ID	Flagdate	Startdate	Cost
133	17-Jul-13	18-Jul-13	1307.77
133	17-Jul-13	18-Jul-13	96.6
133	17-Jul-13	21-Jul-13	55.74
133	17-Jul-13	22-Jul-13	169.86
133	17-Jul-13	22-Jul-13	58.16
133	17-Jul-13	22-Jul-13	30.51
133	17-Jul-13	28-Aug-13	34.93
133	17-Jul-13	30-Aug-13	0
133	17-Jul-13	30-Aug-13	0
133	17-Jul-13	5-Sep-13	34.93
133	17-Jul-13	20-Sep-13	3.34
133	17-Jul-13	23-Sep-13	83.48
134	18-Jul-13	21-Oct-13	13.11
135	19-Jul-13	25-Oct-13	0
136	20-Jul-13	25-Oct-13	11.5
137	21-Jul-13	25-Oct-13	10.48
138	22-Jul-13	25-Oct-13	217.07
139	23-Jul-13	25-Oct-13	83.48
140	24-Jul-13	25-Oct-13	0
141	25-Jul-13	25-Oct-13	49.59
142	26-Jul-13	25-Oct-13	63.2
143	27-Jul-13	27-Oct-13	20.18
144	28-Jul-13	4-Nov-13	11.49
145	29-Jul-13	7-Nov-13	29.45

The problem I need to solve is to compress the above to accumulate the costs for 30 day intervals by ID.

In other words, for first row Intervel_start  = Flag_date or and Intervel_end = Interval_start + 30.

For the second row, Interval_start = Previous row Interval_end + 1 and Interval_end = Interval_start+30 and so on for all rows. Once this is done the startdate and cost within these interval ranges need to be accumulated per each range for each ID. 

This is the mock output for the above data-set. I have tried using lag function to get the ranges but seems to not work. 

Want
ID	Flagdate	Intervel_Start	Intervel_End	Cum_Cost
133	17-Jul-13	17-Jul-13	16-Aug-13	1718.64
133	17-Jul-13	17-Aug-13	16-Sep-13	69.86
133	17-Jul-13	17-Sep-13	17-Oct-13	86.82
133	17-Jul-13	18-Oct-13	17-Nov-13	509.55

Thank you for your time and help.

Best,

SC.

[PGStats · Accepted solution]

You could do this with SQL, using SAS date interval functions:

proc sql;
select 
    id,
    flagDate,
    intnx("month", flagDate, 
        intck("month", flagDate, startDate, "continuous"), "same") 
        as intervalStart format=yymmdd10.,    
    intnx("month", flagDate, 
        intck("month", flagDate, startDate, "continuous")+1, "same")-1 
        as intervalEnd format=yymmdd10.,
    sum(cost) as totalCost
from have
group by id, flagDate, calculated intervalStart, calculated intervalEnd;
quit;

[andreas_lds]

Please post data in usable form, after more than 50 posts you should know that tables provide no information about the data-types you have.

After you did that, please explain the fourth row in your output-table. The data you showed in that row is not in your have-table.

[shasank]

Hi Andreas,
Thank you for taking time to reply. Sorry for not providing enough information. The sequence of transformation starts with calculating the interval_start and Interval_end from the flag date with 30 day interval and then if the the startdate is within this interval then those costs are accumulated in that want table row.