%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.
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
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
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!!!
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; ;
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 ....
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
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.