topic How to merge match data with non-matched data in Statistical Procedures

How to merge match data with non-matched data

Henri — Thu, 15 Mar 2018 03:52:13 GMT

Hi Everyone

I am asking your help to solve the following question:

I have two datasets to merge with SAS 9.4. One contains matched cases and controls and some controls can be matched to more than one case. The second dataset contains the same ID with possible multiple observations per ID but does not contain the matching_ID. The two datasets and the expected output are displayed below.

Thanks a lot for your help!

dataset 1

ID	Match_ID	case
1	1a	0
2	1a	1
1	1b	0
3	1b	1

dataset 2

ID	var_date
1	01APR2008
1	10OCT2009
2	11JUL2008
2	09OCT2010
3	02JANV2011

Expected output

ID	Match_ID	Case	var_date
1	1a	0	01APR2008
1	1a	0	10OCT2009
2	1a	1	11JUL2008
2	1a	1	09OCT20010
1	1b	0	01APR2008
1	1b	0	10OCT2009
3	1b	1	02JANV2011

Re: How to merge match data with non-matched data

PGStats — Thu, 15 Mar 2018 04:15:24 GMT

Do a SQL join:

data d1;
input ID Match_ID $	case;
datalines;
1	1a	0
2	1a	1
1	1b	0
3	1b	1
;
 
data d2;
input ID var_date :date9.;
format var_date date9.;
datalines;
1	01APR2008
1	10OCT2009
2	11JUL2008
2	09OCT2010
3	02JAN2011
;

proc sql;
create table d3 as
select d1.*, d2.var_date
from d1 inner join d2 on d1.id=d2.id
order by match_id, id, var_date;
select * from d3;
quit;

Re: How to merge match data with non-matched data

Henri — Thu, 15 Mar 2018 15:10:27 GMT

Thank you very much PG. It works perfectly.

Henri