topic Re: Merge/ Combine in SAS Programming

Merge/ Combine

deega — Fri, 11 Nov 2016 06:07:39 GMT

Hi All,

I would like two combine the following two datasets.

Dataset-1
ID	P1	P2	P3	P4
1	A	B	C	D
2	B	A	C
3	A	C	B	D
4	D	C	A	B
5	C	A	B
6	C	A	B	D
7	A	B	C
8	A	C	B
9	A	C
10	B	C	D	A

Dataset-2
Main_Id	ID
1	2
2	3
3	2
4	2
5	2
6	5
7	5
8	9
9	9
10	9

I expect the following Dataset

Main_Id	ID	P1	P2	P3	P4
1	2	B	A	C
2	3	A	C	B	D
3	2	B	A	C
4	2	B	A	C
5	2	B	A	C
6	5	C	A	B
7	5	C	A	B
8	9	A	C
9	9	A	C
10	9	A	C

I wrote following program

proc sort dataset1;

by id;

run;

proc sort dataset2;

by id;

run;

data final ;
merge dataset1 dataset2;
by id;
run;

but I am getting multiple records in the result. Please advice. Thanks in advance !

Re: Merge/ Combine

Reeza — Fri, 11 Nov 2016 06:41:19 GMT

Check your input datasets. One has duplicates.

Re: Merge/ Combine

deega — Fri, 11 Nov 2016 06:48:15 GMT

Yes, Dataset2 has duplicates. In my output instead of 10 records, I am getting far more than 10.

Re: Merge/ Combine

LinusH — Fri, 11 Nov 2016 06:51:24 GMT

Why are you merging by Id when from your example it's pretty clear that main_ID is the key?
Maybe you elaborate around your real life case and tell what you expect to get from merging on id.

Re: Merge/ Combine

Reeza — Fri, 11 Nov 2016 06:53:03 GMT

Oh... you aren't actually joining on ID.

You want to join on ID from dataset one and mainID in dataset 2. You'll need to rename your variables and modify your sort accordingly.

Re: Merge/ Combine

deega — Fri, 11 Nov 2016 07:30:31 GMT

My main Dataset is Dataset-2 and I want to add detail information of corresponding ID. The detail information of Id is in form of P1 P2... etc in Dataset1 and that is why I am merging by ID.

Re: Merge/ Combine

Reeza — Fri, 11 Nov 2016 07:37:32 GMT

Are you sure dataset 1 doesn't have duplicates?

Otherwise you could try IN to filter but you should first know why it's happening otherwise you may get unexpected results.

Data want;

merge d1(in=in1) d2(in=in2);

by id;

if in2; *cant recall if it's the first or second that filters but hopefully you get the idea - keep only records from d1;

run;

Re: Merge/ Combine

deega — Fri, 11 Nov 2016 08:00:48 GMT

Thanks Everbody. I have found the solution in proc sql. Left join is giving me desired output.

Re: Merge/ Combine

Reeza — Fri, 11 Nov 2016 08:11:54 GMT

As long as you figured out why it happened. If duplicates in Table1 by ID aren't identical you may have unexpected results.