SAS Viya 3.5: What’s new in PROC CASUTIL?

[#1]

PROC CASUTIL has a variety of capabilities.  However, most of us likely use it to simply load data into CAS and possibly replace or promote a table to global scope.  The CASUTIL procedure can load data sets from SAS libraries and files from multiple data sources associated with a CASLIB.  However, SAS Data sets and external files do not have to be associated with a CASLIB. PROC CASUTIL can use a libname engine (via the LOAD DATA statement) or an external file or fileref (via the LOAD FILE statement).  No input CASLIB is required for either of these approaches.  PROC CASUTIL can also save CAS tables to the data source associated with a CASLIB, provide information on CAS tables and data source files, and drop CAS tables and delete files from the data source associated with a CASLIB.

Beginning with SAS Viya 3.5, several new parameters and options are available in the CASUTIL procedure.  Code examples are provided to illustrate some of these new capabilities.

• The new INDEX statement provides the capability to create indexes on one or more variables. The INDEXVARS statement specifies the list of variable(s) to create indexes for in your output CAS table.

/* LOAD data to CAS then create INDEX */ 
proc casutil sessref=mysess; 
load casdata="cars.sas7bdat" outcaslib="casuser" casout="cars" replace 
importoptions=(filetype="basesas" dtm="auto" debug="dmsglvli"); 
quit; 
 
proc casutil sessref=mysess; 
index casdata="cars" incaslib="casuser" outcaslib="casuser" casout="cars_index" replace 
indexvars={"make", "origin"}; 
quit;

OR

/* Create INDEX during LOAD to CAS */ 
proc casutil sessref=mysess; 
load casdata="cars.sas7bdat" outcaslib="casuser" casout="cars" replace 
importoptions=(filetype="basesas" dtm="auto" debug="dmsglvli"); 
index casdata="cars" incaslib="casuser" outcaslib="casuser" casout="cars_index" replace 
indexvars={"make", "origin"}; 
quit;

In either case, the log will display the following message:

NOTE: The Cloud Analytic Services server processed the request in 0.064576 seconds.

NOTE: Indexes were successfully added to the table "cars_index" in the "casuser" caslib.

Output from a PROC CONTENTS will show the indexes that have been applied.

• The new ALTERTABLE statement enables you to modify your CAS tables. You can perform operations such as:
  • RENAME your CAS table.
  • Specify a new LABEL for your CAS table.
  • DROP or KEEP variables.
  • Specify the order of the columns with the COLUMNORDER statement.
  • Modify specific COLUMNS such as assigning a new format, label, or name.

NOTE: Only one of the DROP, KEEP, or COLUMNORDER parameters can be used at one time.

/* LOAD data to CAS then ALTER table */ 
proc casutil sessref=mysess; 
load casdata="cars.sas7bdat" outcaslib="casuser" casout="cars" replace 
importoptions=(filetype="basesas" dtm="auto" debug="dmsglvli"); 
quit; 
 
proc casutil sessref=mysess; 
altertable casdata="cars" incaslib="casuser" label="Altered CARS Table" 
rename="cars_altered" 
/* keep={"make", "model", "type", "origin"} */ 
columnorder={"origin", "make", "model", "type"}; 
quit; 

OR

/* ALTER table during LOAD to CAS */ 
proc casutil sessref=mysess; 
load casdata="cars.sas7bdat" outcaslib="casuser" casout="cars" replace 
importoptions=(filetype="basesas" dtm="auto" debug="dmsglvli"); 
altertable casdata="cars" incaslib="casuser" label="Altered CARS Table" 
rename="cars_altered" 
/* keep={"make", "model", "type", "origin"} */ 
columnorder={"origin", "make", "model", "type"}; 
quit;

In either case, the log will display the following message:

NOTE: The Cloud Analytic Services server processed the request in 0.027774 seconds.

NOTE: Table "cars" was successfully renamed in the "casuser" caslib to table "cars_altered".

NOTE: Label for table "cars" in the "casuser" caslib was successfully updated to "Altered CARS Table".

NOTE: Column information for table "cars" in the "casuser" caslib was successfully updated.

• The existing LOAD statement has been upgraded to support reading external files using a filref. It also supports the WRITETRANSFERSIZE= option, specifying the maximum number of bytes written during the data transfer to CAS.

• The new PARTITION statement enables you to partition a table during the data transfer to CAS.
  • Use the optional PARTITIONBY | GROUPBY statements to name the variables to use for grouping.

/* LOAD data to CAS then PARTITION */ 
proc casutil sessref=mysess; 
load casdata="cars.sas7bdat" outcaslib="casuser" casout="cars" replace 
importoptions=(filetype="basesas" dtm="auto" debug="dmsglvli"); 
quit; 
 
proc casutil sessref=mysess; 
partition casdata="cars" incaslib="casuser" outcaslib="casuser" casout="cars_part" replace 
partitionby=(make origin); 
quit; 

OR

/* PARTITION data during LOAD to CAS */ 
proc casutil sessref=mysess; 
load casdata="cars.sas7bdat" outcaslib="casuser" casout="cars" replace 
importoptions=(filetype="basesas" dtm="auto" debug="dmsglvli"); 
partition casdata="cars" incaslib="casuser" outcaslib="casuser" casout="cars_part" replace 
partitionby=(make origin); 
quit;

In either case, the log will display the following message:

NOTE: The Cloud Analytic Services server processed the request in 0.07634 seconds.

NOTE: The table "cars_part" in the "casuser" caslib was successfully partitioned.

• The existing PROMOTE statement has been enhanced to support the QUIET option if you want to suppress the status and severity messages.

• The new UPDATE statement allows you to update rows in a CAS table.
  • Use the SET statement to specify the variable to update and the value to set. In conjunction with the WHERE parameter, updates can be limited to specific rows.

/* LOAD data to CAS then UPDATE */ 
proc casutil sessref=mysess; 
load casdata="cars.sas7bdat" outcaslib="casuser" casout="cars" replace
importoptions=(filetype="basesas" dtm="auto" debug="dmsglvli"); 
quit; 
 
proc casutil sessref=mysess; 
update casdata="cars" incaslib="casuser" 
set={{var="cylinders", value="12"}} 
where="invoice > 30000"; 
quit;

OR

/* UPDATE data during LOAD to CAS */ 
proc casutil sessref=mysess; 
load casdata="cars.sas7bdat" outcaslib="casuser" casout="cars" replace 
importoptions=(filetype="basesas" dtm="auto" debug="dmsglvli"); 
update casdata="cars" incaslib="casuser" 
set={{var="cylinders", value="12"}} 
where="invoice > 30000"; 
quit;

Partial output of the updated table.

There are many more enhancements to existing procedures and action sets in SAS Viya 3.5.  Please consult the documentation on www.sas.com once SAS Viya 3.5 is available in November 2019.