Joining tables with different key values

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Joining tables with different key values

[ Edited ]

I have two datasets as follows:

Dataset: Scores

Headers:Student_Number, Test,Score

1   Test1   97

1   Test 2  96

2   Test1   93

2   Test2   97

3   Test1   78

3   Test2   87

 

 

Dataset: Time  

Headers: Student_Number, Test, Time

1   A  25

1   B  36

2   A  10

2   B  15

3   A  45

3   B  58

 

Need:  I need to join these 2 tables on Student number and test number so that the final data contains Student_Number, Test, Score, and Time.

 

Constraints:  I cannot bring another table into the mix as I do not have write access to the server and using an external table will time out.  I cannot create an intermediary table to transform the data before the join.

 

I tried something like this:

PROC SQL;
    CREATE TABLE WORK.data1 AS
    SELECT t1.Stud_Num, 
        t1.Test,  

        t1.Time,
        t3.Stud_Num AS Stud_Num1,
        t3.Test AS Test1,
        t3.Score
     FROM WORK.DATA_TEST1 t1
          FULL JOIN WORK.SCORES t3 ON (t1.Stud_Num = t3.Stud_Num)
              AND ((t1.Test = 'Test1' AND t3.Test = 'A')
                     OR (t1.Test = 'Test2' AND t3.Test = 'B'));
QUIT;

 

I have also tried using if statements, but I can't seem to get it right. I am using SAS EG 5.1 for my development.  Any help or direction is appreciated.

 

Thanks!


Accepted Solutions
Solution
‎05-05-2016 12:53 PM
Super User
Posts: 19,105

Re: Joining tables with different key values

You can recode the test in your join statement or create a format and use input to convert it.

 

proc sql;
create table want as
select a.*, b.*
from scores as a
full join time as b
on a.student_number=b.student_number
and case when b.Test='A' then 'Test1'
		when b.Test='B' then 'Test2' end = a.test;
quit;

View solution in original post


All Replies
Solution
‎05-05-2016 12:53 PM
Super User
Posts: 19,105

Re: Joining tables with different key values

You can recode the test in your join statement or create a format and use input to convert it.

 

proc sql;
create table want as
select a.*, b.*
from scores as a
full join time as b
on a.student_number=b.student_number
and case when b.Test='A' then 'Test1'
		when b.Test='B' then 'Test2' end = a.test;
quit;
New Contributor
Posts: 3

Re: Joining tables with different key values

Thank you for your post. Simple and exactly what I needed. Thanks for the quick reply.
Super User
Posts: 11,121

Re: Joining tables with different key values

[ Edited ]

Here is one approach with a custom FORMAT used in lieu of a look up table. Which maybe easier to maintain then in line recoding if the number of values for test get large. Of course one issue is WHY are there two values at all.

Data Scores;
input Student_Number Test $ Score;
datalines;
1   Test1   97
1   Test2  96
2   Test1   93
2   Test2   97
3   Test1   78
3   Test2   87
; 
 
data Time ;
Input Student_Number Test $ Time ;
datalines;
1   A  25
1   B  36
2   A  10
2   B  15
3   A  45
3   A  58
;
run;

proc format;
value $testmatch
'A' = 'Test1'
'B' = 'Test2'
;
run;
PROC SQL;
    CREATE TABLE WORK.data1 AS
    SELECT t1.Student_Number, 
        t1.Test,  

        t1.Time,
        t3.Student_Number AS Student_Number1,
        t3.Test AS Test1,
        t3.Score
     FROM Time as t1
          left JOIN SCORES as t3 ON (t1.Student_Number = t3.Student_Number)
              AND put(t1.Test,$testmatch.) = t3.test;
QUIT;

Please note that the data is in a data step, that helps us test code. I corrected an apparent extra space in the test value for student_number 1.

 

 

Note: It really isn't a good idea to refer to a "header" or variable as Student_number in the description and then use stud_num in the code.

New Contributor
Posts: 3

Re: Joining tables with different key values

 

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 299 views
  • 2 likes
  • 3 in conversation