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
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
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
Thank you very much. I am getting the expected results now.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.