DATA Step, Macro, Functions and more

Merge with OR statement

Reply
Frequent Contributor
Posts: 99

Merge with OR statement

Hi all,
I want to merge 2 datasets where one of the by variables could be equal to any of the 2 variables in the other dataset

Eg

Dataset A variables: ID ,Amt, Serial1
Dataset B variables: ID ,Amt, SerialA, SerialB

The by variable Serial1 in datasetA could by same as SerialA or SerialB. ID is also a by variable.

Thanks,

Amit
Super Contributor
Posts: 474

Re: Merge with OR statement

You could easily do this with a single SQL statement (where SERIAL1 = SERIALA or SERIAL1 = SERIALB).

For a datastep merge solution, you should do a 3 table merge, being the third table a copy of dataset B. Assuming that dataset A is sorted by SerialA, it would look like this;

data BB; /* copy B to BB */
set B;
run;

proc sort; /* sort BB by SerialB */
by SerialB;
run;

data RESULT; /* do the 3 table merge */
merge A (in = a)
B (in = b rename = (SerialA=Serial1) drop = SerialB)
BB (in = bb rename = (SerialB=Serial1) drop = SerialA);
by Serial1;
if a and (b or bb); /* match by Serial1=SerialA or Serial1=SerialB */
run;

Greetings from Portugal.

Daniel Santos at www.cgd.pt
Frequent Contributor
Posts: 99

Re: Merge with OR statement

Posted in reply to DanielSantos
Hi Daniel,
Thanks a lot for the solution. I will implement it both ways, to gain more confidence in proc sql ( trying to learning it)

Thanks.

Amit
Ask a Question
Discussion stats
  • 2 replies
  • 113 views
  • 0 likes
  • 2 in conversation