topic PROC SQL in: in SAS Procedures

PROC SQL in:

GreggB — Tue, 06 Nov 2018 20:35:46 GMT

Can this DATA step be incorporated into the PROC SQL step so that course_numbers beginning with '08' are excluded when table stud4 is created?

 proc sql;
create table stud4 as 
SELECT S.*,CC.COURSE_NUMBER, CC.SECTION_NUMBER, CC.SCHOOLID as cc_schoolID, T.TEACHERNUMBER, 
       T.LASTFIRST as TEACHER, courses.course_name
FROM stud3b as s
left join myoracle.cc as cc
on s.ID=cc.studentid
left join myoracle.teachers as t
on cc.teacherid=t.ID 
left join myoracle.COURSES as courses
on cc.COURSE_NUMBER=courses.course_number
where cc.termid ge 2800
order by s.student_number;
quit;	
	
data stud4a;
set stud4;
if course_number in:('08') then delete;
run;

Re: PROC SQL in:

novinosrin — Tue, 06 Nov 2018 20:46:19 GMT

Perhaps

proc sql;
create table stud4 as 
SELECT S.*,CC.COURSE_NUMBER, CC.SECTION_NUMBER, CC.SCHOOLID as cc_schoolID, T.TEACHERNUMBER, 
       T.LASTFIRST as TEACHER, courses.course_name
FROM stud3b as s
left join myoracle.cc as cc
on s.ID=cc.studentid
left join myoracle.teachers as t
on cc.teacherid=t.ID 
left join myoracle.COURSES as courses
on cc.COURSE_NUMBER=courses.course_number
where cc.termid ge 2800
   having cc.course_number not like ('08%')
order by s.student_number;
quit;

EDITED to NOT like

sorry

Re: PROC SQL in:

novinosrin — Tue, 06 Nov 2018 20:45:51 GMT

or is it

where cc.termid ge 2800   and cc.course_number not like ('08%')

EDITED to NOT like

sorry

Re: PROC SQL in:

GreggB — Tue, 06 Nov 2018 20:43:49 GMT

doesn't this do the opposite?

Re: PROC SQL in:

novinosrin — Tue, 06 Nov 2018 20:45:18 GMT

Don't know your data. I am taking a wild guess

Oops sorry

should be NOT like

Re: PROC SQL in:

FreelanceReinh — Tue, 06 Nov 2018 21:04:39 GMT

Alternatively, you can use a dataset option:

create table stud4(where=(course_number~=:'08')) as

Re: PROC SQL in:

PGStats — Tue, 06 Nov 2018 23:03:21 GMT

If you want the left join to work properly with a where condition on the table on the right side of the join, you must force SQL to apply the condition before operating the join, as such:

proc sql;
create table stud4 as 
SELECT  
    S.*,
    CC.COURSE_NUMBER, 
    CC.SECTION_NUMBER, 
    CC.SCHOOLID as cc_schoolID, 
    T.TEACHERNUMBER, 
    T.LASTFIRST as TEACHER, 
    courses.course_name
FROM 
    stud3b as s left join 
    (   select * 
        from myoracle.cc 
        where 
            course_number net "08" and 
            termid ge 2800  ) as cc on s.ID=cc.studentid left join 
    myoracle.teachers as t on cc.teacherid=t.ID left join 
    myoracle.COURSES as courses on cc.COURSE_NUMBER=courses.course_number
order by s.student_number;
quit;

otherwise, the where condition is applied to the missing values generated by the join and those added records are removed (you end up with the equivalent of an inner join, not a left join)

Re: PROC SQL in:

GreggB — Wed, 07 Nov 2018 15:45:17 GMT

Thanks. Never knew about net.

Re: PROC SQL in:

PGStats — Wed, 07 Nov 2018 18:25:57 GMT

Me nether... You made me look