Fluorite | Level 6

## Observation Join

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:

StudentColumn1Column2Column3
ACoef1Coef4Coef5
BCoef3..
CCoef2Coef4.

Table B:

StudentCoef1Coef2Coef3Coef4Coef5

A

1600025
B015000
C01715016

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Opal | Level 21

## Re: Observation Join

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

PG
5 REPLIES 5
Quartz | Level 8

## Re: Observation Join

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

PROC Star

## Re: Observation Join

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??

Opal | Level 21

## Re: Observation Join

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

PG
Fluorite | Level 6

## Re: Observation Join

Thank You!!!!

Fluorite | Level 6

## Re: Observation Join

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.

Discussion stats
• 5 replies
• 1038 views
• 0 likes
• 4 in conversation