topic Re: Observation Join in SAS Programming

Observation Join

Jsendzik — Wed, 09 Oct 2013 17:59:00 GMT

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	Coef5
A	16	0	0	25
B	0	15	0	0
C	0	17	15	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.

Re: Observation Join

Anotherdream — Wed, 09 Oct 2013 18:15:49 GMT

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

Re: Observation Join

Astounding — Wed, 09 Oct 2013 18:17:22 GMT

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

Re: Observation Join

PGStats — Wed, 09 Oct 2013 18:28:22 GMT

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;

Re: Observation Join

Jsendzik — Wed, 09 Oct 2013 18:29:19 GMT

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.

Re: Observation Join

Jsendzik — Wed, 09 Oct 2013 18:41:48 GMT

Thank You!!!!