Fill in dates between incident dates, but how??

[wphupkes]

All,

I've got a table with multiple variables, an incident date and id's which can occur more than once. The incident date changes every time an incident occur. Like:

ID | Incident txt || Incident Date
1 A 01-01-2008
1 A 05-03-2008
1 B 08-03-2008
2 B 02-02-2008
2 F 03-04-2008
3 A 05-02-2008

Before, I had an table where on 1 row I've had an start and an end date, and with the statement: "do date = start_date to end_date by 1", it was extremeley simple to create a table with all the dates since between incidents for every ID (and later on, filling in the missing values which wasn't really a problem).

But now, when the 'end' date is on the following row, I have no idea how to create a table with all the dates between incidents per ID!

Anyone have any idea? Thanks in advance!

 

[kdt82]

Can you post an example of how you would like the output table to look, that would allow us to have a better idea of what you are looking for.

cheers

 

[ChrisW75]

I've been really busy lately, I'm going away on holiday in 1 week and have a bunch of stuff to complete before then, but I'm sure there's a better way of doing this.
My most recent idea is to use a "reporting" dataset which has the dates that you want to report, then joining that to the dataset containing the events.
You could use proc sql, and put a where clause on it to make sure that the event date is less than or equal to the reporting date. This should give you the dataset with each reporting date, and ALL events for all customers before the reporting date.
Next, order the data by reporting date, customer and event date.
Use by group processing to take only the last record, so that you dedupe the file and only have the most recent event before each reporting date.
Then, all you need to do is calculate the arrears for each record, group and transpose that arrears, then use proc summary to sum that up by report date.
I reckon that'll reduce your processing a fair amount.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[wphupkes]

Well, I've got this particular problem coverred with a sort of reversed 'LAG' construction (the problem was, I've got to had a field of the second row before the first was released...):

Data test2;
_n_ ++ 1;if _n_ <=n then do;
set test1 point = _n_;
date_end=incident_date;
id2=id;
end;
else date_end=.;
set test1 nobs=n;
format date_end yymmdd10.;
run;

AND

Data test3;
set test2;
F = id2-id;
If F ne 0 then date_end = "&sysdate."d;
run;

And then a datastep where the actual dates are placed between the begin and end dates (do date = date to date_end by 1).

Right now, I think it could done be more simple by merging the tables together, and let the second table begin reading 1 row lower... ;-) But ok, this looks better.

And now working on the rest! Thanks!