Merge Problems

[piyushas]

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

p.

[Reeza]

How many records do you get without any IF statement?

[piyushas]

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

this gives me 623 observations.

[Reeza]

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. 

[piyushas]

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

[KachiM]

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.

[Reeza]

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;

[Astounding]

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.

[piyushas]

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;

[Reeza]

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

[piyushas]

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?

[Reeza]

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'. 

[Tom]

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?

[piyushas]

A&B - 551

B only - 34

= 585 in B.

Dataset B only has 581 observations to begin with.

[Reeza]

---

@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.