BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8

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.

7 REPLIES 7
Doc_Duke
Rhodochrosite | Level 12

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
Opal | Level 21

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

SASPhile
Quartz | Level 8


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
Opal | Level 21

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
Quartz | Level 8

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
Opal | Level 21

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
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 965 views
  • 2 likes
  • 5 in conversation