Expression using IN has components that are of different data types

[xliu1]

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;

[Kurt_Bremser]

What is the type of new_val?

[Shmuel · Accepted solution]

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]

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.