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.
Thank you RM6.
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;
Thank you so much Ksharp.
It gives me the correct solution as well.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.