update

[SASPhile]

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.

[Doc_Duke]

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;

[art297]

I can't test it without example data, but isn't that precisely what the datastep update statement was designed for?

[SASPhile]

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

[art297]

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;

[RichardinOz]

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

[art297]

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?

[Haikuo]

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