topic Re: date format in sql where statement issue in SAS Procedures

date format in sql where statement issue

tmm — Thu, 14 Jun 2012 14:43:43 GMT

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.

Re: date format in sql where statement issue

ballardw — Thu, 14 Jun 2012 14:54:26 GMT

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

Re: date format in sql where statement issue

PGStats — Thu, 14 Jun 2012 14:56:01 GMT

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

Re: date format in sql where statement issue

QLi — Thu, 14 Jun 2012 15:05:31 GMT

Check the informat of FST_SRVC_DT

and try this condition.

WHERE year (A.FST_SRVC_DT BETWEEN)=2010