Get the column list & data list of 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 try to get the column information of CASTable by various methods.

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

Get only the column name

It is OK to get only the column name with for in.

for col in tbl:
    print(col)

The output is as follows.

sepal_length
sepal_width
petal_length
petal_width
species

Get column name and index

If you want to get the index in addition to the column name, use the ʻenumerate` function.

for i, col in enumerate(tbl):
    print(i, col)

The output is as follows.

0 sepal_length
1 sepal_width
2 petal_length
3 petal_width
4 species

Get column name and column type

Use the zip function to get the type in addition to the column name.

for col, dtype in zip(tbl, tbl.dtypes):
    print(col, dtype)

The output is as follows.

sepal_length double
sepal_width double
petal_length double
petal_width double
species varchar

Get as CASColumn

Use the ʻiteritemsmethod to get it as aCASColumn` where you can get more detailed information.

for col, obj in tbl.iteritems():
    print(col, obj)
    print('')

The output is as follows.

sepal_length CASColumn('DATA.IRIS', caslib='CASUSER(username)')['sepal_length'].sort_values(['sepal_length', 'sepal_width'], ascending=[False, True])

sepal_width CASColumn('DATA.IRIS', caslib='CASUSER(username)')['sepal_width'].sort_values(['sepal_length', 'sepal_width'], ascending=[False, True])

petal_length CASColumn('DATA.IRIS', caslib='CASUSER(username)')['petal_length'].sort_values(['sepal_length', 'sepal_width'], ascending=[False, True])

petal_width CASColumn('DATA.IRIS', caslib='CASUSER(username)')['petal_width'].sort_values(['sepal_length', 'sepal_width'], ascending=[False, True])

species CASColumn('DATA.IRIS', caslib='CASUSER(username)')['species'].sort_values(['sepal_length', 'sepal_width'], ascending=[False, True])

Get the data in detail

Next is how to get the data row by row. The first is when using the ʻiterrows` method.

for row in tbl.iterrows():
    print(row)

The output is as follows. There are 150 lines in total.

(0, sepal_length          7.9
sepal_width           3.8
petal_length          6.4
petal_width             2
species         virginica
Name: 0, dtype: object)
(1, sepal_length          7.7
sepal_width           2.6
petal_length          6.9
petal_width           2.3
species         virginica
  :
Name: 148, dtype: object)
(149, sepal_length       4.3
sepal_width          3
petal_length       1.1
petal_width        0.1
species         setosa
Name: 149, dtype: object)

Next is the case of using the ʻitertuples` method.

for row in tbl.itertuples():
    print(row)

The result is as follows, only the value is returned.

(0, 7.9000000000000004, 3.7999999999999998, 6.4000000000000004, 2.0, 'virginica')
(1, 7.7000000000000002, 2.6000000000000001, 6.9000000000000004, 2.2999999999999998, 'virginica')
  :
(148, 4.4000000000000004, 3.2000000000000002, 1.3, 0.20000000000000001, 'setosa')
(149, 4.2999999999999998, 3.0, 1.1000000000000001, 0.10000000000000001, 'setosa')

Summary

There are various ways to get column information. Please use properly according to your needs.

SAS for Developers | SAS

Recommended Posts

Get the column list & data list of CASTable
[python] Get the list of classes defined in the module
Get the number of digits
[Python] Get the list of ExifTags names of Pillow library
Try to get the function list of Python> os package
Get the number of specific elements in a python list
Get the number of occurrences for each element in the list
Get the number of views of Qiita
Get the complete bitflyer tick data
Get the attributes of an object
Get the first element of queryset
Check the data summary in CASTable
Get the number of Youtube subscribers
About the basics list of Python basics
I tried to get the index of the list using the enumerate function
[python] Get the rank of the values in List in ascending / descending order
The story of verifying the open data of COVID-19
Algorithm Gymnastics 24 Middle of the Linked List
Arbitrarily rearrange the column order of Pandas.DataFrame
Get the minutes of the Diet via API
[python] Get a list of instance variables
Get the value of the middle layer of NN
Get the last day of the specified month
[Python] Get the character code of the file
Get the filename of a directory (glob)
Get the EDINET code list in Python
[Python] Get a list of folders only
[PowerShell] Get the reading of the character string
Visualize the export data of Piyo log
[Linux] Command to get a list of commands executed in the past
I measured 6 methods to get the index of the maximum value (minimum value) of the list
Try to get the road surface condition using big data of road surface management
Get the list of packages for the specified user from the packages registered on PyPI
Get the key for the second layer migration of JSON data in python
Export CASTable data
Get the contents of git diff from python
[python] Check the elements of the list all, any
[Python] Sort the list of pathlib.Path in natural sort
[Python] Get / edit the scale label of the figure
[Python] Get the main topics of Yahoo News
Get the caller of a function in Python
Make a copy of the list in Python
I checked the list of shortcut keys of Jupyter
Get only the subclass elements in a list
[Python] Get the last updated date of the website
Get a list of IAM users with Boto3
I want to get League of Legends data ③
I want to get League of Legends data ②
Get a list of Qiita likes by scraping
The story of reading HSPICE data in Python
Python script to get a list of input examples for the AtCoder contest
Get only the address part of NIC (eth0)
Search by the value of the instance in the list
To get the path of the currently running python.exe
List the AMIs used by AWS Data Pipeline
I want to get League of Legends data ①
[Python] Get the day of the week (English & Japanese)
The transition of baseball as seen from the data
Check the status of your data using pandas_profiling
Download the wind data of the Japan Meteorological Agency
Scraping the winning data of Numbers using Docker