topic Re: Insert into a table in Oracle Using Proc SQL; in SAS Procedures

Insert into a table in Oracle Using Proc SQL;

Pradeepbanu — Sun, 18 Feb 2018 16:11:40 GMT

%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.

Re: Insert into a table in Oracle Using Proc SQL;

kiranv_ — Sun, 18 Feb 2018 16:21:27 GMT

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

Re: Insert into a table in Oracle Using Proc SQL;

Pradeepbanu — Sun, 18 Feb 2018 16:27:00 GMT

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!!!

Re: Insert into a table in Oracle Using Proc SQL;

kiranv_ — Sun, 18 Feb 2018 16:43:42 GMT

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; ;

Re: Insert into a table in Oracle Using Proc SQL;

Pradeepbanu — Sun, 18 Feb 2018 17:17:02 GMT

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 ....

Re: Insert into a table in Oracle Using Proc SQL;

kiranv_ — Sun, 18 Feb 2018 20:46:34 GMT

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

Re: Insert into a table in Oracle Using Proc SQL;

Pradeepbanu — Sun, 18 Feb 2018 17:39:39 GMT

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

Re: Insert into a table in Oracle Using Proc SQL;

Tom — Sun, 18 Feb 2018 19:36:09 GMT

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.