topic Merge with OR statement in SAS Programming

Merge with OR statement

AmitKB — Wed, 15 Apr 2009 16:00:30 GMT

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

Re: Merge with OR statement

DanielSantos — Wed, 15 Apr 2009 16:59:24 GMT

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

Re: Merge with OR statement

AmitKB — Wed, 15 Apr 2009 17:40:21 GMT

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