Hello,
I'm not sure how to solve this problem and was wondering if yall could help me out. I have two tables
Table A:
Student | Column1 | Column2 | Column3 |
---|---|---|---|
A | Coef1 | Coef4 | Coef5 |
B | Coef3 | . | . |
C | Coef2 | Coef4 | . |
Table B:
Student | Coef1 | Coef2 | Coef3 | Coef4 | Coef5 |
---|---|---|---|---|---|
A | 16 | 0 | 0 | 0 | 25 |
B | 0 | 15 | 0 | 0 | 0 |
C | 0 | 17 | 15 | 0 | 16 |
I'm trying to take the observation's in table A and somehow return the observations from table b. I can't think of a way to join this.
With proc transpose:
data table_A;
input Student $ Column1 $ Column2 $ Column3 $;
datalines;
A Coef1 Coef4 Coef5
B Coef3 . .
C Coef2 Coef4 .
;
data Table_B;
input Student $ Coef1 Coef2 Coef3 Coef4 Coef5;
datalines;
A 16 0 0 0 25
B 0 15 0 0 0
C 0 17 15 0 16
;
proc transpose data=table_a out=ta;
by student;
var column:;
run;
proc transpose data=table_b out=tb;
by student;
var coef:;
run;
proc sql;
create table table_c as
select
ta.student,
ta.COL1 as coef,
tb.COL1 as value
from
ta inner join
tb on ta.student=tb.student and ta.COL1=tb._NAME_
order by student, coef;
quit;
PG
Are you trying to say that for student A you'd like to keep Coef1, Coef4, and Coef5 but for student B you'd only like to Keep Coef3, for c etc....
If so, how do you want your outputs? Theoritically you already have your output in your secondary table if you want it to all be in one table. Did you want a different table for each student?
I think a tiny bit more clarification would help!
Thanks!
Brandon
Magic would be required.
There is absolutely nothing about Table A that shows where the numbers in Table B come from. Perhaps you know more about the problem than you are telling us.
Or perhaps you already have both tables and would like to generate something else as your final output. What would that look like??
With proc transpose:
data table_A;
input Student $ Column1 $ Column2 $ Column3 $;
datalines;
A Coef1 Coef4 Coef5
B Coef3 . .
C Coef2 Coef4 .
;
data Table_B;
input Student $ Coef1 Coef2 Coef3 Coef4 Coef5;
datalines;
A 16 0 0 0 25
B 0 15 0 0 0
C 0 17 15 0 16
;
proc transpose data=table_a out=ta;
by student;
var column:;
run;
proc transpose data=table_b out=tb;
by student;
var coef:;
run;
proc sql;
create table table_c as
select
ta.student,
ta.COL1 as coef,
tb.COL1 as value
from
ta inner join
tb on ta.student=tb.student and ta.COL1=tb._NAME_
order by student, coef;
quit;
PG
Thank You!!!!
I agree with the magic concept, but what I'm looking to do is not dependent on the values in table B. It's essentially a check to see if they even exist in the table.
Student B is the easiest scenario for me to try and explain.
In table A I'm perform a 'QA' on student B and it's been decided that they do not have Coef3. Table B is a historical record for all Coef's student B has had. If we see it in table A and it also exists as a >0 value in Table B then my 'QA' must have been wrong.
Hope this helps.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: