topic Re: SQL Match Few to Many (SAS 9.2) in SAS Procedures

SQL Match Few to Many (SAS 9.2)

Jolly — Thu, 15 Oct 2015 18:59:22 GMT

Hello and thank you for taking the time to look at this for me.

I have two dataset that I am working with in SAS 9.2. I need to match by a common id (parent_id) then I need to pull the data from one data set and copy it to the other data set that has the matching parent_id. This data set also has multiple of the same parent_id for some cases, but the child_ids are unique for each and every parent.

Data Have 1

parent_id child_id child_info

1 a unique_1

2 b unique_2

2 c unique_3

2 d unique_4

3 e unique_5

4 f unique_6

4 g unique_7

Data 2 Have

parent_id parent_info Parent_info2

1 aa aaa

2 bb bbb

3 cc ccc

4 dd ddd

Data Want

Parent_id Child_id child_info parent_info parent_info2

1 a unique_1 aa aaa

2 b unique_2 bb bbb

2 c unique_3 bb bbb

2 d unique_4 bb bbb

3 e unique_5 cc ccc

4 f unique_6 dd ddd

4 g unique_7 dd ddd

Guidance on this topic is greatly appreciated.

Thank you,
Jeff S. O,

Re: SQL Match Few to Many (SAS 9.2)

LinusH — Thu, 15 Oct 2015 19:37:16 GMT

Join...?

Re: SQL Match Few to Many (SAS 9.2)

ballardw — Thu, 15 Oct 2015 19:43:47 GMT

Basic Left Join

Proc sql;
   create table want as
   select a.*, b.parent_info, b.parent_info2
   from dataset1 as a left join dataset2 as b 
           on a.parent_id = b.parent_id;
quit;

Re: SQL Match Few to Many (SAS 9.2)

PGStats — Fri, 16 Oct 2015 21:01:36 GMT

Or go natural:

Proc sql;
   create table want as
   select *
   from dataset1 natural left join dataset2;
quit;