Hi ,
I have two tables a and b.
i need to get email address and phone no from table b. but if the ph no in a is same as in b then keep the one from a, if they are not equal or if it is missing in table a then get from b.
both tables have common id.
This should work; untested. The SQL step adds the additional phone number and the DATA step resolves the missing issue. You might be able to do it in a single query, but possibility of b.phone being missing makes it messy (the MAX function in SQL is different from the one in the DATA step, so the derived variable approach may not work). This assumes there is at most one record in b for each record in a.
PROC SQL;
CREATE TABLE c AS
SELECT a.*, b.phone AS bphone
FROM a
LEFT JOIN b
ON a.id=b.id
ORDER BY a.id;
QUIT; RUN;
DATA c;
SET c;
IF phone=. THEN phone=bphone;
DROP bphone;
RUN;
I can't test it without example data, but isn't that precisely what the datastep update statement was designed for?
table A
id Ph_no
1 6986912255
2 7025576682
3
4
table B
id Ph_no
1 6986912255
2 7025576676
3 5167741474
4
output table:
id Ph_no
1 6986912255
2 7025576676
3 5167741474
4
data A;
input id Ph_no;
infile cards truncover;
cards;
1 6986912255
2 7025576682
3
4
;
data B;
input id Ph_no;
infile cards truncover;
cards;
1 6986912255
2 7025576676
3 5167741474
4
;
data want;
update A B;
by id;
run;
Update will always overwrite the phone number in A with a non missing value in B, but the OP wants to preserve existing non-numeric values in A. In the past I have used update twice - first to create NewB which is updated with existing data from A, then updated A with NewB.
Richard in Oz
I don't think that is what the OP requested (i.e., preserve existing non-numeric values). The original post stated that "if the ph no in a is same as in b then keep the one from a". If they are the "same", does it really matter which is kept?
It matters when you are a pure perfectionist. In this case, I would have kept my (in=) variables, and if that does not meet my standard, my heart starts burning.
LOL, Art, Happy Turkey Day!
Haikuo
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.