Hi all.
Assume the code below. I am using 'inobs=1000' or 'obs=1000' options on the proc sql statement to select the top 1000 records. Is there a faster way to get the same result? I thought I could use the WHERE or SELECT TOP statement but I getting errors.
PROC SQL;
CONNECT TO ORACLE as EstCon
(PATH=mypath authdomain=Oracledev);
CREATE TABLE MyTable AS
SELECT *
FROM CONNECTION TO EstCon (
SELECT
t1.ID, t2.name, t3.gender, t4.Age
FROM Table1 t1
INNER JOIN table2 t2 ON (t1.ID = t2._ID)
INNER JOIN table3 t6 ON (t1.ID = t3.ID)
INNER JOIN table4 ON (t1.PARTY_ID = t4.PARTY_ID));
DISCONNECT FROM EstCon;
QUIT;
Thanks Reeza.
I tried it but I got this error msg:
ERROR: ORACLE prepare error: ORA-00923: FROM keyword not found where expected. SQL statement: SELECT TOP 100 * FROM table1 t1 inner join table2 on (t1.ID = t2.ID)...
The most efficient way is to filter the rows in oracle :
PROC SQL;
CONNECT TO ORACLE as EstCon
(PATH=mypath authdomain=Oracledev);
CREATE TABLE MyTable AS
SELECT *
FROM CONNECTION TO EstCon (
SELECT
t1.ID, t2.name, t3.gender, t4.Age
FROM Table1 t1
INNER JOIN table2 t2 ON (t1.ID = t2._ID)
INNER JOIN table3 t6 ON (t1.ID = t3.ID)
INNER JOIN table4 ON (t1.PARTY_ID = t4.PARTY_ID)
WHERE rownum <=1000
);
DISCONNECT FROM EstCon;
QUIT;
This is the alternate query to filter in sas:
PROC SQL outobs=1000;
CONNECT TO ORACLE as EstCon
(PATH=mypath authdomain=Oracledev);
CREATE TABLE MyTable(obs=2) AS
SELECT *
FROM CONNECTION TO EstCon (
SELECT
t1.ID, t2.name, t3.gender, t4.Age
FROM Table1 t1
INNER JOIN table2 t2 ON (t1.ID = t2._ID)
INNER JOIN table3 t6 ON (t1.ID = t3.ID)
INNER JOIN table4 ON (t1.PARTY_ID = t4.PARTY_ID));
DISCONNECT FROM EstCon;
QUIT;
May be try this :
PROC SQL;
CONNECT TO ORACLE as EstCon
(PATH=mypath authdomain=Oracledev);
CREATE TABLE MyTable AS
SELECT *
FROM CONNECTION TO EstCon (
SELECT * FROM ( SELECT
t1.ID, t2.name, t3.gender, t4.Age
FROM Table1 t1
INNER JOIN table2 t2 ON (t1.ID = t2._ID)
INNER JOIN table3 t6 ON (t1.ID = t3.ID)
INNER JOIN table4 ON (t1.PARTY_ID = t4.PARTY_ID)) SAMP
WHERE rownum <=1000
ORDER BY rownum
);
DISCONNECT FROM EstCon;
QUIT;
try rownum.
PROC SQL;
CONNECT TO ORACLE as EstCon
(PATH=mypath authdomain=Oracledev);
CREATE TABLE MyTable AS
SELECT *
FROM CONNECTION TO EstCon (
select * from
( SELECT
t1.ID, t2.name, t3.gender, t4.Age
FROM Table1 t1
INNER JOIN table2 t2 ON (t1.ID = t2._ID)
INNER JOIN table3 t6 ON (t1.ID = t3.ID)
INNER JOIN table4 ON (t1.PARTY_ID = t4.PARTY_ID))
WHERE ROWNUM <= 1000);
DISCONNECT FROM EstCon;
QUIT;
If you want to limit the results of a query to see the first 1000 where order by matters than you might use a sub-query technique additionally.
SELECT *
FROM (SELECT t1.ID, t2.name, t3.gender, t4.Age
FROM Table1 t1
INNER JOIN table2 t2 ON (t1.ID = t2._ID)
INNER JOIN table3 t6 ON (t1.ID = t3.ID)
INNER JOIN table4 ON (t1.PARTY_ID = t4.PARTY_ID)
Order by t1.PARTY_ID)
WHERE ROWNUM <=1000;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.