The script begins by defining macro variables to configure the data source type (e.g., ODBC), the CAS session name, and the caslib alias. It includes parameter placeholders for connection information (username, password, database, schema). A CAS session is initiated, then a caslib is added using the data connector specified by the macro variables. A DATA STEP is then used to create a 'LOADSAMPLE' table in the external DBMS via a libname configured for the connector. This table is then loaded into CAS three times: once in its entirety, once with a WHERE clause to filter rows, and once by selecting specific variables. The tables loaded into CAS memory are verified using PROC PRINT. Finally, the script cleans up resources by deleting the source table from the external DBMS and terminating the CAS session.
Data Analysis
Type : MIXTE
Data is initially defined 'inline' via a DATALINES statement within the script. It is then written to a table ('LOADSAMPLE') in an external database management system (DBMS), whose type is specified by the 'srctype' macro variable (ODBC by default). This data, although created by the script, temporarily resides externally before being loaded and processed in the CAS environment.
1 Code Block
Variables Macro
Explanation :
This block defines essential macro variables for connection configuration. `srctype` specifies the type of database management system (DBMS). `cas_session` and `caslib_alias` respectively name the CAS session and the CAS library. The other variables (`username`, `password`, `database`, `schema`, `CONNOPTS`) are placeholders for authentication and connection information specific to the external DBMS.
This block defines essential macro variables for connection configuration. `srctype` specifies the type of database management system (DBMS). `cas_session` and `caslib_alias` respectively name the CAS session and the CAS library. The other variables (`username`, `password`, `database`, `schema`, `CONNOPTS`) are placeholders for authentication and connection information specific to the external DBMS.
Copied!
| 1 | /* Specify the DBMS engine, for example: ODBC */ |
| 2 | %let srctype=odbc; |
| 3 | |
| 4 | %let cas_session = mysess; |
| 5 | %let caslib_alias = datalib; |
| 6 | |
| 7 | |
| 8 | /*Specify connection parameters within a string |
| 9 | |
| 10 | Edit the below variables with the required DBMS-specific conection options |
| 11 | Also, specify such variables within the CONNOPS string, as this will be used for the other sample programs |
| 12 | |
| 13 | Most datasources will require username, password, database, and schema/server |
| 14 | Please refer to the connopts.xlsx file for specific connection requirements for each DBMS engine |
| 15 | |
| 16 | BigQuery: requires protject and cred_path parameters |
| 17 | Hadoop: requires hadoopJarPath, hadoopConfigDir, and dtm parameters |
| 18 | Impala: requires port and conopts parameters |
| 19 | JDBC: requires url, class, and classpath parameters |
| 20 | Salesforce: requires authendpoint and catalog parameters |
| 21 | SAP Hana: requires instance and tabletype parameters |
| 22 | Teradata: requires dataTransferMode parameter |
| 23 | Oracle, Netezza, and MSSWL require catalog parameter |
| 24 | |
| 25 | */ |
| 26 | |
| 27 | %let username = ???????; |
| 28 | %let password = ???????; |
| 29 | %let database = ???????; |
| 30 | %let schema = ???????; |
| 31 | |
| 32 | %let CONNOPTS=%str(user=??????? |
| 33 | pwd=??????? |
| 34 | dsn=???????); |
2 Code Block
Session CAS
Explanation :
This statement initializes a CAS session with the name specified by the `cas_session` macro variable. The `cashost` and `casport` options are commented out, indicating they may be optional in SAS Studio or defined by the environment.
This statement initializes a CAS session with the name specified by the `cas_session` macro variable. The `cashost` and `casport` options are commented out, indicating they may be optional in SAS Studio or defined by the environment.
Copied!
| 1 | /* Connect to CAS using cashost and casport, optional in SAS Studio */ |
| 2 | /* options cashost="???????" casport=????; */ |
| 3 | |
| 4 | |
| 5 | /* Create a CAS Session */ |
| 6 | cas &cas_session; |
3 Code Block
PROC CAS (action addCaslib)
Explanation :
This block uses `PROC CAS` to execute the `addCaslib` action. This action creates a new caslib (`&caslib_alias`) and connects it to the external DBMS via the defined `srctype`. The parameters `user`, `pass`, `database`, `schema`, and `catalog` are passed as connection options to the data connector, allowing CAS to access data from the DBMS.
This block uses `PROC CAS` to execute the `addCaslib` action. This action creates a new caslib (`&caslib_alias`) and connects it to the external DBMS via the defined `srctype`. The parameters `user`, `pass`, `database`, `schema`, and `catalog` are passed as connection options to the data connector, allowing CAS to access data from the DBMS.
Copied!
| 1 | PROC CAS; |
| 2 | SESSION &cas_session; |
| 3 | ACTION addCaslib caslib="&caslib_alias" |
| 4 | datasource={ srctype="&srctype", |
| 5 | user="&username", |
| 6 | pass="&password", |
| 7 | database="&database", |
| 8 | schema="&schema", |
| 9 | catalog="*"}; |
| 10 | RUN; |
| 11 | QUIT; |
4 Code Block
DATA STEP Data
Explanation :
This DATA STEP is crucial for creating test data. It first defines a libname `loadlib` that points to the external DBMS via the specified `srctype` and `CONNOPTS`. Then, a table named `LOADSAMPLE` is created in this external DBMS using data provided directly in the script via the `datalines` statement. This table simulates an external data source ready to be loaded into CAS.
This DATA STEP is crucial for creating test data. It first defines a libname `loadlib` that points to the external DBMS via the specified `srctype` and `CONNOPTS`. Then, a table named `LOADSAMPLE` is created in this external DBMS using data provided directly in the script via the `datalines` statement. This table simulates an external data source ready to be loaded into CAS.
Copied!
| 1 | /* Create sample load table in DBMS: using DBMS-specific Libname engine*/ |
| 2 | LIBNAME loadlib &srctype &CONNOPTS; |
| 3 | |
| 4 | DATA loadlib.LOADSAMPLE; |
| 5 | INPUT FLIGHT $3. +5 DATES $7. +3 DEPART $5. +2 ORIG $3. |
| 6 | +3 DEST $3. +7 MILES +6 BOARDED +6 CAPACITY; |
| 7 | DATALINES; |
| 8 | 114 01MAR98 7:10 LGA LAX 2475 172 210 |
| 9 | 202 01MAR98 10:43 LGA ORD 740 151 210 |
| 10 | 219 01MAR98 9:31 LGA LON 3442 198 250 |
| 11 | 622 01MAR98 12:19 LGA FRA 3857 207 250 |
| 12 | 132 01MAR98 15:35 LGA YYZ 366 115 178 |
| 13 | 271 01MAR98 13:17 LGA PAR 3635 138 250 |
| 14 | 302 01MAR98 20:22 LGA WAS 229 105 180 |
| 15 | 114 02MAR98 7:10 LGA LAX 2475 119 210 |
| 16 | 202 02MAR98 10:43 LGA ORD 740 120 210 |
| 17 | 219 02MAR98 9:31 LGA LON 3442 147 250 |
| 18 | 622 02MAR98 12:19 LGA FRA 3857 176 250 |
| 19 | 132 02MAR98 15:35 LGA YYZ 366 106 178 |
| 20 | ; |
| 21 | RUN; |
5 Code Block
PROC CAS (action loadtable)
Explanation :
This block uses `PROC CAS` to load the entire `LOADSAMPLE` table (residing in the external DBMS and accessible via `&caslib_alias`) into CAS memory. The in-memory CAS table will be named `SAMDATLOAD`. The `replace=true` option ensures that if a table with the same name already exists, it will be replaced.
This block uses `PROC CAS` to load the entire `LOADSAMPLE` table (residing in the external DBMS and accessible via `&caslib_alias`) into CAS memory. The in-memory CAS table will be named `SAMDATLOAD`. The `replace=true` option ensures that if a table with the same name already exists, it will be replaced.
Copied!
| 1 | /* Proc cas loadtable action: loads entire table*/ |
| 2 | PROC CAS; |
| 3 | SESSION &cas_session; |
| 4 | ACTION loadtable |
| 5 | casout={name="SAMDATLOAD" replace=true} |
| 6 | caslib="&caslib_alias" |
| 7 | path="LOADSAMPLE"; |
| 8 | RUN; |
| 9 | QUIT; |
6 Code Block
PROC CAS (action loadtable avec WHERE)
Explanation :
This block demonstrates conditional data loading. It loads the `LOADSAMPLE` table into CAS under the name `SAMDATLOADWHERE`, but applies a filter (`where="FLIGHT = 114"`) to include only rows where the 'FLIGHT' column has the value '114'.
This block demonstrates conditional data loading. It loads the `LOADSAMPLE` table into CAS under the name `SAMDATLOADWHERE`, but applies a filter (`where="FLIGHT = 114"`) to include only rows where the 'FLIGHT' column has the value '114'.
Copied!
| 1 | /* Proc cas loadtable action with where parameter: subsets the input data */ |
| 2 | PROC CAS; |
| 3 | SESSION &cas_session; |
| 4 | ACTION loadtable |
| 5 | casout={name="SAMDATLOADWHERE" replace=true} |
| 6 | caslib="&caslib_alias" |
| 7 | where="FLIGHT = 114" |
| 8 | path="LOADSAMPLE"; |
| 9 | RUN; |
| 10 | QUIT; |
7 Code Block
PROC CAS (action loadtable avec VARS)
Explanation :
This block illustrates selective column loading. It loads the `LOADSAMPLE` table into CAS under the name `SAMDATLOADVARS`, but only transfers the 'FLIGHT', 'ORIG', and 'DEST' columns, thereby reducing the amount of data in memory.
This block illustrates selective column loading. It loads the `LOADSAMPLE` table into CAS under the name `SAMDATLOADVARS`, but only transfers the 'FLIGHT', 'ORIG', and 'DEST' columns, thereby reducing the amount of data in memory.
Copied!
| 1 | /* Proc cas loadtable action with vars parameter: specifies variables to load */ |
| 2 | PROC CAS; |
| 3 | SESSION &cas_session; |
| 4 | ACTION loadtable |
| 5 | casout={name="SAMDATLOADVARS" replace=true} |
| 6 | caslib="&caslib_alias" |
| 7 | vars={"FLIGHT", "ORIG", "DEST"} |
| 8 | path="LOADSAMPLE"; |
| 9 | RUN; |
| 10 | QUIT; |
8 Code Block
PROC PRINT
Explanation :
This block creates a `caslib` libname of type 'CAS' that references the active CAS session. It then uses `PROC PRINT` to display the content of the three tables loaded into memory (`SAMDATLOAD`, `SAMDATLOADWHERE`, `SAMDATLOADVARS`), allowing verification that the loading and filtering operations were performed correctly.
This block creates a `caslib` libname of type 'CAS' that references the active CAS session. It then uses `PROC PRINT` to display the content of the three tables loaded into memory (`SAMDATLOAD`, `SAMDATLOADWHERE`, `SAMDATLOADVARS`), allowing verification that the loading and filtering operations were performed correctly.
Copied!
| 1 | /* Verify loadtable using caslib libref with CAS Libname engine to print in-memory tables*/ |
| 2 | LIBNAME caslib cas sessref=&cas_session tag=""; |
| 3 | PROC PRINT DATA=caslib.SAMDATLOAD; |
| 4 | RUN; |
| 5 | PROC PRINT DATA=caslib.SAMDATLOADWHERE; |
| 6 | RUN; |
| 7 | PROC PRINT DATA=caslib.SAMDATLOADVARS; |
| 8 | RUN; |
9 Code Block
PROC CAS (action deleteSource)
Explanation :
This cleanup block uses `PROC CAS` with the `deleteSource` action. It deletes the `LOADSAMPLE` table from the external DBMS, which was created by the initial DATA STEP and served as the source for CAS loading operations, thereby releasing external resources.
This cleanup block uses `PROC CAS` with the `deleteSource` action. It deletes the `LOADSAMPLE` table from the external DBMS, which was created by the initial DATA STEP and served as the source for CAS loading operations, thereby releasing external resources.
Copied!
| 1 | /* Clean-up: Delete files from data source associated with caslib */ |
| 2 | PROC CAS; |
| 3 | SESSION &cas_session; |
| 4 | ACTION deleteSource |
| 5 | caslib="&caslib_alias" |
| 6 | SOURCE="LOADSAMPLE" |
| 7 | ; |
| 8 | RUN; |
| 9 | QUIT; |
10 Code Block
Session CAS
Explanation :
This statement terminates the CAS session specified by `&cas_session`, releasing all resources allocated to this session on the CAS server.
This statement terminates the CAS session specified by `&cas_session`, releasing all resources allocated to this session on the CAS server.
Copied!
| 1 | /* Remove CAS |
| 2 | session */ |
| 3 | cas &cas_session. terminate; |
| 4 |
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.
Copyright Info : SAS SAMPLE LIBRARY
