DATA Step, Macro, Functions and more

SAS Merge statement Any solution to join my problem

Reply
Contributor
Posts: 72

SAS Merge statement Any solution to join my problem

I have two datasets. If the record is in both the datasets then I dont want that. If its only find in any of the dataset then I want that record to be output to different dataset.

data a;
c1=1; c2="aaa"; c3="1"; c4="ss1"; c5="gg"; output;
c1=2; c2="aaa"; c3="1-1"; c4="ss"; c5="ff"; output;
c1=3; c2="bbb"; c3="2"; c4="ss"; c5="yyy"; output;
run;

data b;
c1=1; c2="aaa"; c3="1"; c4="ss1"; c5="gg"; output;
c1=2; c2="ccc"; c3="1-1"; c4="ss"; c5="rtr"; output;
c1=3; c2="bbb"; c3="2"; c4="ss"; c5="hhj"; output;
run;

data d e;
merge a(in=in1) b(in=in2); by c1 c2;
if (in1=1 and in2=0) then output d;
if (in1=0 and in2=1) then output e;
run;

The dataset d should contain below observations
c1=2; c2="aaa"; c3="1-1"; c4="ss"; c5="ff"; output;

ant the dataset e should contain

c1=2; c2="ccc"; c3="1-1"; c4="ss"; c5="rtr"; output;

Any body has idea how to acheive this.
Valued Guide
Posts: 634

Re: SAS Merge statement Any solution to join my problem

Posted in reply to anandbillava
There will be some truncation because of the way the variables (C3 and C5) are defined in your test data sets (A and B), otherwise it seems like you should be successful. What exactly is not working?
Contributor
Posts: 72

Re: SAS Merge statement Any solution to join my problem

Is there any limit for the by variable.
SAS Super FREQ
Posts: 8,862

Re: SAS Merge statement Any solution to join my problem

Posted in reply to anandbillava
Hi:
What is the issue you're seeing that leads you to wonder about the limit for the BY variable? Are you doing one-to-one merging, one-to-many merging or many-to-many merging in the "real" data. With just 3 test observations in each data set, it is hard to understand your issue or what leads you to ask about BY variables.

If you are seeing this message in the log with your "REAL" data,
[pre]
NOTE: MERGE statement has more than one data set with repeats of BY values.
[/pre]

it generally indicates that you are doing a many-to-many merge, which may produce undesireable results, as explained in the Tech Support note:
http://support.sas.com/kb/24/752.html

cynthia
Super Contributor
Super Contributor
Posts: 365

Re: SAS Merge statement Any solution to join my problem

Posted in reply to anandbillava
Hello Anandbilava,

As AtrC noticed you have a truncation error in your character variables c3-c5 in the a and b datasets. If you add the line

length c1 8 c2 c3 c4 c5 $3;

after data a; and data b; then everything should work fine. At least it is true for my computer.

Sincerely,
SPR
Ask a Question
Discussion stats
  • 4 replies
  • 153 views
  • 0 likes
  • 4 in conversation