I keep getting error message from sas log for the following program and will appreciate any help with this. Thanks!
PROC SQL; CREATE TABLE MIN_ENRL_TERM AS SELECT H.OASIS_PIDM as OASIS_PIDM , R.UID as USF_ID ,(SELECT H.REPT_TIME_FRAME as MIN_ENRL_TIME, H.OASIS_PIDM as MIN_ENRL_PIDM, min(CASE WHEN (H.STU_ENTRY_TYP IN ('B', 'E') AND H.STU_RECENT_ADM_TYP IN ('B', 'E') AND R.CRS_SYSTEM = 'N' AND R.CRS_GROUP = 'A' and R.STU_CLASS_LEVEL ^= 'N' AND R.TERM_TAKEN >= H.STU_ENTRY_DT) THEN R.TERM_TAKEN WHEN (R.CRS_SYSTEM = 'N' AND R.CRS_GROUP = 'A' and R.STU_CLASS_LEVEL ^= 'N' AND R.TERM_TAKEN >= H.STU_RECENT_ADM_DT) THEN R.TERM_TAKEN END)) AS FIRST_ENRL_TERM FROM SID.SID_COURSES_TO_DEGREE R JOIN SID.SID_HOURS_TO_DEGREE H ON R.CONTEXT_CODE = H.CONTEXT_CODE AND R.OASIS_PIDM = H.OASIS_PIDM AND R.REPT_TIME_FRAME = H.REPT_TIME_FRAME AND R.REPT_TIME_FRAME IN ('201905', '201908', '202001'); QUIT;
Your SQL has a subquery (sub-SELECT) that appears to select more than one column? But the FROM for that inner query isn't clear to me.
Also, you have a JOIN keyword but not an INNER or LEFT/RIGHT modifier. Maybe this an acceptable shortcut but I haven't seen it before.
When constructing SQL with an inner query, I advise testing out that inner query on its own first, getting it right, and then folding it into a larger query. Usually an inner query returns just one value or a list of values that are used as a filter for the outer query.
it looks like you are doing an inline select based on this screen shot with multiple columns being returned (MIN_ENRL_TIME, MIN_ENRL_PIDM...) and then trying to call the whole thing FIRST_ENRL_TERM. I do not believe this is valid syntax.
Not sure why you have a min() aggregate function around the case statement, and have no GROUP BY?
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.