CAS-Action! Rename Columns in a CAS Table

Welcome back to my SAS Users blog series CAS Action! – a series on fundamentals. If you’d like to start by learning more about the distributed CAS server and CAS actions, please see CAS Actions and Action Sets – a brief intro. Otherwise, let’s learn how to rename columns in CAS tables.

In this example, I will use the CAS language (CASL) to execute the alterTable CAS action. Be aware, instead of using CASL, I could execute the same action with Python, R and more with some slight changes to the syntax for the specific language. Refer to the documentation for syntax in other languages.

Load the demonstration data into memory

I’ll start by executing the loadTable action to load the WARRANTY_CLAIMS_0117.sashdat file from the Samples caslib into memory in the Casuser caslib. By default the Samples caslib should be available in your SAS Viya environment. Then I’ll preview the CAS table using the columnInfo and fetch CAS actions.

* Connect to the CAS server and name the connection CONN *;
cas conn;
 
proc cas;
   * Specify the output CAS table *;
   casTbl = {name = "WARRANTY_CLAIMS", caslib = "casuser"};
 
   * Load the CAS table *;
   table.loadtable / 
      path = "WARRANTY_CLAIMS_0117.sashdat", caslib = "samples",
      casOut = casTbl;
 
    * Preview the CAS table *;
    table.columnInfo / table = casTbl;
    table.fetch / table = casTbl, to = 5;
quit;

The columnInfo action returns information about each column. Notice that the WARRANTY_CLAIMS CAS table has column names and columns labels.

The fetch CAS action returns five rows.

Notice that by default the fetch action uses columns labels in the header.

Rename columns in a CAS table

To rename columns in a CAS table, use the alterTable CAS action. In the alterTable action, specify the CAS table using the name and caslib parameters. Additionally, use the columns parameter to specify the columns to modify. The columns parameter requires a list of dictionaries, each dictionary specifies the column to modify.

Here, I’ll rename the claim_attribute_1, seller_attribute_5 and product_attribute_1 columns. Then I’ll execute the columnInfo action to view the updated column information.

proc cas;
   * Reference the CAS table *;
   casTbl = {name = "WARRANTY_CLAIMS", caslib = "casuser"};
 
   * Rename columns *;
   table.alterTable / 
      name = casTbl['name'], caslib = casTbl['caslib'],
      columns = {
	{name = 'claim_attribute_1', rename = 'Campaign_Type'},
	{name = 'seller_attribute_5', rename = 'Selling_Dealer'},
	{name = 'product_attribute_1', rename = 'Vehicle_Class'}
      };
 
   * View column metadata *;
   table.columnInfo / table = casTbl;
quit;

The results show that the alterTable CAS action renamed the columns to Campaign_Type, Selling_Dealer and Vehicle_Class. While this worked, what if you wanted to rename all columns in the CAS table using the column labels?

Rename all columns using the column labels

I’ll dynamically rename the CAS table columns using the column labels. Since the column labels contain spaces, I’ll also replace all spaces with an underscore. Now, I could manually specify each column and column label in the alterTable action, but why do all that work? Instead you can dynamically create a list of dictionaries for use in the alterTable action.

proc cas;
* Reference the CAS table *;
   casTbl = {name = "WARRANTY_CLAIMS", caslib = "casuser"};
 
  * Rename columns with the labels. Spaces replaced with underscores *;
 
   *1. Store the results of the columnInfo action in a dictionary *;
   table.columnInfo result=cr / table = casTbl;
 
   *Loop over the columnInfo result table and create a list of dictionaries *;
   *2*;
   listElementCounter = 0;
   *3*;
   do columnMetadata over cr.ColumnInfo;
	*4.1*; listElementCounter = listElementCounter + 1;
	*4.2*; convertColLabel = tranwrd(columnMetadata['Label'],' ','_');
	*4.3*; renameColumns[listElementCounter] = {name = columnMetadata['Column'], rename = convertColLabel};
   end;
 
   *5. Rename columns *;
   table.alterTable / 
	name = casTbl['Name'], 
	caslib = casTbl['caslib'], 
	columns=renameColumns;
 
   *6. Preview CAS table *;
   table.columnInfo / table = casTbl;
quit;

1. The columnInfo action will store the results in a dictionary named cr.
2. The variable listElementCounter will act as a counter that can be used to append each dictionary to the list.
3. Loop over the result table stored in the cr dictionary. When you loop over a result table, each row is treated as a dictionary. The key is the column name and it returns the value of that column.
4. In the loop:
   1. accumulate the counter
   2. access the column label and replace all spaces with underscores using the tranwrd function
   3. create a list named renamedColumns that contains each dictionary with the column to rename and it’s new name.
5. The alterTable action will use the list of dictionaries to rename each column.
6. The columnInfo action will display the new column information.

The results show that each column was dynamically renamed using the column label and the spaces replaced with underscores.

Summary

In summary, using the alterTable CAS action enables you to rename columns in a CAS table.  With some knowledge of lists, dictionaries and loops in the CAS language, you can dynamically use the column labels to rename the columns. When using the alterTable action remember that:

• The name and caslib parameters specify the CAS table.
• The columns parameter requires a list of dictionaries.
• Each dictionary specifies the column to modify.

Want to learn how to do this using Python? Check out my post Getting started with Python integration to SAS® Viya® – Part 11 – Rename Columns.

Additional resources

simple.freq CAS action
SAS® Cloud Analytic Services: CASL Programmer’s Guide 
CAS Action! – a series on fundamentals
Getting Started with Python Integration to SAS® Viya® – Index
SAS® Cloud Analytic Services: Fundamentals

CAS-Action! Rename Columns in a CAS Table was published on SAS Users.