Accepted Solutions
Where does the extra semi-colon come from. It's not in the code as posted.
And because I "stumbled" over the code while doing something else here the logic to derive a DB schema name from a SAS library definition (code for SAS libref myora as per your sample code).
proc sql noprint;
select sysvalue into :ora_schema trimmed
from dictionary.libnames
where
libname="%upcase(myora)"
and sysname='Schema/Owner'
;
quit;
proc sql;
connect using myora;
create table want as
select * from connection to myora
(
SELECT *
FROM &ora_schema..fact_ope_form
limit 10000;
)
;
disconnect from myora;
quit;
IF you've got already a SAS library defined that points to your Oracle schema then I'd be using the connect using syntax as it avoids passing explicit connection info in code - especially credentials. If there isn't such a pre-assigned SAS library then ideally use an authentication domain instead of explicit credentials.
If developing explicit pass-through SQL I normally use a two step approach:
1. First develop the DB specific pass-through SQL syntax using a DB client (i.e. SQL Developer).
2. Once DB SQL fully working "wrap" SAS around it.
@Sajid01 @Kurt_Bremser I share with you the time it takes doing different approaches.
The by descending performs much faster.
However the pre-requisite is that you know a sorting criteria that makes sense.
In some case this is not given, therefore for me a valid choice is to sort by descending _all_ assuming that the oracle table has more recent observations at the end.
The summary screenshot has more weight on recent years of contract signing date and somehow (in this case) mimics a random selection. That's not a very scientific approach, but for my case at hand it's acceptable as I'm using all the oracle tables for cas actions in the context of sample data, cardinality, robust univariate statistics, outliers, distinct count, ...
I've have tried as well using fedsql for accessing the oracle table, but it takes longer.
Thanks to all for your support.
As you already found out Oracle tables are not sequential. Data is stored arbitrary and there is no concept of first row or last row in a table. If you don't request a result set to be returned in a specific order (order by ...) then the row order returned can vary between executions for the exactly same code.
The bottleneck is the data transfer between Oracle and SAS. If you've got an Oracle table with 10 million rows but only need 10000 on the SAS side then you should reduce the data volumes on the Oracle side and only transfer what you need to the SAS side.
If the only thing you care about is the number of rows you get on the SAS side then using explicit pass-through SQL with the LIMIT clause will likely outperform any other approach.
proc sql;
connect using myora;
create table want as
select * from connection to myora
(
SELECT *
FROM <schema name>.fact_ope_form
limit 10000;
)
;
disconnect from myora;
quit;
If you don't know the Oracle schema name then one way to get it easily is to execute below which will print the library definition with the schema name to the SAS log.
libname myora list;
You could of course also query the SAS dictionary tables to retrieve the schema name dynamically.
....and in case this needs to execute in CAS then look-up how explicit pass-through SQL works for CAS. You could certainly not use Proc SQL. Proc FedSQL should work or there are eventually also some CAS actions sets for this.
What makes me think you're using SAS Viya CAS is your usage of a by statement without a Proc Sort.
If that's what you're doing then add the following options right before your data step.
OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;
These options will print into the SAS log the SQL that SAS generates and sends to Oracle for execution. If you're lucky then CAS uses the data set OBS= option and translates it into a Oracle SQL limit clause. If that's the case then just use the data step you've got without a by statement.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
-
4887 views
-
7 likes
-
-
