Accessing and Managing Data
1. Import Packages and Connect to SAS Viya
import swat
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib
import sys
## Set options
pd.set_option('display.max_columns', None)
## Connect to SAS Viya
conn = swat.CAS(server_name, port, username, password)
print(conn)
This Python script initializes SAS Viya integration using SWAT. It imports Pandas, NumPy, and Matplotlib for data manipulation and visualization, sets Pandas to display all columns, and connects to SAS Viya, printing the connection details.
2. Explore Available Data in SAS Viya
## View available data sources in SAS Viya
ci = conn.caslibInfo()
## View available data source files
fi = conn.fileInfo(caslib = 'PIVY')
## View available in-memory tables
ti = conn.tableInfo(caslib = 'casuser')
-
View Available Data Sources in SAS Viya:
ci = conn.caslibInfo(): Retrieves information about available CAS libraries in SAS Viya, storing the details in the variable ci.
-
View Available Data Source Files:
fi = conn.fileInfo(caslib='PIVY'): Obtains information about the files in the CAS library named 'PIVY', storing the details in the variable fi.
-
View Available In-Memory Tables:
ti = conn.tableInfo(caslib='casuser'): Retrieves information about in-memory tables in the CAS library named 'casuser', storing the details in the variable ti.
3. Load Data into Memory in SAS Viya
-
Load a Server-Side File Into Memory
## Load a table into memory and display the output
lt = conn.loadTable(path = 'loans_raw.sashdat', caslib = 'PIVY',
casOut = {'caslib' : 'casuser',
'replace' : True})
## View available in-memory tables
ti = conn.tableInfo(caslib = 'casuser')
display(lt, ti)
-
Load a Client-Side File Into Memory
## Path to the client side file cars_client.csv. The CSV file resides in the PIVY -> Data
## folder on your Python client.
carsFile = (os
.path
.dirname(os.getcwd()) + r'/data/cars_client.csv')
## Set a list of column names for the CSV file
colNames = ['Make', 'Model', 'Type', 'Origin', 'DriveTrain', 'MSRP', 'Invoice',
'EngineSize', 'Cylinders', 'Horsepower', 'MPG_City', 'MPG_Highway',
'Weight', 'Wheelbase', 'Length']
conn.read_csv(carsFile,
header = None,
names = colNames,
casout = {'name' : 'clientupload',
'caslib' : 'casuser'}
)
-
Define the Path to the Client-Side CSV File:
carsFile = (os.path.dirname(os.getcwd()) + r'/data/cars_client.csv'): Constructs the file path to the CSV file named 'cars_client.csv' located in the 'PIVY -> Data' folder on the Python client. The path is stored in the variable carsFile.
-
Set a List of Column Names:
colNames = [...]: Defines a list of column names to be used for the CSV file. Each element in the list corresponds to a specific attribute of the cars dataset.
-
Read the CSV File into SAS Viya:
conn.read_csv(...): Uses the read_csv function to read the CSV file.header=None: Specifies that the CSV file does not have a header row.names=colNames: Provides the list of column names to be used.casout={'name': 'clientupload', 'caslib': 'casuser'}: Specifies the CAS table name ('clientupload') and the CAS library ('casuser') where the data will be stored in SAS Viya.
4. Make a Reference to a Table in SAS Viya
## Create a reference to a table and view the object
tbl = conn.CASTable('loans_raw', caslib = 'casuser')
tbl
5. Explore a Table
## View the dimensions of the table
shape = tbl.shape
## Preview the table
df_head = tbl.head()
## View column attributes
df_ci = tbl.columnInfo()
## Obtain summary statistics
colNames = ['Age', 'Salary', 'EmpLength', 'Amount', 'InterestRate']
df_summary = tbl.summary(input = colNames)
6. Analyze a Table
## Calculate the frequency of Category in SAS Viya
df = (tbl
.Category
.value_counts(normalize = True))
display(df)
## Plot the summarized results on the client using Pandas
df.plot(kind = 'bar',
figsize = (10,6),
title = 'Percent of Loans by Category');
df = (tbl
.query('Category = "Mortgage"')
.groupby('Year')
.Amount
.sum())
display(df)
df.plot(kind = 'line',
figsize = (10,6),
title = 'Total Amount of New Mortgage Loans by Year');
7- Preparing data
## Subset the data by adding the where parameter to the CASTable object
tbl.where = 'Category = "Car Loan" and LoanStatus ne "Fully Paid"'
## Create the calculated columns by adding the computedVarsProgram parameter to the CASTable object
calcAccOpenDate = 'AccOpenDate = mdy(Month, Day, Year);'
calcLoanLengthMonths = 'LoanLengthMonths = LoanLength * 12;'
tbl.computedVarsProgram = calcAccOpenDate + calcLoanLengthMonths
## Specify the columns to include by adding the vars parameter to the CASTable object
tbl.vars = ['ID', 'AccNumber', 'Amount', 'AccOpenDate', 'LoanLengthMonths','LoanStatus', 'Category']
## Preview the CAS table with the parameters
tbl.head()
-
Subset the Data Using the WHERE Parameter:
tbl.where = 'Category = "Car Loan" and LoanStatus ne "Fully Paid"': Modifies the CASTable object (tbl) to subset the data, including only rows where the 'Category' is "Car Loan" and the 'LoanStatus' is not "Fully Paid".
-
Create Calculated Columns Using ComputedVarsProgram:
calcAccOpenDate and calcLoanLengthMonths: Define two calculations to create new columns. calcAccOpenDate calculates the 'AccOpenDate' using the 'Month', 'Day', and 'Year' columns, and calcLoanLengthMonths computes 'LoanLengthMonths' as the product of 'LoanLength' and 12.tbl.computedVarsProgram = calcAccOpenDate + calcLoanLengthMonths: Modifies the CASTable object to include the specified calculated columns.
-
Specify Columns to Include Using Vars Parameter:
tbl.vars = ['ID', 'AccNumber', 'Amount', 'AccOpenDate', 'LoanLengthMonths', 'LoanStatus', 'Category']: Specifies the columns to include in the CASTable object, limiting the output to the specified columns.
-
Preview the CAS Table:
tbl.head(): Displays a preview of the modified CASTable, showing the specified columns and the calculated columns based on the defined conditions and computations.
8- Terminate session
conn.terminate()
terminate or close the connection to the SAS Viya server.
