How to count and categorize within in two groupings

[sas_student1]

Hello Experts!!

I have a data set of hospital visits of multiple hospitals. In the data set I have client ID (IDs) with admission and discharge date by service types. IN=Inpatient, OU=Outpatient and ER=Emergency Room.

I want to know how many outpatient visits happen within 30 days of the inpatient visits discharge. I want to do a county by each hospital. So in the example below Hospital A would yield a 0 as the discharge from inpatient was on 1/5/19 and the admission to the outpatient after the inpatient visit was on 6/5/19, more than 30 days. Hospital B only ID 234 would count by not ID 345.

suggestions on this would really help as I want to count IDs within each hospital and counting with a condition of if the service is met.

I have about 50,000 records with many other services in the dataset, I just gave a simplified example below.

data have;
  length Hosp $1. ID $3. service $2. ;
  input Hosp ID service admission :mmddyy10. discharge :mmddyy10.;
  format admission mmddyy10. discharge mmddyy10.;

datalines;
A 123 IN 01/01/2019 01/05/2019
A 123 ER 04/01/2019 04/03/2019
A 123 OU 06/05/2019 06/30/2019
A 123 OU 08/01/2019 08/29/2019
A 123 ER 09/01/2019 09/05/2019
A 123 IN 10/01/2019 10/05/2019
B 234 IN 01/01/2019 01/05/2019
B 234 ER 04/01/2019 04/03/2019
B 234 IN 06/05/2019 06/30/2019
B 234 IN 08/01/2019 08/29/2019
B 234 OU 09/01/2019 09/05/2019
B 234 IN 10/01/2019 10/05/2019
B 345 IN 10/01/2019 10/05/2019
B 345 OU 12/15/2019 12/16/2019
C 567 IN 02/03/2019 02/05/2019
C 567 ER 02/07/2019 02/15/2019
C 567 OU 02/20/2019 03/01/2019
D 567 IN 04/01/2019 04/03/2019
D 567 OU 04/15/2019 04/16/2019
E 789 OU 05/01/2019 05/15/2019
E 789 IN 07/01/2019 07/15/2019
E 789 OU 08/01/2019 08/13/2019
;
run;

[mkeintz]

What if a patient has two episodes of distinct INPATIENT discharge followed by distinct outpatient visits within 30 days.  Is that a count of one or two?

[sas_student1]

First, I love how you thought of an instance that I forgot. So in this case we want to get the first instance of the inpatient visit and see if there was an outpatient visit within 30 days of the inpatient. If the patient had 3 separate instances I would want to know that, but I would count it as 1 as its the same patient. But my next question would have been how many times the 1 patient had three different episodes of inpatient to outpatient within in 30 days. So that would help. But it maybe two different steps, right?

[mkeintz · Accepted solution]

OK, so let's re-express the task to "for each INPATIENT discharge, how many OUTPATIENT admissions occur within 30 days.  So you would have 1 output record per inpatient record, with the variable N_RETURNS30 ranging from 0 to (theoretically) 30, as below.  Note, that instead of reading all the inpatient records first, setting the calendar array dummies, and then checking the outpatient records, this effectively reverses the order to

