DATA Step, Macro, Functions and more

update

Reply
Super Contributor
Posts: 673

update

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.

Trusted Advisor
Posts: 2,115

Re: update

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;

PROC Star
Posts: 7,471

Re: update

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

Super Contributor
Posts: 673

Re: update


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

PROC Star
Posts: 7,471

Re: update

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;

Super Contributor
Posts: 644

Re: update

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

PROC Star
Posts: 7,471

Re: update

Posted in reply to RichardinOz

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?

Respected Advisor
Posts: 3,156

Re: update

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

Ask a Question
Discussion stats
  • 7 replies
  • 233 views
  • 2 likes
  • 5 in conversation