Prev: How to code my question?
Next: Change display manager Log window font size?!
From: Sigurd Hermansen on 8 Mar 2010 11:17
June:
Assuming that the MS Access query works as required in MS Access, I'd start with it. In a quick glance at the program, I see only a couple of syntax differences between the Access SQL and SAS SQL queries:

SAS SQL doesn't support bracket syntax for referencing attributes:
[loc_crse]![crdt_per_smstr]+[reg_crse]![crdt_per_smstr]

In SAS SQL, a CREATE TABLE <SAS dataset name> AS ... clause precedes a SELECT clause and replaces an INTO clause of this form:
course_CRD_wt INTO Course_to_CIP

Other than these clauses, everything else looks legal. I should mention, though, that the SAS SQL compiler thru V9.2 does not in many instances optimize LEFT or RIGHT JOIN's effectively. Query performance given a data tables too large for MS Access may be poor. I've just finished a programming task in which standard SAS SQL LEFT JOIN's produced "Disk Full" errors. By default the outer joins sort datasets first before joining them. I had to rewrite the LEFT JOIN's as, in one situation, a hash object equivalent of the LEFT JOIN (see Ken Borowiak's NESUG paper), and in another case, a union of the key values' intersection and set complement. The SAS-L archives include descriptions of both of these workarounds.

As a rule, you don't have to sort datasets before referencing them in a SAS SQL query. The SAS SQL compiler far too often resorts to sorting instead of more efficient methods that one can write fairly easily in SAS programs, but no need to add to the drag on performance in those cases where the SAS SQL compiler finds a better method than sorting.
S
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of June G
Sent: Monday, March 08, 2010 10:28 AM
To: SAS-L(a)LISTSERV.UGA.EDU
Subject: ACCESS SQL program into SAS code

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: How to code my question?
Next: Change display manager Log window font size?!