Prev: new joinee
Next: Mock ups
From: June G on 8 Mar 2010 10:28
I am working on rewriting the Access SQL code into a SAS program and having
difficulty. (The dataset is too large for Access.) I was thinking the SAS
code needed to be written from inside out. My joins are not working properly
though. Any help would be much appreciated.

Thank you,
June

Below is the Access SQL code followed by the SAS code thus far:

SELECT student.stu_id, student.ssn, student.namel, student.namef,
student.addr1, student.city, student.grade,
student.efe_no, student.sch_id, enrollment.crse_id,
loc_crse_cip.cip, reg_crse.crdt_per_smstr, loc_crse.crse_name,
reg_crse.fiscal_year, reg_crse.apprv_code, loc_crse.var_crdt,
loc_crse.crdt_per_smstr,
[loc_crse]![crdt_per_smstr]+[reg_crse]![crdt_per_smstr]
AS course_CRD_wt INTO Course_to_CIP

FROM (((enrollment RIGHT JOIN student ON
(enrollment.efe_no = student.efe_no)
AND (enrollment.sch_id = student.sch_id)
AND (enrollment.stu_id = student.stu_id))
LEFT JOIN loc_crse ON (enrollment.crse_id = loc_crse.crse_id)
AND (enrollment.sch_id = loc_crse.sch_id)
AND (enrollment.efe_no = loc_crse.efe_no))
LEFT JOIN reg_crse ON (loc_crse.efe_no = reg_crse.efe_no)
AND (loc_crse.reg_crse_id = reg_crse.reg_crse_id))
LEFT JOIN loc_crse_cip ON (enrollment.crse_id = loc_crse_cip.crse_id)
AND (enrollment.sch_id = loc_crse_cip.sch_id)
AND (enrollment.efe_no = loc_crse_cip.efe_no)

GROUP BY student.stu_id, student.ssn, student.namel, student.namef,
student.addr1, student.city, student.grade,
student.efe_no, student.sch_id, enrollment.crse_id,
loc_crse_cip.cip, reg_crse.crdt_per_smstr, loc_crse.crse_name,
reg_crse.fiscal_year, reg_crse.apprv_code, loc_crse.var_crdt,
loc_crse.crdt_per_smstr,
[loc_crse]![crdt_per_smstr]+[reg_crse]![crdt_per_smstr]

HAVING (((student.grade) Between 8 And 13) AND ((reg_crse.apprv_code)="1"
Or (reg_crse.apprv_code)="3"));




SAS Code

/****************************************************************************
INNER JOIN loc_crse and reg_crse by efe_no STEP 4
*****************************************************************************/;
proc sort data=isbe.loc_crse out=loc_crse;
by efe_no;
run;
proc sort data=isbe.reg_crse out=reg_crse;
by efe_no;
run;
proc sql;
create table step4 as
select l.crse_name, l.crdt_per_smstr, l.var_crdt, l.efe_no,
r.fiscal_year, r.crdt_per_smstr, r.apprv_code, r.efe_no
from loc_crse as l inner join reg_crse as r
on l.efe_no=r.efe_no;
run;
proc contents data=step4;
run;
/****************************************************************************
INNER JOIN loc_crse and reg_crse by efe_no STEP 3
*****************************************************************************/;
proc contents data=isbe.loc_crse_cip;
run;
proc sort data=step4 out=step4;
by efe_no;
run;
proc sort data=isbe.loc_crse_cip out=loc_crse_cip;
by efe_no;
run;
proc sql;
create table step3 as
select l.crse_name, l.crdt_per_smstr, l.var_crdt,
l.fiscal_year, l.apprv_code, l.efe_no,
r.cip, r.efe_no
from step4 as l inner join loc_crse_cip as r
on l.efe_no=r.efe_no;
run;
proc contents data=step3;
run;
/****************************************************************************
INNER JOIN loc_crse and reg_crse by efe_no STEP 2
*****************************************************************************/;
data student;
set student;
stu_id=put(stu_id,8.);
sch_id=put(sch_id,8.);
efe_no=put(efe_no,8.);
run;
proc sort data=isbe.enrollment out=enrollment(obs=1000);
by stu_id;
run;

data enrollment2;
set enrollment;
stu_id1=put(stu_id,8.);
sch_id1=put(sch_id,8.);
efe_no1=put(efe_no,8.);
run;

proc sql;
create table tem2 as
select l.stu_id1, l.efe_no1, l.sch_id1, crse_id,
l.stu_id1 || l.sch_id1 || l.efe_no1 as new_v1,
r.ssn, r.namel, r.namef, r.addr1, r.city, r.grade
from enrollment2 as l left join student2 as r
on l.stu_id1=r.stu_id1;
run;
 | 
Pages: 1
Prev: new joinee
Next: Mock ups