1. read all the outpatient records first
2. add 1 to each calendar array element corresponding to the admission date and 30 prior dates (now it's "add 1" instead of "set to 1", to account for multiple returns ).
3. read each inpatient record, and fetch the value of the calendar array element corresponding to the discharge date - it will have the number of outpatient admissions within 30 days.

data have;
  length Hosp $1. ID $3. service $2. ;
  input Hosp ID service admission :mmddyy10. discharge :mmddyy10.;
  format admission mmddyy10. discharge mmddyy10.;

datalines;
A 123 IN 01/01/2019 01/05/2019
A 123 ER 04/01/2019 04/03/2019
A 123 OU 06/05/2019 06/30/2019
A 123 OU 08/01/2019 08/29/2019
A 123 ER 09/01/2019 09/05/2019
A 123 IN 10/01/2019 10/05/2019
B 234 IN 01/01/2019 01/05/2019
B 234 ER 04/01/2019 04/03/2019
B 234 IN 06/05/2019 06/30/2019
B 234 IN 08/01/2019 08/29/2019
B 234 OU 09/01/2019 09/05/2019
B 234 IN 10/01/2019 10/05/2019
B 345 IN 10/01/2019 10/05/2019
B 345 OU 12/15/2019 12/16/2019
C 567 IN 02/03/2019 02/05/2019
C 567 ER 02/07/2019 02/15/2019
C 567 OU 02/20/2019 03/01/2019
C 567 OU 03/06/2019 03/10/2019  extra obs
D 567 IN 04/01/2019 04/03/2019
D 567 OU 04/15/2019 04/16/2019
E 789 OU 05/01/2019 05/15/2019
E 789 IN 07/01/2019 07/15/2019
E 789 OU 08/01/2019 08/13/2019
;

%let beg_date=01dec2018;
%let end_date=31dec2019;

data want3 (drop=d);
  set have (where=(service='OU'))
      have (where=(service='IN'));
  by hosp id;

  array calendar {%sysevalf("&beg_date"d):%sysevalf("&end_date"d)} _temporary_;
  if first.id then call missing(of calendar{*});

  if service='OU' then do d=admission-30 to admission;
    calendar{d}=sum(calendar{d},1);
  end;
  if service='IN';
  n_returns30=sum(0,calendar{discharge});
run;

Note I've added an extra outpatient record to create a 2nd admission within 30 days of the preceding inpatient discharge - i.e. n_returns30=2 for id 567, hospital C.

Also I change the calendar date range (to start 01dec2018 instead of 01jan2019), since I'm counting back from outpatient admissions instead of counting forward from inpatient discharge.

Edited additional note:  The only problem with this is if you have two inpatient records with discharge date less that 30 days apart.  Say you have 2 inpatient discharges within 15 days, following by an outpatient admission only 5 days later.  Then each of the inpatient visits would be attributed to have a return within 30 days.

[sas_student1]

@mkeintz Thank you!!! I think this one will work.

I was first trying your method 2 that created the want2 database, and I realized that I missed another point in the dataset. That is, if a patient has an inpatient visit at one hospital (say hosp A) but then has their outpatient visit at another hospital (say hospital B) I wanted to count that as well and give hospital A the credit.

In the example data set below I have a patient 123. He is discharged from Hospital A inpatient on 1/15/19 and admitted into the outpatient in hospital A on 1/25/19, here hospital A would get the credit. Then patient 123 was discharged from hospital B on 4/15/19 and was admitted into hospital C outpatient on 4/20/19, here I would want to give hospital B the credit.

I think running your new macro would work. I am still running the step right now to check. Unless you think that wont work?

data have;
  length Hosp $1. ID $3. service $2. ;
  input Hosp ID service admission :mmddyy10. discharge :mmddyy10.;
  format admission mmddyy10. discharge mmddyy10.;

datalines;
A 123 IN 01/05/2019 01/15/2019
A 123 OU 01/25/2019 01/30/2019
A 123 IN 02/05/2019 02/15/2019
A 123 ER 02/16/2018 02/18/2019
B 234 IN 04/05/2019 04/15/2019
B 234 ER 01/25/2019 01/30/2019
C 234 OU 04/30/2019 05/01/2019
;
run;

[mkeintz]

Because my earlier program only tracks within-hospital returns it will NOT work, since you now have told us that you need to count returns to ANY hospital within 30 days for a given ID.  And you want to attribute the return count to the hospital with the inpatient record, not the hospital with the outpatient record..

So before we go any further, please re-state your objective in detail, with a sample output data set that shows what results you expect for each of the situations you anticipate.

As an aside, although these forums focus on how to use SAS to accomplish various tasks, I think the most important skill one can develop here is learning how to clearly and comprehensively describe a task.  After that has been accomplished there's usually a lot of us that can help operationalizing the solution.  So give it another try.

[sas_student1]

@mkeintz Thank you! I will start a new thread though to keep it clean.

I usually do try to be more clear, unfortunately I forgot an element when I created this thread and only remembered it after.

But your help here was not in vain! I am sure I will be using your tips in the near future.

Thank you!

[mkeintz]

Assuming the answer to my earlier question is that a single patient can be counted once for EACH outpatient visit within 30 days of an inpatient discharge, then  (btw thank you for providing a working DATA step):

data have;
  length Hosp $1. ID $3. service $2. ;
  input Hosp ID service admission :mmddyy10. discharge :mmddyy10.;
  format admission mmddyy10. discharge mmddyy10.;

datalines;
A 123 IN 01/01/2019 01/05/2019
A 123 ER 04/01/2019 04/03/2019
A 123 OU 06/05/2019 06/30/2019
A 123 OU 08/01/2019 08/29/2019
A 123 ER 09/01/2019 09/05/2019
A 123 IN 10/01/2019 10/05/2019
B 234 IN 01/01/2019 01/05/2019
B 234 ER 04/01/2019 04/03/2019
B 234 IN 06/05/2019 06/30/2019
B 234 IN 08/01/2019 08/29/2019
B 234 OU 09/01/2019 09/05/2019
B 234 IN 10/01/2019 10/05/2019
B 345 IN 10/01/2019 10/05/2019
B 345 OU 12/15/2019 12/16/2019
C 567 IN 02/03/2019 02/05/2019
C 567 ER 02/07/2019 02/15/2019
C 567 OU 02/20/2019 03/01/2019
D 567 IN 04/01/2019 04/03/2019
D 567 OU 04/15/2019 04/16/2019
E 789 OU 05/01/2019 05/15/2019
E 789 IN 07/01/2019 07/15/2019
E 789 OU 08/01/2019 08/13/2019
;

%let beg_date=01jan2019;
%let end_date=31dec2019;

data want (keep=hosp n_returns30);

  set have (where=(service='IN'))
      have (where=(service='OU'));
  by hosp id;

  array calendar {%sysevalf("&beg_date"d):%sysevalf("&end_date"d)} _temporary_;

  if first.id then do;
     call missing (of calendar{*});
     if first.hosp then n_returns30=0;
  end;

  if service='IN' then do d=discharge to discharge+30;
    calendar{d}=1;
  end;
  else if calendar{admission}=1 then n_returns30+1;
  if last.hosp;
run;

This program creates a CALENDAR array covering each date from 01jan2019 through 31dec2019.  It is reset to missing at the start of each ID.  All the ID's INPATIENT records are read first, and each generates a series of 31 values of 1 in the array (from inpatient discharge date through another 30 days).  Then all the OUTPATIENT records are read and their admission dates are checked for a value of 1 in the array, which triggers an increment in the N_RETURNS30 count.

Just note that if a single inpatient discharge is followed by 2 separate outpatient admissions within 30 days, that counts as two.  If that's not what you want the program can be tweaked.  Or if a single patient has two separate inpatient discharges, each followed by an outpatient admission within 30 days, that also counts as 2.

BTW, here's a possibly more interesting program which carries forward the date information of the inpatient record to the outpatient record.  I.e. it outputs each outpatient record, with two new variables inpatient admission and discharge dates.  The latter are set to missing if the inpatient discharge is "stale", i.e. more than 30 days old.   And if you didn't bother resetting stale dates to missing, you would have all you need to get the complete distribution of days between outpatient records and most recent inpatient record:

data want2;
  set have (keep=hosp id service admission where=(service='IN'))
      have (where=(service='OU'));
  by hosp id admission;

  if service='IN' then set have (where=(service='IN') rename=(admission=inpat_admission discharge=inpat_discharge));
  if service='OU';
  if admission-30>inpat_discharge then call missing(of inpat_:);
  output;
  if last.id then call missing(of _all_);
run;