Getting Started with Python Integration to SAS® Viya® - Part 17 - Saving CAS tables

Welcome to the continuation of my series Getting Started with Python Integration to SAS Viya. In this post I'll discuss saving CAS tables to a caslib's data source as a file. This is similar to saving pandas DataFrames using to_ methods.

Load and preview the CAS table

First, I imported the necessary packages and connected my Python client to the distributed CAS server and named my connection conn. Second, I loaded the warranty_claims_0017.sashdat file from the Samples caslib into memory as a distributed CAS table. The Samples caslib should be available in your environment. Lastly, I referenced the CAS table in the variable castbl and preview the table.

For more information about loading server-side files into memory check out Part 5 - Loading Server-Side Files into Memory.

## Packages
import swat
import os
import pandas as pd
import numpy as np

## Options
pd.set_option('display.max_columns', 50)

## Connect to CAS
conn = swat.CAS() ## enter CAS connection information

## Load the data into CAS
conn.loadTable(path='WARRANTY_CLAIMS_0117.sashdat', caslib='samples',
casout={'name':'warranty_claims',
'caslib':'casuser',
'replace':True})

## Reference the CAS table in a variable
castbl = conn.CASTable('warranty_claims', caslib = 'casuser')

## Preview the CAS table
df = castbl.head()
ci = castbl.columnInfo()
display (df, ci)

# and the results
NOTE: Cloud Analytic Services made the file WARRANTY_CLAIMS_0117.sashdat available as table WARRANTY_CLAIMS in caslib CASUSER(Peter).

Results show five rows and the column metadata of the CAS table. Notice the column names are vague but the column labels are detailed.

Prepare the CAS table

After the table is loaded into memory, I'll prepare the CAS table by completing the following:

Replace column names with the column labels all lower cased and spaces replaced with underscores.

Drop unnecessary columns.

Create a new column named car_id that concatenates the make, product_model and platform columns.

Display the parameters of the CASTable object castbl, and preview five rows.

I won't go into detail of the code. I use a combination of familiar Python and SAS Viya techniques from Part 8 - Creating Calculated Columns and Part 11 - Rename Columns.

##
## RENAME AND DROP COLUMNS
## Use the current column labels as the column names
##

## Store the current column names and labels in a dataframe
df_col_names = castbl.columnInfo()['ColumnInfo'].loc[:,['Column','Label']]

## Create a list of dictionaries of how to rename each column using the column labels
renameColumns = []
for row in df_col_names.iterrows():

## Store current column name
colName = row[1].values[0]

## Store current column label and clean up
labelName = row[1].values[1].replace(' ','_').lower()

## Create a dictionary of the column to rename, and how to rename it
columnDict = dict(name = colName,
rename = labelName)

## Add a date format to columns if the column name contains the word date
if columnDict['rename'].find('date') &gt; 0:
columnDict['format'] = 'DATE9.'

## Create a list of dictionaries, one dictionary for each column
renameColumns.append(columnDict)

## List of columns to drop using the updated columns names
dropColumns = ['defect_code','repairing_division','usage_value','campaign_type','customer_country','ship_year',
'product_unit_assembly_date','primary_replaced_material_group_code','primary_labor_group_code',
'selling_dealer','vehicle_class','ship_date', 'total_claim_count', 'primary_labor_code', 'defect_key', 'primary_replaced__material_id']

## Apply new column names and drop columns to the CAS table
castbl.alterTable(columns = renameColumns,
drop = dropColumns)

##
## CREATE A NEW COLUMN
##

## Create a new column in the table by combining make, model and platform
castbl.eval("car_id = strip(make)|| '-' || strip(product_model) || '-' || strip(platform)")

##
## PREVIEW THE CHANGES
##

## View the CASTable object parameters and 5 rows
display(castbl, castbl.head())

The CASTable object stores parameters on how to create the new CAS table and the table preview shows the updated column names.

Create a new CAS table

Once the castbl CASTable object is ready, I'll create a new CAS table named warranty_final in the Casuser caslib using the copyTable action (in Viya 3.5 use partition instead).

## Create a new CAS table for the final table
castbl.copyTable(casout = {'name':'warranty_final',
'caslib':'casuser',
'replace':True})

Since this will be the final table, I'll reorganize the columns to make it easier to work with. I'll reference the warranty_final CAS table in the variable finalTbl, then rearrange the columns and preview the table.

## Reference the new CAS table and reorganize columns
finalTbl = conn.CASTable('warranty_final', caslib = 'casuser')

## Specify the order of the columns
newColumnOrder = ['warranty_claim_id', 'car_id', 'make', 'product_model', 'model_year', 'platform', 'trim_level', 'engine_model','vehicle_assembly_plant',
'primary_labor_group', 'primary_labor_description', 'gross_claim_amount', 'gross_labor_amount', 'gross_material_amount', 'gross_other_amount',
'product_unit_id', 'repairing_state_province', 'repairing_region', 'repairing_country', 'repairing_dealer', 'latitude', 'longitude',
'claim_repair_start_date', 'claim_repair_end_date', 'claim_processed_date', 'claim_submitted_date','service_year_date']

finalTbl.alterTable(columnOrder = newColumnOrder)

## Preview the CAS table
finalTbl.head()

Save the CAS table as a file

Just like pandas DataFrames, CAS tables are in-memory. There are times you will want to save them back to disk. In CAS use the table.save CAS action to save the in-memory CAS table back to disk as a file (or back to a database). The save action is similar to saving a DataFrame using pandas to_ methods. For example, I'll save the warranty_final CAS table as a CSV file named warranty_final.csv in the Casuser caslib.

finalTbl.save(name = 'warranty_final.csv', caslib = 'casuser')

# and the results
NOTE: Cloud Analytic Services saved the file warranty_final.csv in caslib CASUSER(Peter).

The save action enables you to easily save the CAS table as other file formats. Here I'll save it as a PARQUET file.

finalTbl.save(name = 'warranty_final.parquet', caslib = 'casuser')

# and the results
NOTE: Cloud Analytic Services saved the file warranty_final.parquet in caslib CASUSER(Peter).

You can also save them as a SASHDAT file. A SASHDAT file is an the in-memory copy of a distributed CAS table on disk. They also store additional SAS information like column labels and formats.

finalTbl.save(name = 'warranty_final.sashdat', caslib = 'casuser')

# and the results
NOTE: Cloud Analytic Services saved the file warranty_final.sashdat in caslib CASUSER(Peter).

I'll run the fileInfo action to view available files in the Casuser caslib. I'll use the % wildcard character to find all files that begin with warranty_final. Then manipulate the CASResults object to access the SASDataFrame and select the Permission, Name and Time columns. For more information working with results from CAS actions check out Part 2 - Working with CAS Actions and CASResults Objects.

(conn.fileInfo(caslib = 'casuser', path='warranty_final%')['FileInfo']
.loc[:,['Permission','Name', 'Time']])

Summary

The SWAT package blends the world of pandas and CAS to process your distributed data in SAS Viya. In this example you learned how to save in-memory CAS tables back to disk using the save CAS action. The save action provides additional parameters that you can use to modify how you save your tables. Using the save CAS Action is similar to using a to_ method in pandas to save a DataFrame as a file.

Additional and related resources

Getting Started with Python Integration to SAS® Viya® - Index

SWAT API Reference

table.save CAS action

SAS® Cloud Analytic Services: Fundamentals

CAS Action Documentation

CAS Action! - a series on fundamentals

SAS Course - SAS® Viya® and Python Integration Fundamentals

Getting Started with Python Integration to SAS® Viya® - Part 17 - Saving CAS tables was published on SAS Users.