BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jsendzik
Fluorite | Level 6

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
PGStats
Opal | Level 21

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

5 REPLIES 5
Anotherdream
Quartz | Level 8

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

Astounding
PROC Star

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

PGStats
Opal | Level 21

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
Jsendzik
Fluorite | Level 6

Thank You!!!!

Jsendzik
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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