BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
das
Obsidian | Level 7 das
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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

4 REPLIES 4
Reeza
Super User
Its a SQL LEFT JOIN (or Right join) depending on table order 🙂
das
Obsidian | Level 7 das
Obsidian | Level 7
Thanks. I'll see if I can figure out SQL. Not my forte but I figured that was where the solution would be.
Reeza
Super User
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
Obsidian | Level 7 das
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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