DATA Step, Macro, Functions and more

Merge Problems

Reply
Occasional Contributor
Posts: 8

Merge Problems

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.

Super User
Posts: 19,815

Re: Merge Problems

How many records do you get without any IF statement?
Occasional Contributor
Posts: 8

Re: Merge Problems

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

 

this gives me 623 observations.

Super User
Posts: 19,815

Re: Merge Problems

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. 

 

Occasional Contributor
Posts: 8

Re: Merge Problems

[ Edited ]

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

Super Contributor
Posts: 298

Re: Merge Problems

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.

Super User
Posts: 19,815

Re: Merge Problems

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;

Super User
Posts: 5,509

Re: Merge Problems

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.

Occasional Contributor
Posts: 8

Re: Merge Problems

Posted in reply to Astounding

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;

Super User
Posts: 19,815

Re: Merge Problems

Yes it will, but are both of your data sets de-duplicated?
Occasional Contributor
Posts: 8

Re: Merge Problems

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?

alldata.PNG

 

 

 

pubdata.PNG

 

 

Super User
Posts: 19,815

Re: Merge Problems

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

Super User
Super User
Posts: 7,050

Re: Merge Problems

[ Edited ]

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?

 

Occasional Contributor
Posts: 8

Re: Merge Problems

A&B - 551

B only - 34

 

= 585 in B.

 

Dataset B only has 581 observations to begin with.

Super User
Posts: 19,815

Re: Merge Problems


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.

Ask a Question
Discussion stats
  • 16 replies
  • 191 views
  • 0 likes
  • 5 in conversation