Counting admitted patients (Length of Stay) by day - Array/do loops

[avbraga]

Hi all,

I am trying to count, by day, how many people are in a hospital, given the fact that I have the patient’s admission date (start_date), discharge date (end_date), and their length of stay.

Visually this is what I am looking for, the cells in yellow populated, so they can then be summarized by day.

Programmatically what I was thinking was:

1. have SAS create as many new "month_day" columns based on the min(start_date) and max(discharge_date). Those are represented in the attached picture as columns April_07, April_08, April_09, etc.
2. in a array/do loop of sorts, have sas look up the month and day of the start_date and of the end_date, and map those to the "month_day" columns with prefixes that match the start and end date.
3. Put a “1” on those columns so they can later be summarized by day.

I attempted a code that was something like the below, which doesn’t work, because each row will have a different “i” subscript range.

array days(*) april_07-april_20;

do i = begin_day to end_day;

      if days(i) = 0 then days(i) = 1;

end;

drop i;

******

Any way I can do the above programmatically? Any other ways that i am not seeing that can do this task programmatically?

Your help is appreciated.

Thanks,

Alex

[ballardw · Accepted solution]

---

@avbraga wrote:

Hi all,

I am trying to count, by day, how many people are in a hospital, given the fact that I have the patient’s admission date (start_date), discharge date (end_date), and their length of stay.

Visually this is what I am looking for, the cells in yellow populated, so they can then be summarized by day.

Programmatically what I was thinking was:

1. have SAS create as many new "month_day" columns based on the min(start_date) and max(discharge_date). Those are represented in the attached picture as columns April_07, April_08, April_09, etc.
2. in a array/do loop of sorts, have sas look up the month and day of the start_date and of the end_date, and map those to the "month_day" columns with prefixes that match the start and end date.
3. Put a “1” on those columns so they can later be summarized by day.

I attempted a code that was something like the below, which doesn’t work, because each row will have a different “i” subscript range.

array days(*) april_07-april_20;

do i = begin_day to end_day;

      if days(i) = 0 then days(i) = 1;

end;

drop i;

******

Any way I can do the above programmatically? Any other ways that i am not seeing that can do this task programmatically?

Your help is appreciated.

Thanks,

Alex

---

If you are looking to sum things by date across patients then you do NOT want to add that many columns.

Consider:

data example;
   input patientid $ begin_day : date9. end_day :date9.;
   format begin_day end_day date9.;
datalines ;
11111  01Jan2019 14Jan2019
2222   12Jan2019 15Jan2019
333    10Jan2019 12Jan2019
;
run;

data want;
   set example;
   do activedate = begin_day to end_day;
      output;
   end;
   format activedate date9.;
   label activedate = 'Day in hosptital';
run;

/* to get a data set with count of patients per calendar day*/
proc summary data=want nway;
   class activedate;
   output out=work.daycount (drop=_type_);
run;

The output creates one record for each date in the hospital.

If your begin and end dates are currently not SAS date values then that is the first step: make sure you have dates.

If you want to display the data, or a subset, in a wide format then something like:

Proc tabulate data=want;
   class patientid activedate;
   table patientid,
         activedate=' '*n=' '
         /misstext='0'
   ;
run;

Other variables would be carried along in the WANT data set so you could get summaries by any likely grouping you need.

[avbraga]

Hi ballardw,

This is a thing of beauty. Exactly what I needed without the convolution. Simple and elegant.

Thank you so much!

Alex

[PGStats]

Expand and count:

data stays;
input hosp $ patientID date_begin:date9. date_end:date9.;
datalines;
A 1 20jan2001 15mar2001
A 2 22jan2001 15feb2001
A 3 10jan2001 10mar2001
;

data days;
set stays;
do day = date_begin to date_end;
    output;
    end;
format day yymmdd10.;
keep hosp day;
run;

proc sql;
create table hospPop as
select hosp, day, count(*) as nbPatients
from days
group by hosp, day;
quit;

proc print data=hospPop noobs; run;

[avbraga]

Also works, PG. Thank you so much for your input. You guys are great!

Alex