Help using Base SAS procedures

date format in sql where statement issue

Reply
Regular Contributor
Regular Contributor
Posts: 238

date format in sql where statement issue

I have two tables that have the same type and format for both dates. My code is this:

PROC SQL;

CREATE TABLE ReAdm.Sum2010 AS

(SELECT DISTINCT

A.MBR_SYS_ID,

A.TIN,

A.FST_SRVC_DT,

A.READMIT,

B.ALLOWED_AMT,

B.ADMIT_DT,

B.DISCH_DT,

B.CUTPUT_DT,

B.START_DT,

B.STAT_DAY,

B.START_DT2,

B.READMIT_DT,

B.REDISCH_DT,

B.CUTPUT_DT2

FROM ReAdm.ReAdm1 A

LEFT JOIN ReAdm.Cleaning3 B

ON A.MBR_SYS_ID = B.MBR_SYS_ID

AND

A.TIN = B.TIN

WHERE

A.FST_SRVC_DT BETWEEN ('01-01-2010') AND ('12-31-2010') AND

A.READMIT >=1 AND B.START_DT2 IS NOT NULL);

RUN;

If I take the date items out it runs fine but putting the date stuff in I get this error:

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

NOTE: The IN referred to may have been transformed from an OR to an IN at some point during PROC

      SQL WHERE clause optimization.

449  RUN;

NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.

Super User
Posts: 11,343

Re: date format in sql where statement issue

If the FST_SRVC_DT variable are SAS dates then you are comparing a numeric to a string '01-01'2010'.

If these are SAS date values you want to reference date literals as '01JAN2010'd and '31DEC201'd

Respected Advisor
Posts: 4,936

Re: date format in sql where statement issue

Change the way you represent datetime litterals:

WHERE

A.FST_SRVC_DT BETWEEN ('01-01-2010') AND ('12-31-2010') AND

for proper datetime constants:

WHERE

A.FST_SRVC_DT BETWEEN '01JAN2010:00:00:00'dt AND '31DEC2010:23:59:59'dt AND

or, if those are date variables (despite the DT in the name)

WHERE

A.FST_SRVC_DT BETWEEN '01JAN2010'd AND '31DEC2010'd AND

PG

PG
Contributor QLi
Contributor
Posts: 59

Re: date format in sql where statement issue

Check the informat  of FST_SRVC_DT

and try  this condition.

WHERE   year (A.FST_SRVC_DT BETWEEN)=2010

Ask a Question
Discussion stats
  • 3 replies
  • 362 views
  • 0 likes
  • 4 in conversation