SAS: Create SAS Dataset from Oracle

SAS is very prevalent in our environment. When testing, I need to create some datasets for the business users so they they can do their UAT (yes, I shouldn't be involved at this point...but I am).

Here's the syntax to do that (I use SAS EG):

LIBNAME dmart oracle path=<oracle_sid> schema=<schema_name> pw=<password> user=<db_username>;

CREATE TABLE sasave.mytable 
AS
SELECT * FROM dmart.mytable;
QUIT;

You can also use the following syntax:

LIBNAME dmart oracle path=<oracle_sid> schema=<schema_name> pw=<password> user=<db_username>;

DATA sasave.mytable_filtered;
SET dmart.mytable
( WHERE = id IN ( 5, 6, 7, 8 ) );

RUN;

If you want to use Oracle SQL (SAS guys and gals call it passthrough SQL), here's the syntax for that:

PROC SQL NOPRINT;

CONNECT TO oracle( username=<username> pw=<password> path="<oracle_sid>");

CREATE TABLE sasave.dual_test
AS
SELECT * 
FROM connection to oracle

( SELECT * FROM DUAL );

disconnect from oracle;

QUIT;