BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GreggB
Pyrite | Level 9

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;
1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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)

 

PG

View solution in original post

8 REPLIES 8
novinosrin
Tourmaline | Level 20

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

 

novinosrin
Tourmaline | Level 20

or is it

 

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

 EDITED to NOT like

 

sorry

GreggB
Pyrite | Level 9

doesn't this do the opposite?

novinosrin
Tourmaline | Level 20

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

 

Oops sorry 

 

should be NOT like

FreelanceReinh
Jade | Level 19

Alternatively, you can use a dataset option:

create table stud4(where=(course_number~=:'08')) as
PGStats
Opal | Level 21

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)

 

PG
GreggB
Pyrite | Level 9
Thanks. Never knew about net.
PGStats
Opal | Level 21

Me nether... You made me look Smiley Happy

PG

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 959 views
  • 1 like
  • 4 in conversation