Hello,
I couldn't figure out why I got this message. Is it because PUT function used in query? Could you please help? Thank you!
ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended.
proc sql;
&Database_Login.;
create table temp as
select * from connection to oracle
(select ID
,STORE_NUMBER
,QTY
FROM DB.PAYMENT_TBL as A,
(SELECT DISTINCT put(STORE_NUMBER_n, z7.) as STORE_NUMBER FROM DB.REF_STORE_NUMBER) as C
WHERE A.STORE_NUMBER = C.STORE_NUMBER
and trunc(SALE_DATETIME) BETWEEN '01Oct2017' AND '01Nov2017'
);
Disconnect from oracle;
quit;
and trunc(SALE_DATETIME) BETWEEN '01Oct2017' AND '01Nov2017'
I don't believe that's how you specify dates in Oracle. I think it's 'YY-MM-DD' instead of date9 format.
A good way to check is to test your query in Oracle and see if it runs or not.
@Ying wrote:
Hello,
I couldn't figure out why I got this message. Is it because PUT function used in query? Could you please help? Thank you!
ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended.
proc sql;
&Database_Login.;
create table temp as
select * from connection to oracle
(select ID
,STORE_NUMBER
,QTY
FROM DB.PAYMENT_TBL as A,
(SELECT DISTINCT put(STORE_NUMBER_n, z7.) as STORE_NUMBER FROM DB.REF_STORE_NUMBER) as C
WHERE A.STORE_NUMBER = C.STORE_NUMBER
and trunc(SALE_DATETIME) BETWEEN '01Oct2017' AND '01Nov2017'
);
Disconnect from oracle;
quit;
Thank you, Reeza! The date format is fine. I use it all the time.
@Ying wrote:
Thank you, Reeza! The date format is fine. I use it all the time.
In pass through SQL? Which version of Oracle are you using that support that?
proc sql;
&Database_Login.;
create table temp as
select * from connection to oracle
(select ID
,STORE_NUMBER
,QTY
FROM DB.PAYMENT_TBL as A,
(SELECT DISTINCT put(STORE_NUMBER_n, z7.) as STORE_NUMBER FROM DB.REF_STORE_NUMBER) as C
WHERE A.STORE_NUMBER = C.STORE_NUMBER
and trunc(SALE_DATETIME) BETWEEN '01Oct2017' AND '01Nov2017'
);
Disconnect from oracle;
quit;
I colored in blue the SAS code and in red the Oracle code.
Is there an Oracle put function? How do you expect Oracle to run a SAS function?
Thank you, Chris!
When run the inside piece on SQL developer, I don't need to convert the number to character, and it works. I don't think the PUT function works in here.
You also have to use Oracle-style date literals.
I don't think you even need the put statement, but I do think that you have to identify the dates as date constants, add a format to the store_number_n variable, and check where you have closing parenthses.
I can't help with the Oracle part but, hopefully, this will give you some clues regarding the rest:
data db_ref_store_number;
input store_number_n;
cards;
1
1
2
2
;
data db_payment_tbl;
format store_number z7.;
input id store_number qty;
cards;
1 0000001 5
1 0000001 10
2 0000002 3
2 0000002 5
;
data connection_to_oracle;
informat sale_datetime anydtdtm.;
input x y z sale_datetime;
cards;
4 5 6 10oct2017:18:00
7 8 9 12oct2017:18:00
;
proc sql;
/* &Database_Login.; */
create table temp as
select * from connection_to_oracle
,
(select ID,a.STORE_NUMBER,QTY
FROM DB_PAYMENT_TBL as A),
(SELECT DISTINCT STORE_NUMBER_n as CSTORE_NUMBER format=z7. FROM DB_REF_STORE_NUMBER)
WHERE A.STORE_NUMBER = CSTORE_NUMBER
and datepart(SALE_DATETIME) BETWEEN '01Oct2017'd AND '01Nov2017'd
;
/* Disconnect from oracle; */
quit;
Art, CEO, AnalystFinder.com
Thank you, art297!
without running in Oracle, PUT(STORE_NUMBER_n, z7.) does the same as your FORMAT=Z7.. Either way gives me error.
Then check into the other things I mentioned, especially the use of date constants (in the where statement), and where you have your parentheses.
Art, CEO, AnalystFinder.com
Why are you using the PUT() function in the query? Looks like one table has it as a number and the other has it as a string?
Perhaps you can try doing the conversion the other way? Although there might be some performance issues.
Also make sure your Oracle code uses proper Oracle syntax for dates. For example you appear be just using DDMONYYYY as the default format for date constants, but what format works depends on your settings. https://oracle-base.com/articles/misc/oracle-dates-timestamps-and-intervals You could try using ANSI syntax instead.
You might want to also be clearer about which version of STORE_NUMBER you want. Or for that matter where ID and QTY come from.
proc sql;
&Database_Login.;
create table temp as
select * from connection to oracle
(select A.ID
, A.STORE_NUMBER
, A.QTY
FROM DB.PAYMENT_TBL as A
, (SELECT DISTINCT STORE_NUMBER_n FROM DB.REF_STORE_NUMBER) as C
WHERE cast(A.STORE_NUMBER as integer) = C.STORE_NUMBER_n
and trunc(SALE_DATETIME) BETWEEN DATE '2017-10-01' AND DATE '2017-11-01'
)
;
disconnect from oracle;
quit;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: