Help using Base SAS procedures

Using WHERE in a PROC SQL statement

Accepted Solution Solved
Reply
Super Contributor
Posts: 268
Accepted Solution

Using WHERE in a PROC SQL statement

Here is my code:

proc sql;

create table reenroll_statecourses_teacher as select

c.*,r.* from statecourses_teacher as c left join join1 as r 

on c.studentID=r.studentID and c.crs_schoolid=r.schoolid and c.termid lt 0 and (c.dateleft=r.exitdate or c.dateleft=r.exitdate+1)

order by studentID;

quit;

I believe the part in bold "and c.termid lt 0" is not correct because the matching is not what I'm wanting.  I only want observations from the data set "statecourses_teacher" WHERE the termid is negative but I get matches where termid is both positive and negative.  I checked and termid is a numeric value.

I appreciate any  help.

Gregg


Accepted Solutions
Solution
‎03-31-2015 10:24 AM
Respected Advisor
Posts: 4,649

Re: Using WHERE in a PROC SQL statement

In a left join, every record from the left table is included in the result, even when the on condition is not met. Move the condition to a where clause :

proc sql;

create table reenroll_statecourses_teacher as select

c.*,r.* from statecourses_teacher as c left join join1 as r

on c.studentID=r.studentID and c.crs_schoolid=r.schoolid and (c.dateleft=r.exitdate or c.dateleft=r.exitdate+1)

where c.termid lt 0

order by studentID;

quit;

PG

PG

View solution in original post


All Replies
Solution
‎03-31-2015 10:24 AM
Respected Advisor
Posts: 4,649

Re: Using WHERE in a PROC SQL statement

In a left join, every record from the left table is included in the result, even when the on condition is not met. Move the condition to a where clause :

proc sql;

create table reenroll_statecourses_teacher as select

c.*,r.* from statecourses_teacher as c left join join1 as r

on c.studentID=r.studentID and c.crs_schoolid=r.schoolid and (c.dateleft=r.exitdate or c.dateleft=r.exitdate+1)

where c.termid lt 0

order by studentID;

quit;

PG

PG
Super Contributor
Posts: 268

Re: Using WHERE in a PROC SQL statement

Thank you very much.  I am getting the expected results now.

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 163 views
  • 0 likes
  • 2 in conversation