DATA Step, Macro, Functions and more

Select for data in table b based on table a

Reply
Regular Contributor
Posts: 194

Select for data in table b based on table a

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!

SAS Super FREQ
Posts: 8,868

Re: Select for data in table b based on table a

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

PROC Star
Posts: 7,487

Re: Select for data in table b based on table a

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

Super User
Posts: 11,343

Re: Select for data in table b based on table a

Maybe something like

Proc Sql;

     Create table final as

     select b.*

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

quit;

Regular Contributor
Posts: 194

Re: Select for data in table b based on table a

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?

Super User
Super User
Posts: 7,070

Re: Select for data in table b based on table a

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;

Occasional Contributor
Posts: 11

Re: Select for data in table b based on table a

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.

Super User
Posts: 10,041

Re: Select for data in table b based on table a

OR SQL

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

Regular Contributor
Posts: 194

Re: Select for data in table b based on table a

Thank you very much Everyone!!

Ask a Question
Discussion stats
  • 8 replies
  • 334 views
  • 8 likes
  • 7 in conversation