topic ERROR: Expression using IN has components that are of different data types. in SAS Programming

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

nid197 — Tue, 07 Mar 2017 08:57:41 GMT

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

Kurt_Bremser — Tue, 07 Mar 2017 09:02:15 GMT

The message says it all.

To get real help, post the log (step and messages), and information about the data involved. Output from proc contents comes to mind.

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

gamotte — Tue, 07 Mar 2017 09:04:36 GMT

SAS has two data types : character and numeric.When making a comparison, the variable has to be of the same

type than your list of values.

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

nid197 — Tue, 07 Mar 2017 09:05:36 GMT

22 GOPTIONS ACCESSIBLE;
23 /*%let status_date_start='2016-12-01';
24 %let status_date_end='2016-12-31';*/
25

26 proc sql;
27 create table adhoc_wl_new as
28 select
29 a.jrn_flag,
30 a.wr_no,
31 b.WR_RFA_NO,
32 a.entry_date,
33 a.status_date,
34 a.close_std_date,
35 a.business_category,
36 a.place_id,
37 a.company_code,
38 a.postal_dist,
39 a.prime_rfa_no,
40 a.region_code,
41 a.operating_district_code,
42 a.cc_sent,
43 a.hdis_status_date,
44 b.previous_wr_no,
45 b.previous_wr_rfa_no,
46 b.prev_field_component_vst_seq,
47 b.ACTN_TYPE_CODE as Action_Type,
48 b.OBJ_TYPE_CODE as Appliance_Name,
49 b.APP_CON_TYPE_CODE as LOS,
50 b.JOB_STATUS_CODE,
51 c.visit_activity_code,
52 c.wr_rfa_no,
53 c.wr_no,
54 c.product_group_code,
2 The SAS System 08:21 Tuesday, March 7, 2017

55 c.visit_activity_code,
56 count(c.acty_cmpnent_vst_status_code) as count_act,
57 max(c.acty_cmpnent_vst_date) as actvst_dt,
58 sum(input(c.acv_duration,6.)) as acv_duration
59
60 /*SUBSTR(a.status_date,1,10) as newdate*/
61
62 from hdp_phc.WMIS_WORK_REQUEST a inner join hdp_phc.WMIS_WR_RFA b
63 on(a.wr_no=b.wr_no)
64 and a.jrn_flag = b.jrn_flag
65 and a.jrn_flag = 'ORIGINAL'
66
67 left join hdp_phc.WMIS_ACTIVITY_COMPONENT_VISIT c
68 on c.wr_no = a.wr_no
69 and c.WR_RFA_NO = b.WR_RFA_NO
70 and c.jrn_flag = a.jrn_flag
71
72 where a.WR_NO in
73 ('1300085728','1300001528','1300001828','1300002528','1300002728')
74 and date(input(status_date,10.)) between '2016-12-01' and '2016-12-31'
75 /*and date(status_date) between &status_date_start and &status_date_end
76 /*and newdate between "2016-12-01" and "2016-12-31"*/
77 /*and a.status_date between "2016-12-01:23:59:59:0"dt and "2016-12-31:23:59:59:0"dt*/
78
79 group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27;
WARNING: Function DATE requires at most 0 argument(s). The extra one(s) will be ignored.
ERROR: Expression using IN has components that are of different data types.

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

LinusH — Tue, 07 Mar 2017 09:14:30 GMT

I'm going out on a limb here, but my guess is that wr_no is numeric, which you can't compare to character constants.

For the second error, it seems like you are mixing SAS and non SAS SQL function.

Date function will give you today's date.

There are two other ways to accomplish your task in SAS:

Use SAS date constants using the d literal: '01Dec2016'd. The no need to tamper with your date column.
Use put(status_date,yymmdd10.) to make you date column comparable to your date style constants. I would say that this is the least preferable option from a performance perspective at least.

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

nid197 — Tue, 07 Mar 2017 09:25:23 GMT

i had already tried your solution ie. '01Dec2016'd but it is still not working.

and wr_no error has occured just after i had used the date condition earlier it was working fine by extracting the required data.

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

RW9 — Tue, 07 Mar 2017 09:55:02 GMT

It is quite clear from the text you give from the log:

72 where a.WR_NO in
73 ('1300085728','1300001528','1300001828','1300002528','1300002728')
ERROR: Expression using IN has components that are of different data types.

This error is telling you that the varible - and this might be key as in the select you take C.wr_no - that A.wr_no is numeric and the in list you give is character, therefore it cannot compare. Maye you meant to use c.wr_no for this if that is character, or may you need put(a.wr_no,best.) in().

74 and date(input(status_date,10.)) between '2016-12-01' and '2016-12-31'

WARNING: Function DATE requires at most 0 argument(s). The extra one(s) will be ignored.

The above warning is telling you that SAS has a function called date(), and that that function does not have any parameters - all it does it to return todays date. You are mixing this up with a database function called date() which probably converts text to date values. To do this calculation you need to use SAS functions:

and input(status_date,yymmdd10.) between '01DEC2016'd and '31DEC2016'd