BookmarkSubscribeRSS Feed
6 REPLIES 6
gamotte
Rhodochrosite | Level 12

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.

nid197
Obsidian | Level 7


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.

LinusH
Tourmaline | Level 20

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.
Data never sleeps
nid197
Obsidian | Level 7

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. 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

 


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6 replies
  • 8342 views
  • 0 likes
  • 5 in conversation