topic Re: Merge two data sets when the visit hospital between two events dates in SAS Data Management

Merge two data sets when the visit hospital between two events dates

elias_2020 — Mon, 11 Dec 2017 01:27:56 GMT

data offenders;
   input  id   offence_DATE mmddyy10. sex$;
   format offence_DATE  mmddyy10.;
   datalines;
1 05/23/05  M 
1 08/01/09  M
2 04/18/01  F
2 06/10/12  F
3 01/01/11  M
;
run;



data HOSPITAL;
   input  id   ADMIT_date mmddyy10. ;
   format ADMIT_date mmddyy10.;
   datalines;
1 10/21/03      
1 06/29/05      
1 02/03/07 
1 09/21/08
1 08/17/09         
3 12/31/10      
3 01/02/13      
;
run;

/* I would like to know any hospital visit between the two offences (if the offenders has two) or any visit after the first offence (fot those who has only one offence such as ID=3)*/
/* I would like to keep the offender who doesn’t have any visit in the data*/ 
/*The result should be appear as below

ID  offence_DATE   ADMIT_date   sex
1   05/23/05                    M
1                   06/29/05    M
1                   02/03/07    M
1                   09/21/08    M
1  08/01/09                     M
2  04/18/01                     F 
2  06/10/12                     F
3  01/01/11                     M
3                   01/02/13    M               

/*

Could you please suggest for the best method for merging such this kind of the data.

Thank you.

Re: Merge two data sets when the visit hospital between two events dates

RM6 — Mon, 11 Dec 2017 06:12:34 GMT

here is a sample code which would do good with the example, but you will need to modify it when there are more than 2 offence_date.

proc sql;
create table admit as
select *
from (
select distinct a.id,admit_date format mmddyy10.,admit_date as offence_date format mmddyy10.,sex
from (
select id,offence_date format mmddyy10., min(offence_date) as min format mmddyy10.,
case when count(id) =1 then today() else max(offence_date) end as max format mmddyy10.,
sex
from offenders
group by id
) a
left join hospital b
on a.id= b.id
and admit_date between min and max
)
where admit_date is not null
order by id,offence_date
;
quit;

proc sort data=offenders; by id offence_date; run;

data fin;
merge offenders admit;
by id offence_date;
if offence_date= admit_date then call missing(offence_date);
run;

Re: Merge two data sets when the visit hospital between two events dates

Ksharp — Mon, 11 Dec 2017 12:26:26 GMT

data offenders;
   input  id   offence_DATE mmddyy10. sex$;
   date=offence_DATE ;
   format offence_DATE  date mmddyy10.;
   datalines;
1 05/23/05  M 
1 08/01/09  M
2 04/18/01  F
2 06/10/12  F
3 01/01/11  M
;
run;



data HOSPITAL;
   input  id   ADMIT_date mmddyy10. ;
   date=ADMIT_date;
   format ADMIT_date date mmddyy10.;
   datalines;
1 10/21/03      
1 06/29/05      
1 02/03/07 
1 09/21/08
1 08/17/09         
3 12/31/10      
3 01/02/13      
;
run;

data temp;
 set offenders HOSPITAL;
 by id date;
 drop date;
run;
data want;
 merge temp(drop=sex) temp(keep=id sex where=(sex is not missing));
 by id;
run;

Re: Merge two data sets when the visit hospital between two events dates

elias_2020 — Mon, 11 Dec 2017 23:14:21 GMT

Thank you RM6.

Re: Merge two data sets when the visit hospital between two events dates

elias_2020 — Mon, 11 Dec 2017 23:15:13 GMT

Thank you so much Ksharp.

It gives me the correct solution as well.