topic Re: Select for data in table b based on table a in SAS Programming

Select for data in table b based on table a

jcis7 — Thu, 18 Apr 2013 22:48:59 GMT

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

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!

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

Cynthia_sas — Thu, 18 Apr 2013 22:55:48 GMT

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

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

art297 — Thu, 18 Apr 2013 23:05:58 GMT

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

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

ballardw — Thu, 18 Apr 2013 23:11:06 GMT

Maybe something like

Proc Sql;

Create table final as

select b.*

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

quit;

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

Tom — Fri, 19 Apr 2013 04:10:09 GMT

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;

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

JatinBansal — Fri, 19 Apr 2013 11:26:52 GMT

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.

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

Ksharp — Sat, 20 Apr 2013 08:57:49 GMT

OR SQL

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

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

jcis7 — Mon, 22 Apr 2013 22:05:26 GMT

Thank you very much Everyone!!

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

jcis7 — Wed, 24 Apr 2013 18:15:50 GMT

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?