Help using Base SAS procedures

Insert into a table in Oracle Using Proc SQL;

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Insert into a table in Oracle Using Proc SQL;


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


Accepted Solutions
Solution
‎02-18-2018 12:39 PM
PROC Star
Posts: 508

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

[ Edited ]
Posted in reply to Pradeepbanu
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

 

 

 

View solution in original post


All Replies
PROC Star
Posts: 508

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

Posted in reply to Pradeepbanu

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

Occasional Contributor
Posts: 13

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

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

 

PROC Star
Posts: 508

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

Posted in reply to Pradeepbanu

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; ;
Occasional Contributor
Posts: 13

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

[ Edited ]

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

 

 

 

 

Solution
‎02-18-2018 12:39 PM
PROC Star
Posts: 508

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

[ Edited ]
Posted in reply to Pradeepbanu
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

 

 

 

Occasional Contributor
Posts: 13

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

Thank you so much!! Really appreciate your effort!! Smiley Happy
Super User
Super User
Posts: 8,068

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

Posted in reply to Pradeepbanu

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 497 views
  • 3 likes
  • 3 in conversation