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 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 3 replies
  • 960 views
  • 0 likes
  • 3 in conversation