Active clients based on admission and discharge dates

[BigD]

Hello,

I have a set of admission dates and discharge dates for the past 10 years that I want to turn into a graph of active cases for each date over the time period of interest.

Have:

Admission_Date	Discharge_Date	id
22-Nov-11	26-Jul-13	1
17-Dec-12	01-May-14	2
30-Jan-13	07-Sep-13	3
05-Feb-13	08-Aug-13	4
18-Mar-13	16-Aug-13	5
10-May-13	04-Jun-14	6
23-May-13	20-Jan-14	7
12-Aug-13	31-Mar-14	8
etc

so client 1 was active between Nov 22, 2011 to Jul 26, 2013...

want (example)

Date	Active Clients (count)
22-Nov-11	20
23-Nov-11	18
24-Nov-11	18
25-Nov-11	18
26-Nov-11	20
27-Nov-11	20
etc.

Which can be easily graphed.

Thanks for you help.

Bruce

[Reeza · Accepted solution]

An easy coding but possibly generates a large data set:

data step1;

set have;

do date=admission_date to discharge_date;

     output;

end;

run;

proc freq data=step1;

table date/out=active_clients;

run;

[BigD]

That's pretty much it. I made the following modifications to get exactly what I needed.

data step1;

set have;

by id;

length_of_stay =discharge_date - admission_date;

by id;

do i= 1 to  length_of_stay by 1;;

count_date = ADMISSION_DATE + I;

OUTPUT;

end;

FORMAT count_date DATE9.;

run;

[DaveBirch]

If you are concerned about generating too large a data set, then modify Reeza's code to use a view:

data step1 / view=step1;  ** data step will be processed "in-line" when used without occupying excessive space **;

set have;

do date=admission_date to discharge_date;

     output;

end;

run;

proc freq data=step1;

table date/out=active_clients;

run;