SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Find first office visit after hospital admission.

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

Find first office visit after hospital admission.

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*/

Accepted Solutions
Solution
‎02-11-2016 02:37 PM
Trusted Advisor
Posts: 1,115

Re: Find first office visit after hospital admission.

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;

View solution in original post


All Replies
Solution
‎02-11-2016 02:37 PM
Trusted Advisor
Posts: 1,115

Re: Find first office visit after hospital admission.

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;
Contributor
Posts: 34

Re: Find first office visit after hospital admission.

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

Super User
Posts: 9,681

Re: Find first office visit after hospital admission.

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 305 views
  • 2 likes
  • 3 in conversation