Help using Base SAS procedures

Proc SQL join?

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

Proc SQL join?

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

STUDENTGRADESCHOOL PROGRAM
E9029GARDEN8
E9029GARDEN9
E9029BELL8
N2339BELL8
E98110DINGLE9
STUDENTGRADESCHOOLGATEBASPADVCALC
N2339BELLX
E98110DINGLE X
E9029GARDENXX

Accepted Solutions
Solution
‎03-11-2013 06:16 PM
Respected Advisor
Posts: 4,654

Re: Proc SQL join?

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;

PG

PG

View solution in original post


All Replies
Super User
Posts: 17,868

Re: Proc SQL join?

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?

Contributor
Posts: 28

Re: Proc SQL join?

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

GRADESCHOOL PROGRAM
E9029GARDEN8
E9029GARDEN9
E9029BELL8
N2339BELL8
E98110DINGLE9

Second table:

PROGRAM

CODE

PROGRAM
8 GATE
9BASP
1ADVCALC
2BIOSC

Would like my results to look like this:

STUDENTGRADESCHOOLGATEBASPADVCALC
N2339BELLX
E98110DINGLEX
E9029GARDENXX
 
Super Contributor
Posts: 578

Re: Proc SQL join?

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;

Solution
‎03-11-2013 06:16 PM
Respected Advisor
Posts: 4,654

Re: Proc SQL join?

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;

PG

PG
☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 239 views
  • 5 likes
  • 4 in conversation