topic Re: datasets match merging in SAS Programming

datasets match merging

michan22 — Mon, 24 Apr 2017 04:17:04 GMT

Dear all,

I have an exercise for a class that requires me to merge 2 datasets and I don't think I am getting it right.

The first dataset is class.excel1 with 1166 observations and 28 variables, and second dataset is class.excel2 with 882 observation and 9 variables.

Both dataset have a common variable called "serial_number" and I am suppose to merge the 2 dataset by this variable.

class.excel1 (the bigger dataset) should contain all the serial_number that class.excel2 (the smaller dataset) has, and my goal is the to match the serial_number between the 2 datasets and only keep those observations that the serial_number matched.

Right now my code is (after sorting by serial_number):

data class.merge;

merge class.excel1 class.excel2;

by serial_number;

run;

The log says:

The data set class.merge has 1476 observations and 36 variables.

However, there should be only 882 observations or less, so there must be something off and I am not sure what went wrong and how to fix it.

Any advice is appreciated! Thanks!

Re: datasets match merging

Reeza — Mon, 24 Apr 2017 04:22:27 GMT

Use the IN option to make sure a record is in both datasets.

http://support.sas.com/documentation/cdl/en/basess/68381/HTML/default/viewer.htm#p02xs0q9ihz3k1n1jfmzhy0sk1cw.htm

Re: datasets match merging

Jagadishkatam — Mon, 24 Apr 2017 04:24:13 GMT

Please try

data class.merge;
merge class.excel1(in=a) class.excel2(in=b);
by serial_number;
if a and b;
run;

Re: datasets match merging

michan22 — Mon, 24 Apr 2017 04:25:27 GMT

Thank you. But I did try that:

data class.merge;

merge class.excel1 class.excel2 (in=frodo);

by serial_number;

if frodo;

run;

and log says I have 1166 observations and 36 variables.

Is my in option not quite right?

Re: datasets match merging

Jagadishkatam — Mon, 24 Apr 2017 04:29:22 GMT

It means that there are multiple records by the same serial_number in class.excel1 due to which you are getting all of them into class.merge.

Re: datasets match merging

michan22 — Mon, 24 Apr 2017 04:30:59 GMT

I see! thank you so much @Jagadishkatam !

So I need the in option for both datasets.

I have now 654 observations and 36 variables.

However, another classmate of mine used R to merge and he only got 6 observations that did not match.

Is there a way to print a list of observations that were not matched?

much appreciated.

Re: datasets match merging

Reeza — Mon, 24 Apr 2017 04:33:58 GMT

Rather than using IN to filter use it to create a flag.

Data want;

set data1 (in=a) data2(in=b);

length flag $8.;

if a and b then flag ='MATCH';

Else if a then flag = 'A';

else if b then flag = 'B';

run;

Re: datasets match merging

Jagadishkatam — Mon, 24 Apr 2017 04:39:18 GMT

If b and not a;

This will give the observation that did not match.

Re: datasets match merging

michan22 — Mon, 24 Apr 2017 04:48:36 GMT

Thank you @Reeza!

Re: datasets match merging

michan22 — Mon, 24 Apr 2017 04:49:57 GMT

Thank you @Jagadishkatam! This is life changing 🙂

Re: datasets match merging

HB — Mon, 24 Apr 2017 21:12:31 GMT

I would check both datasets for duplicates. You don't say anywhere that there are no duplicate records. Duplicate records can make a mess out of matching.