BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
xliu1
Quartz | Level 8

Hi,

I keep getting error messages on the IN condition in my sas code, cannot figure out what went wrong. Any feedback would be appreciated!

PROC SQL;
CREATE TABLE GR_SCH AS
SELECT SUM(C.STU_SECTN_CRED) AS SCH
FROM SID.SID_COURSES_TAKEN C
WHERE C.TERMID IN ('202005', '202008', '202101') AND C.BENCH='E' AND C.CRS_NBR > '4999'
      AND (C.CIP_CRS NOT IN ('511201', '513818', '512308', '512001') OR 
           C.OASIS_PIDM IN 
              (SELECT NEW_VAL
               FROM DWHOUSE.HUB_XREF_RULE_V_D3 R
               WHERE R.SOURCE_NAME = 'HUB' AND R.KEY_WORD = 'IPEDS'
                AND R.QUALIFIER = '12M_EX_IN' AND R.IN_VAL1 = '20202021'
                AND R.IN_VAL2 = '1USF' AND R.EFFECTIVE_STATUS = 'A'
                AND R.EFFECTIVE_DATE =
                    (SELECT MAX(F.EFFECTIVE_DATE)
                       FROM DWHOUSE.HUB_XREF_RULE_V_D3 F
                      WHERE F.XREF_ID = R.XREF_ID
                        AND INPUT(F.EFFECTIVE_DATE, yymmdd10.) <= TODAY())));
QUIT;

xliu1_1-1631216361601.png

 

xliu1_0-1631216319870.png

xliu1_2-1631216453004.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

IN operator requires that variables on both sides of the operator be of the same type, either numeric or character type.

 

Check is c.termid is char type? and is c.cip_crs char type and is c.oasis_pidn is the same type as new_val from dwhouse.

 

For any numeric variable on the left side remove the quotes on the right side from values checked.

 

If new val is of different type than oasis_pidn then use input() function with the char type variable to make it numeric comparison.

 

 

View solution in original post

3 REPLIES 3
Shmuel
Garnet | Level 18

IN operator requires that variables on both sides of the operator be of the same type, either numeric or character type.

 

Check is c.termid is char type? and is c.cip_crs char type and is c.oasis_pidn is the same type as new_val from dwhouse.

 

For any numeric variable on the left side remove the quotes on the right side from values checked.

 

If new val is of different type than oasis_pidn then use input() function with the char type variable to make it numeric comparison.

 

 

xliu1
Quartz | Level 8

Thanks everyone for quick response! I did not pay attention that OASIS_PIDM is a numeric type while new_val is a char type. I used input function to change new_val to numeric type, the code works out.

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
  • 426 views
  • 0 likes
  • 3 in conversation