BookmarkSubscribeRSS Feed
jcis7
Pyrite | Level 9

Hello,

I've a two tables.

I need email addresses for the IDs in table B that match table A.. However, the IDs in table B may be listed more than once because of different email addresses.  The IDs in Table A are listed only once.  And there're IDs in table B that I don't need as they've not listed in table A.

Table A

ID

BAC

IDG

IWL

Table B

ID           email

BAC       bacet@email.net

BAC       bacra@email.net

IDG        edna@gmail.com

IWL       larry@email.net

IWL       edgar@email.net

EEK       lyons@eml.com

EEK       reb@email.com

Final Table

ID           email

BAC       bacet@email.net

BAC       bacra@email.net

IDG        edna@gmail.com

IWL       larry@email.net

IWL       edgar@email.net

I only know how to do this if the IDs are listed once in Table B not more than once.

Thanks!

8 REPLIES 8
Cynthia_sas
SAS Super FREQ

Hi: this is very easy to do with the MERGE statement and the IN= option. I am not in a position to run SAS code right now, but if you look for examples of the MERGE statement, you should find the answer quickly.

cynthia

art297
Opal | Level 21

If you are looking for how to solve many-to-many merges using sql, I just came across this paper yesterday that provides what I found to be an extremely nice way of explaining how to accomplish various merges using proc sql:

http://www2.sas.com/proceedings/sugi30/249-30.pdf

ballardw
Super User

Maybe something like

Proc Sql;

     Create table final as

     select b.*

     from a left join b on a.id=b.id;

quit;

jcis7
Pyrite | Level 9

This is great - I tried it and it works well.

Question:  how do I check to see if the IDs in final table are the same as that in table a?

Tom
Super User Tom
Super User

Perhaps your example is simpler than your actual data, but in your example the list of IDs Table A is unique.  So you do know how to do it.  Using normal SAS MERGE statement should work fine.

data FINAL;

merge A (in=in1) B (in=in2);

by id;

if in1 ;

run;

JatinBansal
Calcite | Level 5

To do this in SAS datastep, both datasets will need to be sorted first on the common variable. The full code for doing the merge in datastep will turn out to be the following:

Proc sort data=A;

by ID;

run;

Proc sort data=B;

by ID;

run;

data FINAL;

merge A (in=in1) B (in=in2);

by id;

if in1 ;

run;

So doing it in SQL is a lot easier option, the code for which is mentioned by ballardw above.

Ksharp
Super User

OR SQL

select * from b where b.id in ( select id from a ) ;

jcis7
Pyrite | Level 9

Thank you very much Everyone!!

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
  • 8 replies
  • 1831 views
  • 8 likes
  • 7 in conversation