topic Re: Datepart in EG 5.1 SQL in SAS Enterprise Guide

Datepart in EG 5.1 SQL

D_Z_ — Sat, 16 Feb 2013 16:57:55 GMT

Hey All,

I have an issue that i cannot seem to figure out. I am trying to do a datepart as part of my original SQL code. Whenever i do, I get the following that show up in my log. I have used several varying combinations but cannot figure out how to make it work. I am trying to simply split the date away from the time so all i see is the date and DATEPART is not working for me.... I cannot figure out what i am doing wrong. I would appreciate any help from the community on this one.

LOG:

16 Proc SQL noprint;

17 connect to oracle (user='SASAPP' orapw=XXXXXXXXXXX path=R800P preserve_comments);

18 create table work.CSI_CHECK as select * from connection to oracle

19 ( SELECT distinct

20 mq.dt_crtd,

21 ao.agreed_delivery_datetime,

22 datepart(ao.agreed_delivery_datetime) FORMAT=date9. as Delivery_Date,

23 MQ.ODR_ODR_NUMBER,

24 SP.SCRATCH_PAD AS ORDER_NOTES,

25 case

26 when ao.express_product_indicator ='Y' then 'GPT' else 'FPT' end as Channel,

27 ao.express_product_indicator,

28 MQ.USR_CRTD

32 FROM ATS_MASTER_QUEUES MQ,

33 ATS_ORD_SCRATCH_PAD SP,

34 ATS_ORDERS AO

38 WHERE MQ.QUE_TYPE IN ('CSIN')

39 AND MQ.ODR_ODR_NUMBER = SP.ODR_ODR_NUMBER

40 AND MQ.USR_CRTD = SP.USR_CRTD

41 AND MQ.ODR_ODR_NUMBER = AO.ODR_NUMBER

42 );

ERROR: ORACLE prepare error: ORA-00904: "DATEPART": invalid identifier. SQL statement: SELECT distinct mq.dt_crtd,

ao.agreed_delivery_datetime, datepart(max(ao.agreed_delivery_datetime)) as Delivery_Date, MQ.ODR_ODR_NUMBER, SP.SCRATCH_PAD

AS ORDER_NOTES, case when ao.express_product_indicator ='Y' then 'GPT' else 'FPT' end as Channel,

ao.express_product_indicator, MQ.USR_CRTD FROM ATS_MASTER_QUEUES MQ, ATS_ORD_SCRATCH_PAD SP, ATS_ORDERS AO WHERE MQ.QUE_TYPE

IN ('CSIN') AND MQ.ODR_ODR_NUMBER = SP.ODR_ODR_NUMBER AND MQ.USR_CRTD = SP.USR_CRTD AND MQ.ODR_ODR_NUMBER = AO.ODR_NUMBER.

Re: Datepart in EG 5.1 SQL

Linlin — Sat, 16 Feb 2013 17:21:06 GMT

try changing

datepart(ao.agreed_delivery_datetime) FORMAT=date9. as Delivery_Date,

datepart(ao.agreed_delivery_datetime) as Delivery_Date FORMAT=date9.,

Re: Datepart in EG 5.1 SQL

D_Z_ — Sat, 16 Feb 2013 17:27:14 GMT

Thank you Linlin!

I get the following in my log now: (FYI, if i take out the datepart statement the query works as normal)

16 Proc SQL noprint;

17 connect to oracle (user='SASAPP' orapw=XXXXXXXXXXX path=R800P preserve_comments);

18 create table work.CSI_CHECK as select * from connection to oracle

19 ( SELECT distinct

20 mq.dt_crtd,

21 ao.agreed_delivery_datetime,

22 datepart(ao.agreed_delivery_datetime) as Delivery_Date FORMAT=date9.,

23 MQ.ODR_ODR_NUMBER,

24 SP.SCRATCH_PAD AS ORDER_NOTES,

25 case

26 when ao.express_product_indicator ='Y' then 'GPT' else 'FPT' end as Channel,

27 ao.express_product_indicator,

28 MQ.USR_CRTD

32 FROM ATS_MASTER_QUEUES MQ,

33 ATS_ORD_SCRATCH_PAD SP,

34 ATS_ORDERS AO

38 WHERE MQ.QUE_TYPE IN ('CSIN')

39 AND MQ.ODR_ODR_NUMBER = SP.ODR_ODR_NUMBER

40 AND MQ.USR_CRTD = SP.USR_CRTD

41 AND MQ.ODR_ODR_NUMBER = AO.ODR_NUMBER

42 and ao.agreed_delivery_datetime=datepart(ao.agreed_delivery_datetime)

43 );

ERROR: ORACLE prepare error: ORA-00923: FROM keyword not found where expected. SQL statement: SELECT distinct mq.dt_crtd,

ao.agreed_delivery_datetime, datepart(ao.agreed_delivery_datetime) as Delivery_Date FORMAT=date9., MQ.ODR_ODR_NUMBER,

SP.SCRATCH_PAD AS ORDER_NOTES, case when ao.express_product_indicator ='Y' then 'GPT' else 'FPT' end as Channel,

ao.express_product_indicator, MQ.USR_CRTD FROM ATS_MASTER_QUEUES MQ, ATS_ORD_SCRATCH_PAD SP, ATS_ORDERS AO WHERE MQ.QUE_TYPE

IN ('CSIN') AND MQ.ODR_ODR_NUMBER = SP.ODR_ODR_NUMBER AND MQ.USR_CRTD = SP.USR_CRTD AND MQ.ODR_ODR_NUMBER = AO.ODR_NUMBER

