topic Re: Merge Problems in SAS Programming

Merge Problems

piyushas — Tue, 29 Aug 2017 15:06:18 GMT

Hi all,

I'm merging two datasets alldata and pubdata. alldata has 589 observations and pubdata has 581.

the following code returns 34 observations:

data whyin;
merge alldata (in=a) pubdata2 (in=b);
by course;
if b and not a;
run;

the following code returns 551 observations:

data test;
merge alldata (in=a) pubdata2 (in=b);
by course;
if b and a;
run;

Shouldn't those two be adding up to 581 and not 585?

thanks

Re: Merge Problems

Reeza — Tue, 29 Aug 2017 15:09:12 GMT

How many records do you get without any IF statement?

Re: Merge Problems

piyushas — Tue, 29 Aug 2017 15:18:32 GMT

data test;
merge alldata (in=a) pubdata2 (in=b);
by course;
/** if b and a;**/
run;

this gives me 623 observations.

Re: Merge Problems

KachiM — Tue, 29 Aug 2017 15:21:31 GMT

There are 3 possibilities:

A and B

A and not B

not A and B

Note that the first is common to both A and B.

Re: Merge Problems

Reeza — Tue, 29 Aug 2017 15:22:17 GMT

So your data isn't one to one. If the data was one to one - means you have a single unique record for every course then what you expect would be true. But becuase you're likely doing a one to many or possibly many to many you don't get the results you want.

Did you edit your response? I could have sworn I initially read a number less than either of your totals....

Anyways, you need to determine if your data is one to one or many to one and how you should merge it.

Re: Merge Problems

piyushas — Tue, 29 Aug 2017 15:23:32 GMT

sorry yes - i ran the wrong code without the if statement. Thought i edited if before anyone read it.

Re: Merge Problems

Astounding — Tue, 29 Aug 2017 15:22:57 GMT

The issue that makes this more complex: ALLDATA may contain multiple observations for the same COURSE. Thus if a course exists in PUBDATA, there is no telling how many observations it will match in ALLDATA.

Re: Merge Problems

Reeza — Tue, 29 Aug 2017 15:23:31 GMT

Here's a quick example on how to test for something like this using the flags. Then you can filter your data and see what's happening for each set of records.

data class;
set sashelp.class;
run;

data class2;
set sashelp.class;
if age=12 then delete;
bmi = weight**2/height*2;
keep name bmi;
run;

proc sort data=class;
by name;
proc sort data=class2;
by name;
run;

data want;
merge class (in=a) class2(in=b);
by name;
if a and not b then flag=1;
else if a and b then flag=2;
else flag=3;

run;

Re: Merge Problems

piyushas — Tue, 29 Aug 2017 15:25:47 GMT

Will try both those solutions and revert but I have used the following code to ensure I have just one obs for each course. Would it not get rid of duplicates?

data allcourses;
set new14;
by course;
if first.course;
drop military nonmilitary total;
run;

Re: Merge Problems

Reeza — Tue, 29 Aug 2017 15:27:06 GMT

Yes it will, but are both of your data sets de-duplicated?

Re: Merge Problems

piyushas — Tue, 29 Aug 2017 15:40:24 GMT

Yes, both deduped.

data test;
merge alldata (in=a) pubdata3 (in=b);
by course;
if a and not b then flag=1;
else if a and b then flag=2;
else flag=3;
run;

623 observations in total

flag =1 - 38 observations

flag =2 - 551 observations

flag=3 - 34 observations

Same as with if statements.

Only thing I can think of is that in one of the datasets I had to manipulate the data to get the by variable (course) in the right format. In my first run through I got the 'by variables of multilple length...' message. I fixed it (I think) and don't get the error message. But when I run proc contents, this is how the variables are displayed... could this be the problem?

Re: Merge Problems

Reeza — Tue, 29 Aug 2017 15:43:29 GMT

Look at the 34 records. You should be able to figure out the issue by examing your data. Look to see if the course values are what you expect or if you have multiple cases for example, ie "Math" is not equal to "math'.

Re: Merge Problems

Tom — Tue, 29 Aug 2017 16:49:39 GMT

So you have 623 total ids. Of those 551 are in both A and B and 38 are in A only and the final 34 are in B only.

What is the question at this point?

Re: Merge Problems

piyushas — Tue, 29 Aug 2017 17:25:36 GMT

A&B - 551

B only - 34

= 585 in B.

Dataset B only has 581 observations to begin with.

Re: Merge Problems

Reeza — Tue, 29 Aug 2017 17:29:38 GMT

@piyushas wrote:

A&B - 551

B only - 34

= 585 in B.

Dataset B only has 581 observations to begin with.

The merged result can be larger than the original data set because it now includes records from A that were not in B to start with. If you don't want that, then you can use the IN to filter your results.

Re: Merge Problems

Tom — Tue, 29 Aug 2017 18:00:11 GMT

So if B has 581 observations and 34 do not match any ids from A that leaves 547 that do match. The count of 551 matched records means that four extra observations were added by merging with A. So either one id in B matched to 5 observations in A or some other combination. To find which ids are appearing in multiple times you can use the FIRST. and LAST. variables that the BY statement will generate.

data check;
  merge alldata (in=in1) pubdata3 (in=in2);
  by course;
  if in1 and in2 and not (first.course and last.course);
run;
proc print;
run;

Re: Merge Problems

piyushas — Tue, 29 Aug 2017 20:24:28 GMT

OMG thank you! Yup that's it. I introduced duplicates further down in my code, after doing my check for duplicates, and did not catch it.

I've been mucking with this for days -thank you!