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
29
30
31
32 FROM ATS_MASTER_QUEUES MQ,
33 ATS_ORD_SCRATCH_PAD SP,
34 ATS_ORDERS AO
35
36
37
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.
Hi
try changing
datepart(ao.agreed_delivery_datetime) FORMAT=date9. as Delivery_Date,
to
datepart(ao.agreed_delivery_datetime) as Delivery_Date FORMAT=date9.,
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
29
30
31
32 FROM ATS_MASTER_QUEUES MQ,
33 ATS_ORD_SCRATCH_PAD SP,
34 ATS_ORDERS AO
35
36
37
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).
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,
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
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: