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.

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 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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