topic Re: Help with PROC SQL syntax error in SAS Procedures

Help with PROC SQL syntax error

xliu1 — Mon, 22 Feb 2021 17:20:21 GMT

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;

Re: Help with PROC SQL syntax error

ChrisHemedinger — Mon, 22 Feb 2021 17:33:01 GMT

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.

Re: Help with PROC SQL syntax error

utrocketeng — Mon, 22 Feb 2021 19:44:40 GMT

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.

Re: Help with PROC SQL syntax error

LinusH — Tue, 23 Feb 2021 09:58:51 GMT

Not sure why you have a min() aggregate function around the case statement, and have no GROUP BY?