DATA Step, Macro, Functions and more

Observation Join

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

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.


Accepted Solutions
Solution
‎10-09-2013 02:28 PM
Respected Advisor
Posts: 4,659

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

View solution in original post


All Replies
Super Contributor
Posts: 418

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

Super User
Posts: 5,096

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

Solution
‎10-09-2013 02:28 PM
Respected Advisor
Posts: 4,659

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
Contributor
Posts: 35

Re: Observation Join

Thank You!!!!

Contributor
Posts: 35

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.

🔒 This topic is solved and locked.

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

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