- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data test;
merge alldata (in=a) pubdata2 (in=b);
by course;
/** if b and a;**/
run;
this gives me 623 observations.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
sorry yes - i ran the wrong code without the if statement. Thought i edited if before anyone read it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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'.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
A&B - 551
B only - 34
= 585 in B.
Dataset B only has 581 observations to begin with.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.