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

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

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

[ Edited ]
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.


Accepted Solutions
Solution
‎12-11-2017 06:13 PM
Contributor RM6
Contributor
Posts: 24

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

Posted in reply to elias_2020
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;


View solution in original post


All Replies
Solution
‎12-11-2017 06:13 PM
Contributor RM6
Contributor
Posts: 24

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

Posted in reply to elias_2020
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;


Occasional Contributor
Posts: 19

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

Thank you RM6.

Super User
Posts: 10,698

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

Posted in reply to elias_2020
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;
Occasional Contributor
Posts: 19

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

Thank you so much Ksharp.

 

It gives me the correct solution as well.

 

 

 

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 231 views
  • 1 like
  • 3 in conversation