Reading oracle partition in sas using proc sql

Reply
Occasional Contributor
Posts: 8

Reading oracle partition in sas using proc sql

Hello,

           I have Oracle partitions that I'm trying to accessfrom SAS. I am able to access these partitions from Oracle SQL* Developer. However when I use the code in SAS it throws an error:

Here is the code:

connect to oracle(user=&DBUser password=&DBUserPwd path=&DBConn);

create table dir.D1 as select * from connection to oracle

(select count(*) from source_table PARTITION (20140101));

quit;

The error received

ERROR: ORACLE prepare error: ORA-00905: missing keyword.

I tried partition by/for and enclosing the dates in quotes, doesn't seem to work. Any ideas?
Thanks

Valued Guide
Posts: 858

Re: Reading oracle partition in sas using proc sql

instead of 'partition' use 'group by', see if that gets the results you want.

Occasional Contributor
Posts: 8

Re: Reading oracle partition in sas using proc sql

Doesn't work, I get the same error

Valued Guide
Posts: 858

Re: Reading oracle partition in sas using proc sql

are you able to access data from this table otherwise?

Occasional Contributor
Posts: 8

Re: Reading oracle partition in sas using proc sql

yes, I can query that table and retrieve data

Valued Guide
Posts: 858

Re: Reading oracle partition in sas using proc sql

What is it that you are trying to do, can you give an example of the data you have and the data you want?  I think partition is the problem but I'm not sure.

Contributor tlk
Contributor
Posts: 53

Re: Reading oracle partition in sas using proc sql

Have you tried your query directly in SQL+  ?

Occasional Contributor
Posts: 8

Re: Reading oracle partition in sas using proc sql

The below partition query gives the total rows for that particular month in SQL Developer:  The reason in using partition is to expedite the processing speed, I just wanted to get a basic query working before getting into the details ( the data/partition is not the problem)  somehow this has to be tuned to work in SAS:

select count(*) from source_table partition for (20140101) a

where a.date_key between 20140101 and 20140131;

Ask a Question
Discussion stats
  • 7 replies
  • 968 views
  • 0 likes
  • 3 in conversation