Insert into a table in Oracle Using Proc SQL;

[Pradeepbanu]

%let usr="usernm";
%let psswd="12345";
%let path=PATHORAC;

%put path: &path;

proc sql;

connect to oracle (user=&usr
password=&psswd
path=&path
buffsize=5000);

create table work.SAS_Entity_date as
select *
from connection to oracle

INSERT INTO ENTITY_DATES_WK13
SELECT DISTINCT 0,NULL WEEK_NAME,TO_CHAR(WEEK_BEGIN,'YYYYMMDD') WEEK_BEGIN,TO_CHAR(WEEK_END,'YYYYMMDD') WEEK_END,NULL WEEK_ABBREVIATION,WEEK_ABBREVIATION,WEEK_NAME,
WEEK_LABEL
FROM CW_CALENDAR_DATE
WHERE CALENDAR_DATE_KEY BETWEEN
(

SELECT TO_CHAR( TO_DATE(AS_OF_DATE,'YYYYMMDD')-98,'YYYYMMDD' ) AS WEEK_13
FROM
(
SELECT MAX(REPORTING_DATE_KEY) AS_OF_DATE FROM FACT_TABLE
)
) AND

(
SELECT MAX(REPORTING_DATE_KEY) AS_OF_DATE FROM FACT_TABLE
)
ORDER BY WEEK_BEGIN DESC;


(select *
from flm7677.ENTITY_DATES_WK13);
quit;

proc print data = work.SAS_Entity_date;
run;

Do subqueries like this are supported in this proc? If not what would be the better way? I am fairly new to SAS, sorry if this is a simple question.

[kiranv_]

the whole query is in oracle. 

when you use connect statement query is directly sent to Oracle.

subquery are allowed in oracle, SAS and almost all versions where SQL is implemented.

Are  you have any issues with query

[Pradeepbanu]

Yes I have Issue in the query..

After INSERT INTO it throws me this error

ERROR 79-322: Expecting a (

after order by it throws me 

ERROR 180-322: Statement is not valid or it is used out of proper order.

I understand somewhere I am doing wrong in semicolon and (, help to write this query properly in SAS proc SQL;

1. I wanna Insert the Select statement value in Entity_date table

2. And store that as SAS dataset in external location as well.

Thanks in Advance!!!

[kiranv_]

try this and let me know

proc sql;

connect to oracle (user=&usr
password=&psswd
path=&path
buffsize=5000);

create table work.SAS_Entity_date as
select *
from connection to oracle

(
SELECT DISTINCT 0,NULL WEEK_NAME,TO_CHAR(WEEK_BEGIN,'YYYYMMDD') WEEK_BEGIN,TO_CHAR(WEEK_END,'YYYYMMDD') WEEK_END,NULL WEEK_ABBREVIATION,WEEK_ABBREVIATION,WEEK_NAME,
WEEK_LABEL
FROM CW_CALENDAR_DATE
WHERE CALENDAR_DATE_KEY BETWEEN
(

SELECT TO_CHAR( TO_DATE(AS_OF_DATE,'YYYYMMDD')-98,'YYYYMMDD' ) AS WEEK_13
FROM
(
SELECT MAX(REPORTING_DATE_KEY) AS_OF_DATE FROM FACT_TABLE
)
) AND

(
SELECT MAX(REPORTING_DATE_KEY) AS_OF_DATE FROM FACT_TABLE
))
  disconnect from oracle; 
    quit; ;

[Pradeepbanu]

Fantastic!!! It works..,

When I try adding INSERT INTO ENTITY_DATES_WK13 (this table is in Oracle DB) on the top of Select it throws me the error 

ERROR: ORACLE prepare error: ORA-24333: zero iteration count.

SQL statement: insert INTO flm7677.ENTITY_DATES_WK13 Select ....

[kiranv_ · Accepted solution]

proc sql;

connect to oracle (user=&usr
password=&psswd
path=&path
buffsize=5000);



execute(
insert into oracletable
SELECT DISTINCT 0,NULL WEEK_NAME,TO_CHAR(WEEK_BEGIN,'YYYYMMDD') WEEK_BEGIN,TO_CHAR(WEEK_END,'YYYYMMDD') WEEK_END,NULL WEEK_ABBREVIATION,WEEK_ABBREVIATION,WEEK_NAME,
WEEK_LABEL
FROM CW_CALENDAR_DATE
WHERE CALENDAR_DATE_KEY BETWEEN
(

SELECT TO_CHAR( TO_DATE(AS_OF_DATE,'YYYYMMDD')-98,'YYYYMMDD' ) AS WEEK_13
FROM
(
SELECT MAX(REPORTING_DATE_KEY) AS_OF_DATE FROM FACT_TABLE
)
) AND

(
SELECT MAX(REPORTING_DATE_KEY) AS_OF_DATE FROM FACT_TABLE
)) by oracle;
  disconnect from oracle; 
    quit; ;

proc sql;    
  connect to oracle (user=&usr
password=&psswd
path=&path
buffsize=5000); 

   create table sastab.newtable as
  select * 
     from connection to oracle      
        (select *          
           from oracletable);  

      disconnect from oracle; 
    quit;

create sas table needs oracle select. that is why the error.

select * from (insert into does not work)

select * from (select * from table). to do your kind of query you need to do it a different way

first do insert into oracle table and then do select

[Pradeepbanu]

Thank you so much!! Really appreciate your effort!! 🙂

[Tom]

There are two ways to push code into the remote database.  You can use 

SELECT ... FROM CONNECTION TO x ();

Or you can use 

EXECUTE () BYx;

Use the first form when the statement returns results. Use the second form for statements like INSERT that do not return results.