and ao.agreed_delivery_datetime=datepart(ao.agreed_delivery_datetime).

Re: Datepart in EG 5.1 SQL

Patrick — Sun, 17 Feb 2013 02:10:54 GMT

You are using explicit SQL pass-through so the SQL within the "connection to oracle" bit must be Oracle SQL flavour. You obviously can't use any SAS syntax like SAS functions and formats there.

I believe what would work is below code:

Proc SQL noprint;

connect to oracle (user='SASAPP' orapw=XXXXXXXXXXX path=R800P preserve_comments);

create table work.CSI_CHECK as

select

dt_crtd,

agreed_delivery_datetime as agreed_delivery_datetime format=datetime21.,

datepart(ao.agreed_delivery_datetime) as Delivery_Date FORMAT=date9.,

ODR_ODR_NUMBER,

SCRATCH_PAD AS ORDER_NOTES,

Channel,

express_product_indicator,

USR_CRTD

from connection to oracle

(

SELECT distinct

mq.dt_crtd,

ao.agreed_delivery_datetime,

MQ.ODR_ODR_NUMBER,

SP.SCRATCH_PAD AS ORDER_NOTES,

case

when ao.express_product_indicator ='Y' then 'GPT'

else 'FPT'

end

as Channel,

ao.express_product_indicator,

MQ.USR_CRTD

FROM ATS_MASTER_QUEUES MQ,

ATS_ORD_SCRATCH_PAD SP,

ATS_ORDERS AO

WHERE MQ.QUE_TYPE IN ('CSIN')

AND MQ.ODR_ODR_NUMBER = SP.ODR_ODR_NUMBER

AND MQ.USR_CRTD = SP.USR_CRTD

AND MQ.ODR_ODR_NUMBER = AO.ODR_NUMBER

and trunc(ao.agreed_delivery_datetime,'day')=trunc(ao.agreed_delivery_datetime,'day')

);

quit;

Link to the docu: TRUNCATE

Re: Datepart in EG 5.1 SQL

D_Z_ — Sun, 17 Feb 2013 05:20:30 GMT

I tried what you suggested as well Patrick, and got the error below. I cannot figure out why i would get a FROM Keyword not found where expected error but if i take it out the query runs just fine.

ERROR: ORACLE prepare error: ORA-00923: FROM keyword not found where expected. SQL statement: SELECT distinct mq.dt_crtd,

agreed_delivery_datetime as agreed_delivery_datetime format=datetime21., datepart(ao.agreed_delivery_datetime) as

Delivery_Date FORMAT=date9., MQ.ODR_ODR_NUMBER, SP.SCRATCH_PAD AS ORDER_NOTES, case when ao.express_product_indicator ='Y'

then 'GPT' else 'FPT' end as Channel, ao.express_product_indicator, MQ.USR_CRTD FROM ATS_MASTER_QUEUES MQ,

ATS_ORD_SCRATCH_PAD SP, ATS_ORDERS AO WHERE MQ.QUE_TYPE IN ('CSIN') AND MQ.ODR_ODR_NUMBER = SP.ODR_ODR_NUMBER AND

MQ.USR_CRTD = SP.USR_CRTD AND MQ.ODR_ODR_NUMBER = AO.ODR_NUMBER and

trunc(ao.agreed_delivery_datetime,'day')=trunc(ao.agreed_delivery_datetime,'day').

Dean

Re: Datepart in EG 5.1 SQL

Patrick — Sun, 17 Feb 2013 08:45:11 GMT

The Error log you posted is not from executing the code I've posted. There is still a datepart() function and the format= in the select clause.

In the code I have posted was actually still a glitch which in below version should be removed. I can't test the code but the reason why you're getting errors is exactly as I said previously.

How did you create the code in first place? Was this just via EG wizard and then this "analyze" bit which converts the code into pass-through SQL? Do you understand the generated SQL? Because if you understand the SQL and where you can use SAS functions, formats,... and where not, then it's really no more difficult to fix.

Proc SQL noprint;

connect to oracle (user='SASAPP' orapw=XXXXXXXXXXX path=R800P preserve_comments);

create table work.CSI_CHECK as

select

dt_crtd,

agreed_delivery_datetime as agreed_delivery_datetime format=datetime21.,

datepart(agreed_delivery_datetime) as Delivery_Date FORMAT=date9.,

ODR_ODR_NUMBER,

SCRATCH_PAD AS ORDER_NOTES,

Channel,

express_product_indicator,

USR_CRTD

from connection to oracle

(

SELECT distinct

mq.dt_crtd,

ao.agreed_delivery_datetime,

MQ.ODR_ODR_NUMBER,

SP.SCRATCH_PAD AS ORDER_NOTES,

case

when ao.express_product_indicator ='Y' then 'GPT'

else 'FPT'

end

as Channel,

ao.express_product_indicator,

MQ.USR_CRTD

FROM ATS_MASTER_QUEUES MQ,

ATS_ORD_SCRATCH_PAD SP,

ATS_ORDERS AO

WHERE MQ.QUE_TYPE IN ('CSIN')

AND MQ.ODR_ODR_NUMBER = SP.ODR_ODR_NUMBER

AND MQ.USR_CRTD = SP.USR_CRTD

AND MQ.ODR_ODR_NUMBER = AO.ODR_NUMBER

and trunc(ao.agreed_delivery_datetime,'day')=trunc(ao.agreed_delivery_datetime,'day')

);

quit;