BookmarkSubscribeRSS Feed
xliu1
Quartz | Level 8

I keep getting error message from sas log for the following program and will appreciate any help with this. Thanks!

xliu1_0-1614014076263.png

 

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;
3 REPLIES 3
ChrisHemedinger
Community Manager

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.

Register for SAS Innovate 2025!! The premier event for SAS users, May 6-9 in Orlando FL. Sign up now for the best deals!
utrocketeng
Quartz | Level 8

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.  image.png

LinusH
Tourmaline | Level 20

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

Data never sleeps

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1033 views
  • 0 likes
  • 4 in conversation