SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Update/Modify master table with repeated 'By' variable

Accepted Solution Solved
Reply
Contributor das
Contributor
Posts: 44
Accepted Solution

Update/Modify master table with repeated 'By' variable

I have a master data table that has repeated measures on subjects in long format. I need to add genotype data to each row based on subject ID. The genotype info comes from a larger subject database. I can not figure out how to get each instance of a subject in the master table to fill with the same genotype information and not also bring in subjects from the lookup table that are not part of this study

 

Code to create example data tables:

 

data master;

input id replicate result ;

datalines;

4000 1 89

4000 2 36

4000 3 61

4000 4 66

4001 1 94

4001 2 91

4001 3 22

4001 4 47

4002 1 96

4002 2 84

4002 3 87

4002 4 51

;

run;

 

data genotype_lookup ;

input id codon1 $2 codon2 $2 codon3 $2 ;

datalines;

3995 AA RR QQ

3996 AV RR QQ

3997 VV RR QQ

3998 AA RH QQ

3999 AA HH QQ

4000 AA RR QR

4001 AA RR RR

4002 AA RH QR

4003 AA RR QQ

4004 AV RR QQ

4005 VV RR QQ

4006 AA RH QQ

;

run;

 

This is what the 'updated' master table should look like in the end:

 

id replicate result codon1 codon2 codon3
4000 1 89 AA RR QR
4000 2 36 AA RR QR
4000 3 61 AA RR QR
4000 4 66 AA RR QR
4001 1 94 AA RR RR
4001 2 91 AA RR RR
4001 3 22 AA RR RR
4001 4 47 AA RR RR
4002 1 96 AA RH QR
4002 2 84 AA RH QR
4002 3 87 AA RH QR
4002 4 51 AA RH QR

 

 

Thank you for your help,

Dave

 


Accepted Solutions
Solution
‎12-04-2015 06:35 PM
Super User
Posts: 17,868

Re: Update/Modify master table with repeated 'By' variable

proc sql;
create table want as
select a.*, b.codon1, b.codon2, b.codon3
from master as a
left join genotype_lookup as b
on a.id=b.id
order by 1, 2;
quit;

View solution in original post


All Replies
Super User
Posts: 17,868

Re: Update/Modify master table with repeated 'By' variable

Its a SQL LEFT JOIN (or Right join) depending on table order Smiley Happy
Contributor das
Contributor
Posts: 44

Re: Update/Modify master table with repeated 'By' variable

Thanks. I'll see if I can figure out SQL. Not my forte but I figured that was where the solution would be.
Solution
‎12-04-2015 06:35 PM
Super User
Posts: 17,868

Re: Update/Modify master table with repeated 'By' variable

proc sql;
create table want as
select a.*, b.codon1, b.codon2, b.codon3
from master as a
left join genotype_lookup as b
on a.id=b.id
order by 1, 2;
quit;
Contributor das
Contributor
Posts: 44

Re: Update/Modify master table with repeated 'By' variable

Thank you, Reeza, especially for the code itself. I should be able to apply that to my actual tables. I'll repost if there are any quirks not realized in my example tables above.

 

Much appreciate the fast answer. Hooray!

Dave

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 627 views
  • 1 like
  • 2 in conversation