Find first office visit after hospital admission.

[mikemangini]

SAS Community,

I am trying to solve a merging problem. Data set Have1 is a table with hospital admission and discharge dates. Have2 is a table of outpatient office visit dates.

What I want to do, is merge the two in such a way:

For each row where a person/discharge date on Have1, join the date of the first subsequent office visit from Have2. A patient may have several, but I just want to add the first.

A second component that is a bit tricky, is if a person is readmitted to the hospital before they have an outpatient office visit, then the Soonest Follow Up_ Office Visit Date should be left blank, and a flag for readmission set to "Y". In the example below, the patient was discharged on Sept 3. The first subsequent office visit was on Sep 20, however an admission occured prior to this office visit on Sept 5. So in this case I don't want to populate the Soonest Follow Up field, since the person didn't follow up because they were back in the hospital.

Any help is greatly appreciated. Thank you.

Have 1
Person	Admit _date	Discharge Date
A	30APR2015	01MAY2015
A	02SEPT2015	03SEPT2015
A	05SEPT2015	07SEPT2015
A	11NOV2015	12NOV2015
A	19DEC2015	21DEC2015

Have 2
Person	Office Visit Date
A	01JAN2015
A	02JUNE2015
A	15JUL2015
A	20SEPT2015
A	20NOV2015
A	05DEC2015

Want
Person	Admit _date	Discharge Date	Soonest Follow Up_ Office Visit Date	Admitted Prior to Office Visit
A	30APR2015	01MAY2015	02JUNE2015	N	/*June 2 was the first office visit that occurred after this discharge*/
A	02SEPT2015	03SEPT2015		Y	/*The patient was readmitted before the first most recent office visit. No date populated, admitted flag set to Y*/
A	05SEPT2015	07SEPT2015	20SEPT2015	N	/*Sept 20 was the first office visit that occurred after this discharge*/
A	11NOV2015	12NOV2015	20NOV2015	N	/*Nov 20 was the fist office visit that occurred after this discharge*/
A	19DEC2015	21DEC2015		N	/*no office visits had a date that was after this discharge. Value is left blank*/

[FreelanceReinh · Accepted solution]

Hello @mikemangini,

Would this be any good?

data have1;
input person $ (admdate disdate) (:date9.);
cards;
A 30APR2015 01MAY2015
A 02SEP2015 03SEP2015
A 05SEP2015 07SEP2015
A 11NOV2015 12NOV2015
A 19DEC2015 21DEC2015
; 

data have2;
input person $ ovdate :date9.;
cards;
A 01JAN2015
A 02JUN2015
A 15JUL2015
A 20SEP2015
A 20NOV2015
A 05DEC2015
;

data temp;
set have1(rename=(disdate=date) in=dis)
    have2(rename=(ovdate=date)  in=ov)
    have1(rename=(admdate=date) drop=disdate in=adm);
by person date; /* Please make sure that the input datasets are sorted properly. */
length datetype $1;
datetype=ifc(dis,'d',ifc(ov,'v','a'));
format admdate date date9.;
run;

data want(rename=(date=disdate));
merge temp
      temp(firstobs=2 keep=person date datetype
                      rename=(person=_person date=_date datetype=_datetype));
if datetype='d' then do;
  if person=_person & _datetype='v' then do;
    nextFU=_date;
    admflag='N';
  end;
  else if person=_person & _datetype='a' then do;
    nextFU=.;
    admflag='Y';
  end;
  else if person ne _person then do;
    nextFU=.;
    admflag='N';
  end;
  output;
end;
format nextFU date9.;
drop _: datetype;
run; 

proc print data=want;
run;

[mikemangini]

Thank you! I had some issues with sorting as you mentioned but eventually the solution worked.

[Ksharp]

data have1;
input person $ (admdate disdate) (:date9.);
format admdate disdate date9.;
cards;
A 30APR2015 01MAY2015
A 02SEP2015 03SEP2015
A 05SEP2015 07SEP2015
A 11NOV2015 12NOV2015
A 19DEC2015 21DEC2015
; 

data have2;
input person $ ovdate :date9.;
format ovdate date9.;
cards;
A 01JAN2015
A 02JUN2015
A 15JUL2015
A 20SEP2015
A 20NOV2015
A 05DEC2015
;
run;
proc sql;
create table want as
 select a.*,case 
              when exists(select * from have1 where person=a.person and admdate between a.disdate and b.ovdate) then .
              else b.ovdate
             end as Soonest_Office_Visit_Date format=date9.,
            case 
              when exists(select * from have1 where person=a.person and admdate between a.disdate and b.ovdate) then 'Y'
              else 'N'
             end as Prior_to_Office_Visit
  from have1 as a left join have2 as b on a.person=b.person 
   where b.ovdate-a.disdate gt 0 
    group by a.person,a.admdate,a.disdate 
     having b.ovdate-a.disdate=min(b.ovdate-a.disdate)
 union
 select a.*,.,'N'
  from have1 as a left join have2 as b on a.person=b.person 
   group by a.person,a.admdate,a.disdate
    having a.disdate-max(b.ovdate) ge 0; 
quit;