topic update in SAS Programming

update

SASPhile — Tue, 20 Nov 2012 16:55:49 GMT

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.

Re: update

Doc_Duke — Tue, 20 Nov 2012 17:33:25 GMT

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;

Re: update

art297 — Tue, 20 Nov 2012 17:47:40 GMT

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

Re: update

SASPhile — Tue, 20 Nov 2012 19:39:45 GMT

table A

id   Ph_no
1    6986912255
2    7025576682
3

table B
id   Ph_no
1    6986912255
2    7025576676
3    5167741474

output table:

id   Ph_no
1    6986912255
2    7025576676
3    5167741474

Re: update

art297 — Tue, 20 Nov 2012 19:49:23 GMT

data A;

input id Ph_no;

infile cards truncover;

cards;

1 6986912255

2 7025576682

;

data B;

input id Ph_no;

infile cards truncover;

cards;

1 6986912255

2 7025576676

3 5167741474

;

data want;

update A B;

by id;

run;

Re: update

RichardinOz — Tue, 20 Nov 2012 20:47:40 GMT

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

Re: update

art297 — Tue, 20 Nov 2012 21:09:40 GMT

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?

Re: update

Haikuo — Tue, 20 Nov 2012 21:47:37 GMT

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