BookmarkSubscribeRSS Feed
anandbillava
Fluorite | Level 6
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.
4 REPLIES 4
ArtC
Rhodochrosite | Level 12
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?
anandbillava
Fluorite | Level 6
Is there any limit for the by variable.
Cynthia_sas
SAS Super FREQ
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
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 4 replies
  • 778 views
  • 0 likes
  • 4 in conversation