merging variables from an individual member to a paired database

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

merging variables from an individual member to a paired database

Hi all,

I have two databases - one of which is has 25 individual member observations with  4 variables (vars1-vars4). Each observation is identified by a unique ID number (id).

Obsidvar1var2var3var4
18608800072...
2860880013.112
3860880015.112
48608800232...
5861560006.111
6861560009.112
7861560012.112
8861560013.112
98615600171112
108615600301112
118615600312...
12861560040.112
13861560041.112
148617800022...
158617800122...
168617800132...
17861780024.112
188617800272...
198617800361112
20861780050.112
218617800531112
2286244000612..
238624400071112
24862440016.112
25862440025.112

I have a second database - a paired database (12 observations) - in which each observation represents a pair of members that may have a corresponding member data in the database above. Each member of the pair is identified by a unique ID (one called left_id, the other right_id):

Obsleft_idright_id
1861560006861560005
2861560013861560012
3860900004860900003
4861560031861560030
5860880024860880023
6861560041861560040
7861780013861780012
8861780002861780001
9861780050861780049
10862440007862440006
11862440016862440015
12862988001862988000

I would like merge the data from the individual member database (if present) to the paired database with the structure below. I would like each left/right variable list to be populated with corresponding data from the individual database (filled in the first two rows as an example). The output database should have 12 observations like the paired database. If data are not available for one or both members of the pair then the var should be missing (see observation 12 on the paired database which does not have a corresponding individual data for either member).

Obsleft_idleft_var1left_var2left_var3left_var4right_idright_var1right_var2right_var3right_var4
1861560006.111861560005....
2861560013.112861560012.112
3860900004860900003
4861560031861560030
5860880024860880023
6861560041861560040
7861780013861780012
8861780002861780001
9861780050861780049
10862440007862440006
11862440016862440015
12862988001862988000

Any solution using data step and Proc SQL would be much appreciated.

Thanks community.

--Tim


Accepted Solutions
Solution
‎11-06-2013 09:07 AM
Super User
Super User
Posts: 6,502

Re: merging variables from an individual member to a paired database

Use a different type of join

select ......

  from table2 match

  left join table1 left

  on left.id = match.left_id

  left join table1 right

  on right.id = match.right_id

;

View solution in original post


All Replies
Super User
Super User
Posts: 6,502

Re: merging variables from an individual member to a paired database

SQL is easy.

create table want as

   select left.id as left_id

           , left.var1 as left_var1

          ....

  from table1 left

       , table1 right

       , table2 match

where left.id = match.left_id

     and right.id = match.right_id

;

Occasional Contributor
Posts: 11

Re: merging variables from an individual member to a paired database

Thanks Tom,

The SQL produced what I wanted except that the output table 'want' have only rows where the left and right id match. Is it possible to include in the output table all rows from table 2 even if there was no match id's?

Thanks again.

--Tim

Solution
‎11-06-2013 09:07 AM
Super User
Super User
Posts: 6,502

Re: merging variables from an individual member to a paired database

Use a different type of join

select ......

  from table2 match

  left join table1 left

  on left.id = match.left_id

  left join table1 right

  on right.id = match.right_id

;

Occasional Contributor
Posts: 11

Re: merging variables from an individual member to a paired database

Thanks.

This works well.

--Tim

Super Contributor
Posts: 282

Re: merging variables from an individual member to a paired database

Hi,

For a step by step approach using data steps and proc sort:

data bypair;

  pair_id=_n_;

  set have2;

run;

proc sort data=bypair(drop=right_id)

          out =byleft;

  by left_id;

run;

proc sort data=bypair(drop=left_id)

          out =byright;

  by right_id;

run;

data full_left(rename=(var1=left_var1 var2=left_var2 var3=left_var3 var4=left_var4));

  merge have1  (rename=(id=left_id))

  byleft(in=inl)

  ;

  by left_id;

  if inl;

run;

data full_right(rename=(var1=right_var1 var2=right_var2 var3=right_var3 var4=right_var4));

  merge have1  (in=inh rename=(id=right_id))

  byright(in=inr)

  ;

  by right_id;

  if inr;

run;

proc sort data=full_left;

  by pair_id;

run;

proc sort data=full_right;

  by pair_id;

run;

data want(drop=pair_id);

  merge full_left

        full_right;

  by pair_id;

run;

Regards,

Amir.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 305 views
  • 3 likes
  • 3 in conversation