topic Re: combine two datasets in SAS Programming

combine two datasets

LisaZ1 — Fri, 22 Jul 2022 20:38:59 GMT

hi, I now have two datasets(A and B), and I want a new datasets that combine these two based on what A has.

C is the output dataset that I want. In C, I only want data(fam-id, pers-id, date) that have the same fam-id and pers-id in both A and B. A and B are in different size and for the same pair of fam-id and pers-id, A only has one row, but B may have multiple rows and I want multiple rows to show up in C. If a pair of fam-id and pers-id appears only in B but not in A, then it should not appear in C. I tried to use merge in the data step, but it kept giving me output that apart from what I want, it has rows that appear only in B(two rows with fam-id=115 in the following example).

Any suggestions would be appreciated! Thank you!

A : B: C:

fam-id pers-id fam-id pers-id date fam-id pers-id date

111 1112 111 1112 01/02/2020 111 1112 01/02/2020

112 1124 111 1112 03/25/2020 111 1112 03/25/2020

113 1132 111 1112 07/08/2020 111 1112 07/08/2020

114 1145 112 1124 05/30/2020 112 1124 05/30/2020

112 1124 03/26/2020 112 1124 03/26/2020

113 1132 02/24/2020 113 1132 02/24/2020

115 1156 04/07/2020

115 1156 05/06/2020

Re: combine two datasets

tarheel13 — Fri, 22 Jul 2022 20:43:14 GMT

can you post the merge you tried?

Re: combine two datasets

tarheel13 — Fri, 22 Jul 2022 20:50:39 GMT

For future, please post the data as datalines like I have done. You need to use in flags to get the desired output dataset.

data A;
input fam_id $ pers_id $;
datalines;
111 1112
112 1124
113 1132
114 1145
;
run;

data B;
format date mmddyy10.;
input fam_id $ pers_id $ date :mmddyy10.;
datalines;
111 1112 01/02/2020
111 1112 03/25/2020
111 1112 07/08/2020
112 1124 05/30/2020
112 1124 03/26/2020
113 1132 02/24/2020
115 1156 04/07/2020
115 1156 05/06/2020
;
run;

data C;
    merge A(in=inA) B(in=inB);
    by fam_id pers_id;
    if inA and inB;
run;

Re: combine two datasets

LisaZ1 — Fri, 22 Jul 2022 21:05:02 GMT

Thank you so much!! I'm new to SAS and I worked for these for almost 2 days.