Table Creation via SELECT (CTAS)

The script begins by cleaning up any existing tables (`ts_STRING`, `mydblibsas`, `ysas`) in the `mydblib` library. It then proceeds to create a table `ts_STRING` within `mydblib`, inserting a current datetime value into it. Two distinct `PROC SQL` blocks are then used to execute CTAS operations. The first creates the table `mydblibsas` by selecting data from `ts_STRING` via a generic DBMS connection. The second creates the table `ysas` by replicating this operation, but explicitly specifying `READ_METHOD=JDBC` in the connection options. `sastrace` options are enabled for SQL debugging, and each CTAS operation is followed by a display of the created data via a `DATA _NULL_` in the SAS^© log.

Data Analysis

Type : MIXTE

The source table `ts_STRING` is programmatically created by the script in the `mydblib` library, which is itself a connection to an external DBMS. The tables `mydblibsas` and `ysas` are then created from `ts_STRING` within the same external DBMS.

1 Code Block

PROC DELETE

Explanation :
This block deletes the temporary tables (`ts_STRING`, `mydblibsas`, `ysas`) from the `mydblib` library if they exist, thus ensuring a clean environment for script execution.

Copied!

1
2	PROC DELETE
3	DATA=mydblib.ts_STRING;
4
5	RUN;
6	PROC DELETE
7	DATA=mydblib.mydblibsas;
8
9	RUN;
10	PROC DELETE
11	DATA=mydblib.ysas;
12
13	RUN;
14

2 Code Block

DATA STEP Data

Explanation :
This `DATA STEP` block creates a table named `ts_STRING` in the `mydblib` library. This table contains a single column `ts` that stores the current date and time, formatted with a precision of 25.6 characters.

Copied!

1	DATA mydblib.ts_STRING;
2	FORMAT ts datetime25.6;
3	ts=datetime();
4	RUN;

3 Code Block

PROC SQL Data

Explanation :
This segment uses `PROC SQL` to establish a connection to an external DBMS (specified by the `&dbms` and `&CONNOPT` macros). It then executes a 'Create Table As Select' (CTAS) operation to create a new table `mydblibsas` in the `mydblib` library, selecting all data from the `ts_STRING` table via the database connection. `sastrace` options are enabled for debugging. Finally, a `DATA _NULL_` is used to display the contents of the new `mydblibsas` table in the SAS log.

Copied!

1	option sastrace=',,,d' sastraceloc=saslog nostsuffix;
2	PROC SQL;
3	connect to &dbms(&CONNOPT);
4	create TABLE mydblibsas as select * from connection to &dbms(
5	SELECT ts FROM ts_STRING
6	);
7	QUIT;
8
9	option sastrace=',,,' sastraceloc=saslog nostsuffix;
10	DATA _null_; SET mydblibsas; put _all_; RUN;

4 Code Block

PROC SQL Data

Explanation :
This block is similar to the previous one, but it demonstrates an explicit specification of the read method. It uses `PROC SQL` for a CTAS operation, creating the `ysas` table in `mydblib`. The key difference is the inclusion of `READ_METHOD=JDBC` in the DBMS connection string, forcing the use of the JDBC driver for data access. The content of `ysas` is then displayed in the SAS log for verification.

Copied!

1	option sastrace=',,,d' sastraceloc=saslog nostsuffix;
2	PROC SQL;
3	connect to &dbms(&CONNOPT READ_METHOD=JDBC);
4	create TABLE ysas as select * from connection to &dbms(
5	SELECT ts FROM ts_STRING
6	);
7	QUIT;
8
9	option sastrace=',,,' sastraceloc=saslog nostsuffix;
10	DATA _null_; SET ysas; put _all_; RUN;

This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.

Retour à la liste