Select for data in table b based on table a

[jcis7]

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!

[Cynthia_sas]

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]

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]

Maybe something like

Proc Sql;

     Create table final as

     select b.*

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

quit;

[jcis7]

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]

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]

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]

OR SQL

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

[jcis7]

Thank you very much Everyone!!