topic Re: SAS Merge statement Any solution to join my problem in SAS Programming

SAS Merge statement Any solution to join my problem

anandbillava — Thu, 11 Nov 2010 19:55:28 GMT

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.

Re: SAS Merge statement Any solution to join my problem

ArtC — Thu, 11 Nov 2010 20:12:21 GMT

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?

Re: SAS Merge statement Any solution to join my problem

anandbillava — Thu, 11 Nov 2010 20:35:34 GMT

Is there any limit for the by variable.

Re: SAS Merge statement Any solution to join my problem

Cynthia_sas — Thu, 11 Nov 2010 21:07:58 GMT

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

Re: SAS Merge statement Any solution to join my problem

SPR — Fri, 12 Nov 2010 17:14:59 GMT

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