topic Re: Proc SQL join? in SAS Procedures

Proc SQL join?

SAS_new — Mon, 11 Mar 2013 18:35:02 GMT

Is there a PROC SQL way to look at two different tables (would it be a join? ) and based on speciic criterias create a flag in the main dataset?

See table example below- the bottom table would be the main dataset:

Notice student E902 reported in GATE (8) by two different schools but flagged once with no new record to be produced. Can anyone help me? Thank you in advance

STUDENT	GRADE	SCHOOL PROGRAM
E902	9	GARDEN	8
E902	9	GARDEN	9
E902	9	BELL	8
N233	9	BELL	8
E981	10	DINGLE	9
STUDENT	GRADE	SCHOOL	GATE	BASP	ADVCALC
N233	9	BELL	X
E981	10	DINGLE		X
E902	9	GARDEN	X	X

Re: Proc SQL join?

Reeza — Mon, 11 Mar 2013 19:40:07 GMT

SAS_new wrote:

Is there a PROC SQL way to look at two different tables (would it be a join? ) and based on speciic criterias create a flag in the main dataset?

See table example below- the bottom table would be the main dataset:

Notice student E902 reported in GATE (8) by two different schools but flagged once with no new record to be produced. Can anyone help me? Thank you in advance

Yes, you can look at two different tables, using joins or sub-queries.

Can anyone help you?

I don't think you've provided enough information yet.

What is your criteria for setting the flag? What does your data look like? What does the other two datasets look like? What do you want the final dataset to look like?

Do you have to use Proc SQL or can you use datasteps?

Re: Proc SQL join?

SAS_new — Mon, 11 Mar 2013 20:56:03 GMT

Thank you-

I think my mind was going faster than my fingers. I guess I am trying to link table 1 with table 2(translation) and then I would create three new columns (GATE, BASP, ADVCALC, BIOS) in table 3. If table 1 contained program = 8, mark "X" under column GATE in table 3. If program= 9 in table 1, Mark "X" for BASP in table 3 and if program=1 in table 1, mark "X" under ADVCALC in table 3. I would link by student, grade and school. PROC SQL not required- if data step will take care of it. Thank you

Here is the first table:

STUDENT	GRADE	SCHOOL PROGRAM
E902	9	GARDEN	8
E902	9	GARDEN	9
E902	9	BELL	8
N233	9	BELL	8
E981	10	DINGLE	9

Second table:

PROGRAM CODE	PROGRAM
8	GATE
9	BASP
1	ADVCALC
2	BIOSC

Would like my results to look like this:

STUDENT	GRADE	SCHOOL	GATE	BASP	ADVCALC
N233	9	BELL	X
E981	10	DINGLE		X
E902	9	GARDEN	X	X

Re: Proc SQL join?

DBailey — Mon, 11 Mar 2013 21:04:52 GMT

proc sql noprint;

create table table3 as

select

t1.student,

t1.grade,

t1.school,

max(case when t2.program='GATE' then 'X' end) as GATE,

max(case when t2.program='BASP' then 'X' end) as BASP,

max(case when t2.program='ADVCALC' then 'X' end) as ADVCALC

from

table1 t1

left outer join table2 t2

on t1.progam=t2.program_code

group by

t1.student, t1.grade, t1.school;

quit;

Re: Proc SQL join?

PGStats — Mon, 11 Mar 2013 22:16:46 GMT

A format, a view and transpose :

proc format;
value pgm
8 = GATE
9 = BASP
1 = ADVCALC
2 = BIOSC;
run;

data have;
input student $ grade school $ program;
datalines;
E902 9 GARDEN 8
E902 9 GARDEN 9
E902 9 BELL 8
N233 9 BELL 8
E981 10 DINGLE 9
;

proc sql;
create view havev as
select student, grade, school, program format=pgm., "X" as x length=1
from have;
quit;

proc transpose data=havev out=want(drop=_name_);
by student grade school notsorted;
id program;
var x;
run;

proc print; run;