extract last obs from Oracle dataset

[Patrick · Accepted solution]

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.

[Tom]

If you want help discovering the connection details for an existing libref you can use this macro:

https://github.com/sasutils/macros/blob/master/dblibchk.sas

[acordes]

Thanks to all for your contribution.
Finally what has worked best for me was to use a proc contents on the oracle table, read every variable with date related format into a macro variable and use these in the data step that loads from oracle.
By Descending &date_vars. ;
And the obs= option in the set line.

The only difficulty that arose was to handle cases with no data variables available.