BookmarkSubscribeRSS Feed
piyushas
Fluorite | Level 6

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.

16 REPLIES 16
Reeza
Super User
How many records do you get without any IF statement?
piyushas
Fluorite | Level 6

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

 

this gives me 623 observations.

Reeza
Super User

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
Fluorite | Level 6

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

KachiM
Rhodochrosite | Level 12

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
Super User

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
PROC Star

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
Fluorite | Level 6

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
Super User
Yes it will, but are both of your data sets de-duplicated?
piyushas
Fluorite | Level 6

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

 

 

Reeza
Super User

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
Super User Tom
Super User

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
Fluorite | Level 6

A&B - 551

B only - 34

 

= 585 in B.

 

Dataset B only has 581 observations to begin with.

Reeza
Super User

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 16 replies
  • 1669 views
  • 0 likes
  • 5 in conversation