How to use calculated columns in CASTable

SAS Viya is an AI platform. It is available through languages such as Python, Java and R. A table object called CASTable is used in SAS Viya (CAS stands for Cloud Analytic Services). This time, I will explain how to use calculated columns, which dynamically add columns with CASTable.

Get a table from the database

First, connect to SAS Viya.

import swat
conn = swat.CAS('server-name.mycompany.com', 5570, 'username', 'password')

Then get the CASTable. This time, I will use CSV of IRIS data.

tbl = conn.loadtable('data/iris.csv', caslib='casuser').casTable

Add a column

For example, add a column called sepal_factor that adds sepal_length and sepal_width and doubles them.

tbl['sepal_factor'] = ((tbl.sepal_length + tbl.sepal_width) * 2)
tbl.head()

The contents are as follows. It's certainly calculated and new columns are added.

	sepal_length	sepal_width	petal_length	petal_width	species	sepal_factor
0	7.9	3.8	6.4	2.0	virginica	23.4
1	7.7	2.6	6.9	2.3	virginica	20.6
2	7.7	2.8	6.7	2.0	virginica	21.0
3	7.7	3.0	6.1	2.3	virginica	21.4
4	7.7	3.8	6.7	2.2	virginica	23.0

Add more columns.

tbl['total_factor'] = tbl.sepal_factor + tbl.petal_width + tbl.petal_length
tbl.head()

	sepal_length	sepal_width	petal_length	petal_width	species	sepal_factor	total_factor
0	7.9	3.8	6.4	2.0	virginica	23.4	31.8
1	7.7	2.6	6.9	2.3	virginica	20.6	29.8
2	7.7	2.8	6.7	2.0	virginica	21.0	29.7
3	7.7	3.0	6.1	2.3	virginica	21.4	29.8
4	7.7	3.8	6.7	2.2	virginica	23.0	31.9

You can also add a simple string column.

tbl['names'] = 'sepal / petal'
tbl.head()

	sepal_length	sepal_width	petal_length	petal_width	species	sepal_factor	total_factor	names
0	7.9	3.8	6.4	2.0	virginica	23.4	31.8	sepal / petal
1	7.7	2.6	6.9	2.3	virginica	20.6	29.8	sepal / petal
2	7.7	2.8	6.7	2.0	virginica	21.0	29.7	sepal / petal
3	7.7	3.0	6.1	2.3	virginica	21.4	29.8	sepal / petal
4	7.7	3.8	6.7	2.2	virginica	23.0	31.9	sepal / petal

Add more dynamic columns using the dynamically added columns.

tbl['cap_names'] = tbl.names.str.title()
tbl.head()

	sepal_length	sepal_width	petal_length	petal_width	species	sepal_factor	total_factor	names	cap_names
0	7.9	3.8	6.4	2.0	virginica	23.4	31.8	sepal / petal	Sepal / Petal
1	7.7	2.6	6.9	2.3	virginica	20.6	29.8	sepal / petal	Sepal / Petal
2	7.7	2.8	6.7	2.0	virginica	21.0	29.7	sepal / petal	Sepal / Petal
3	7.7	3.0	6.1	2.3	virginica	21.4	29.8	sepal / petal	Sepal / Petal
4	7.7	3.8	6.7	2.2	virginica	23.0	31.9	sepal / petal	Sepal / Petal

Summary

You can add the calculated columns in SQL as well, but in CASTable you can use the calculated columns to add more columns. Columns that have undergone complicated calculation processing can be easily realized, so please use them for your analysis.

SAS for Developers | SAS