Update/Modify master table with repeated 'By' variable

[das]

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

[Reeza]

Its a SQL LEFT JOIN (or Right join) depending on table order 🙂

[das]

Thanks. I'll see if I can figure out SQL. Not my forte but I figured that was where the solution would be.

[Reeza · Accepted solution]

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;

[das]

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