topic Expression using IN has components that are of different data types in SAS Procedures

Expression using IN has components that are of different data types

xliu1 — Thu, 09 Sep 2021 19:41:49 GMT

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;

Re: Expression using IN has components that are of different data types

Kurt_Bremser — Thu, 09 Sep 2021 19:55:31 GMT

What is the type of new_val?

Re: Expression using IN has components that are of different data types

Shmuel — Thu, 09 Sep 2021 20:06:37 GMT

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.

Re: Expression using IN has components that are of different data types

xliu1 — Thu, 09 Sep 2021 20:19:22 GMT

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.