SAS Viya

1月 242023
 

The word innovation often draws to mind images of self-driving cars, new phones, and shiny tech. Yet, innovation often happens behind the scenes, especially in advanced analytics. Around the world, industries like healthcare, government, banking, manufacturing, and more rely on the latest advancements in analytics. At SAS Explore, an event for [...]

4 ways you might not realize advanced analytics is changing the world was published on SAS Voices by Lexi Regalado

1月 172023
 

Welcome to the continuation of my series Getting Started with Python Integration to SAS Viya. In previous posts, I discussed how to connect to the CAS server, working with CAS actions and CASResults objects, and how to summarize columns. Now it's time to focus on how to get the count of unique values in a CAS table column.

Load and prepare data

First, I connected my Python client to the distributed CAS server and named my connection conn. Then I created a function to load and prepare my CAS table. The custom function loads the WARRANTY_CLAIMS_0117.sashdat file from the Samples caslib into memory, renames the columns using the column labels and drops unnecessary columns. This simplifies the table for the demonstration.

The Samples caslib should be available in your SAS Viya environment and contains sample tables. For more information on how to rename columns in a CAS table view Part 11 - Rename Columns.

## Packages
import swat
import pandas as pd
 
## Options
pd.set_option('display.max_columns', 50)
 
## Connect to CAS
conn = ## your connection information
 
def prep_data():
    ## Load the data into CAS
    conn.loadTable(path='WARRANTY_CLAIMS_0117.sashdat', caslib='samples',
                   casout={'name':'warranty_claims', 'caslib':'casuser'})
 
    ## Reference the CAS table in an object
    castbl = conn.CASTable('warranty_claims', caslib = 'casuser')
 
    ## Store the 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():
        colName = row[1].values[0]
        labelName = row[1].values[1].replace(' ','_')
        renameColumns.append(dict(name=colName, rename=labelName))
 
    ## List of columns to keep in the CAS table
    keepColumns = {'Campaign_Type', 'Platform','Trim_Level','Make','Model_Year','Engine_Model',
                   'Vehicle_Assembly_Plant','Claim_Repair_Start_Date', 'Claim_Repair_End_Date'}
 
    ## Rename and drop columns to make the table easier to use
    castbl.alterTable(columns = renameColumns, keep = keepColumns)
 
    return castbl

Next, I'll execute the user defined function and store the CAS table object in the variable tbl  and view it's type.

tbl = prep_data()
type(tbl)
 
# and the results
NOTE: Cloud Analytic Services made the file WARRANTY_CLAIMS_0117.sashdat available as table WARRANTY_CLAIMS in caslib CASUSER(Peter).
swat.cas.table.CASTable

The results show that the WARRANTY_CLAIMS_0117.sashdat is available in the CAS server, and tbl is a CASTable object.

Lastly, I'll preview the distributed CAS table using the SWAT package head method.

tbl.head()

The results show a preview of the WARRANTY_CLAIMS CAS table. The table provides data on warranty claims for car repairs. The data in this example is small for training purposes. Processing data in the CAS server's massively parallel processing environment is typically reserved for larger data.

Using the Pandas API in the SWAT package - value_counts method

I'll begin by using the Pandas API in the SWAT package which provides the value_counts method. The value_counts method works like it's Pandas counterpart. For example, I'll obtain the count of unique values in the Engine_Model CAS table column. I'll store the results of in vc, then display the type and value of vc.

vc = (tbl               ## CAS table
      .Engine_Model     ## CAS table column
      .value_counts()   ## SWAT value_counts method
     )
 
## Display the type and value
display(type(vc),vc)

The SWAT value_counts method summarizes the data in the distributed CAS server and returns a Pandas Series to the Python client. Once you have the Pandas Series on the client, you can work with it as you normally would. For example, I'll plot the Series using the Pandas plot method.

vc.plot(kind = 'bar', figsize=(8,6));

In this example, I used the Pandas API in the SWAT package to summarize data on the CAS server's massively parallel processing environment to return smaller, summarized results to the Python client. Once the summarized results are on the client, I'll work with them using other Python packages like Pandas.

Using the freq CAS action

Instead of using the Pandas API in the SWAT package you can achieve similar results using native CAS actions. In SWAT, CAS actions are simply specified as a method. One action that provides the count of unique values is the simple.freq CAS action.

For example, I can find the count of unique values for multiple columns within the freq action. Here, I'll specify the Engine_Model, Model_Year and Campaign_Type columns in the the inputs parameter. Then, I'll call the Frequency key after the action to obtain the SASDataFrame stored in the dictionary returned to the Python client. Remember, CAS actions always return a dictionary, or CASResults object, to the Python client. You must use familiar dictionary manipulation techniques to work with the results of an action. For more information on working with results of CAS actions, check out Part 2 - Working with CAS Actions and CASResults Objects.

## Columns to analyze
colNames = ['Engine_Model', 'Model_Year', 'Campaign_Type']
 
## Execute the freq CAS action and store the SASDataFrame
freq_df = tbl.freq(inputs = colNames)['Frequency']
 
## Display the type and DataFrame
display(type(freq_df), freq_df)

Again, the action processes the data in the distributed CAS server and returns results to the Python client. The results show the freq action counts the unique values of each column and stores the results in a single SASDataFrame. Once you have the SASDataFrame on the client, you can work with it like you would a Pandas DataFrame.

For example, I'll loop over each analysis column, query the SASDataFrame for the specific column name, and then plot the count of unique values of each column using the familiar Pandas package.

for column in colNames:
    (freq_df
     .query('Column == @column')
     .loc[:,['CharVar','Frequency']]
     .set_index('CharVar')
     .sort_values(by = 'Frequency', ascending=False)
     .plot(kind='bar', figsize=(8,6), title = f'The {column} Column')
    )

The loop produces a visualization of the count of unique values for each analysis column. This was all done using familiar Pandas code on the client side. Remember, the distributed CAS server did all of the processing and summarization, then returned smaller summarized results to the Python client.

Using the freqTab CAS action

Lastly, you can use the freqTab.freqTab CAS action to construct frequency and crosstabulation tables. The freqTab action provides a variety of additional features and information. The action is not loaded by default, so I'll begin by loading the action set.

conn.loadActionSet('freqTab')

Then I'll use the freqTab action in the freqTab action set to count the unique values for the Model_Year and Engine_Model columns, and also count the unique values of Engine_Model by Model_Year.

tbl.freqTab(tabulate = [
                'Model_Year',
                'Engine_Model',
                {'vars':['Engine_Model','Model_Year']}
            ]
    )

The results above show the freqTab action returns a dictionary with a variety of information. The first SASDataFrame is level information, the second SASDataFrame shows the number of observations used, and the remaining SASDataFrames show the two one-way frequency tables for Model_Year, and Engine_Model, and the crosstabulation between Engine_Model by Model_Year (also includes the totals).

With the results on the Python client, you can begin accessing and manipulating the SASDataFrames as needed.

Summary

The SWAT package blends the world of Pandas and CAS. You can use many of the familiar Pandas methods within the SWAT package like value_counts, or the flexible, highly optimized CAS actions like simple.freq and freqTab.freqTab to obtain counts of unique values in the massively parallel processing CAS engine. For more examples on the freq or freqTab CAS actions, check out my CAS action four part series (part 1, part 2, part 3 and part 4). The four part series executes CAS actions using the native CAS language. However, with some small changes to the syntax you can execute the same actions using Python.

Additional and related resources

Getting started with Python integration to SAS® Viya® - Part 12 - Count of Unique Values was published on SAS Users.

1月 042023
 

Thank you to my co-author Raymond Thomas for his assistance on this post.

Learn more about the SAS Certified Specialist: Administration of SAS Viya and how to prepare.

Certification overview

SAS is pleased to announce a new SAS Viya Administration certification. Recognizing the growing need of cloud computing and Kubernetes, this new credential will help create a standard of knowledge within these areas.

Because of the growing skills required in cloud computing and Kubernetes, it’s critical that users can validate the skills they’ve learned and acquired to represent their knowledge. SAS developed the exam to cover the following major content areas:

    • Manage a SAS Viya environment.
    • Monitor, log and troubleshoot a SAS Viya environment.
    • Manage identities and users.
    • Manage content and functionality.
    • Manage data in SAS Cloud Analytic Services (CAS).

View more details about the certification: SAS Viya Administration Specialist | SAS.

To see a full list of topics covered, select the “Exam Content Guide.”

Why it's so important

This certification validates SAS Viya administration skills you have with employers. Proving your skillset through a certification shows your knowledge of managing and troubleshooting in SAS Viya, which greatly increases the opportunity for advancement in your career as a SAS administrator. Also, this certification is recognized worldwide by many leading industries, and obtaining this certification shows employers you are proactive and committed to your career.

Related job roles & industries

SAS Viya Administration applies to job roles across many industries such as Pharmaceutical, Banking, and Government. A few examples of job roles that may benefit from using the SAS Viya Administration include System Architects, Cloud System Administrators, Systems Analyst, System Administrator, System Engineer, and Linux or Windows Administrators.

The certification is intended for anyone with direct responsibility in administering SAS Viya or anyone administering a SAS 9 environment migrating to SAS Viya. SAS Viya administrators support users across many industries and this skill is highly prevalent.

How to prepare

While there are no prerequisites to take this exam, students often use a combination of training and experience to study for this exam. To prepare for the exam, these resources are available:

Courses

There are two free e-learning courses that are no prerequisites that may also be beneficial during your exam preparation:

Practice exam

Sample questions

Registration details

Follow this link to create an account or access your existing account to register: Certification Manager.

For detailed exam topics, hit the "Exam Content Guide" links on each credential's web page. The content guides contain exam sections, objectives, and expanded detail about the important topics.

The Specialist exam is A00-451. There are no pre-requisites for this exam, so anyone who is interested in this credential can register.

Summary

You can learn more about these new credentials at the links above. There you will find detailed exam content guides, free sample questions, and free practice exams (yes - free full length practice exams!).

We hope you tackle the recommended training, develop your skills, and register soon to be the first candidates to earn these credentials. Good luck with your preparations!

If you have additional questions, please ask in the comments below.

New certification alert: SAS® Certified Specialist: Administration of SAS® Viya® was published on SAS Users.

12月 232022
 

Data scientists are in short supply. McKinsey found the U.S. labor market alone is short 190,000 data scientists, and demand is growing. According to the World Data Science Initiative, "the data science market size is expected to grow from USD 95.3.9 billion in 2021 to USD 322.9 billion by 2026.” To help equip more data scientists to be ready for the world ahead, SAS created SAS Explore.  

Created by technologists for technologists, SAS Explore has more than 100 free SAS training sessions. Data scientists, programmers, and analytics enthusiasts tuned in globally for our inaugural event.  

Thanks to the high interest, these SAS training courses available to the public at no cost. Now, data scientists (and aspiring data scientists) can learn for free on demand. 

With so many sessions to choose from, it’s hard to choose only a few to highlight. In this article, you’ll find the top five technical sessions, according to audience rankings. Keep reading to get a peek at what you can learn with SAS Explore!

#1: Creating Reports Again and Again... and Again with Macro 

You know you can create a flexible report with SAS using macro variables, but what about when it comes to multiple reports? What about sending the report to Microsoft Excel? This presentation focuses on using %if/%then logic and %do loops to enable you to create multiple excel reports. The macro definitions that create the reports are flexible, customizable, and, best of all, able to run repeatedly without needing to manually change anything! 

In this session, you’ll learn how to create reports with Macro, how to define the ‘underperform’ value, exporting to excel, adding parameters, excel ODS options, and more.  

What attendees had to say about this session: 

  • “Everything was helpful. Thank you so much!” 
  • “Walking through all the code is something I value highly. There are many resources available online for SAS, but nothing beats someone explaining all the different code options.” 
  • “In this session, I learned about non-positional in macros. Didn't know that you could set defaults like that. I will be using that in my coding. Also, more in depth usage of %if %then %else macros that weren't in the Coursera SAS training.” 
  • “This is easily actionable content that will make a real difference to my coding efficiency.” 

Watch the full training session for free now on YouTube: 

#2: Using Python for Data Analytics in SAS® Viya® 

Unite the Python language with the analytic power of SAS. In this presentation, you’ll learn how to use the SWAT (SAS wrapper for analytics transfer) package to take advantage of the SAS Cloud Analytic Services (CAS) engine in SAS Viya for massively parallel processing (MPP).  

This training begins with a quick overview of SAS Viya and the CAS engine and then discusses how to leverage the strengths of the CAS engine and your local Python client, how to connect Python to CAS, and how to access and load data into CAS's MPP environment. 

Then, you’ll dive into exploring, preparing, and analyzing data using the CAS server, taking advantage of the CAS server's distributed processing power using familiar pandas API and CAS actions from the SWAT package. Lastly, you’ll learn how to unite traditional python packages such as Pandas and matplotlib with the summarized results from the CAS server for additional processing and visualization. 

What attendees had to say about this session: 

  • “Lots of valuable information on using python in SAS!” 
  • “The jupyter notebook format really made it easy to follow the presentation.” 
  • “Overall, this session was outstanding.” 

Watch the full training session for free now on YouTube: 

#3: Software Workshop: End-to-End Machine Learning in Model Studio 

Effective predictive modeling projects follow the SAS analytics life cycle, from data to discovery and then deployment. SAS Viya provides the capability to perform all these steps from one graphical user interface, making the transition between stages of the analytics life cycle seamless. 

In this training, you'll load data into memory, prepare input variables for modeling, and build complex analytics pipelines to demonstrate powerful machine learning models. Need to better understand your model? No problem. We’ll show you how to produce model interpretability plots. Need to integrate open-source models? No problem. We’ll show you how to do that and deploy any model with one click. Then the best-performing model is saved and packaged for deployment, and you can still retrain this model on new data – all from the same shared interface. 

What attendees had to say about this session: 

  • “The best session of all. Well explained!” 
  • “Having a virtual lab where I can follow the training step-by-step gave me a fantastic experience. The instructor is amazing. Thank you all!” 
  • “I learned about options that I did not know existed.” 
  • “Being walked through the entire process from end to end was amazing!” 

Watch the full training session for free now on YouTube: 

#4: Speaking to Microsoft Excel with PROC SQL in SAS® Viya® 

Microsoft Excel, SQL and SAS are all equally loved for their unique strengths. Excel has been a go-to for a long time. Database workers live in SQL. SAS Viya is a cloud-enabled, in-memory analytics engine that provides quick, accurate, and reliable analytical insights. What would it be like to stay in the comfort of SAS while beckoning Excel in to join data using PROC SQL?  

If you have wondered how this is possible, this is the session for you. Watch this incredible trio in action. You will learn to bring in data from Excel to SAS Viya using SAS engines, join multiple Excel spreadsheets, summarize data within SAS Viya using PROC SQL, and send the results to Excel. And you do all these things within SAS. Charu will show you a great way to build a bridge between SAS coders and colleagues who never code. All levels welcome! 

What attendees are saying: 

  • “The whole session is useful!” 
  • “Charu, thank you! Your teaching skills are excellent. The presentation was exceptionally clear. You add detail just where it is needed. Very easily understood. The steps in code were very logical. I think this presentation is the best I viewed in SAS Explore!” 
  • “I loved the combination of the presenter's communication skills and the step-by-step examples! Great job!!!!!!!!!!!!!!!!!!!!!!!!” 

Watch the full training session for free now on YouTube: 

#5: Struggling with Syntax? SAS® Studio to the Rescue! 

One of the more frustrating elements of learning to code in SAS is mastering the syntax. In this hands-on SAS training, you’ll learn how to use SAS Studio tasks in SAS OnDemand for Academics to automatically generate code for a range of statistical analyses such as t-tests, analysis of variance, linear regression, and logistic regression. For even more flexibility in our modeling, you’ll also learn also the autocomplete and dynamic syntax tooltips in the programming editor of SAS Studio – which aids in the construction of more dynamic statistical models. 

Both sets of SAS Studio tools will help you obtain results from statistical analyses faster – so that you can focus more on the results… rather than the syntax errors. For our academic partners, this means more time covering course content – which makes you, and your students, more productive. 

What attendees are saying: 

  • “This was a great session. Simple data but the analysis was laid out in a logical way and then shown how easy it was to code using pre-built tasks. The instructor was also very knowledgeable.” 
  • “Concise and interesting demonstration. I liked that she used the same data set in all analyses.” 
  • “I have never used the left menu before. Helpful to know a different way to do things.” 

Watch the full training session for free now on YouTube: 

Keep exploring free SAS training 

If you loved these free SAS trainings, you’ll love the rest too. Explore 50+ free virtual sessions on our YouTube channel now. 

You’ll find topics like: SAS Viya in Microsoft Azure, Connected Supply Chain Planning, Building Analytical Applications, Demystifying SAS Certification, Automated Large-Scale Forecasting, Preventing Bias in AI, and so much more.   

All the sessions are full of tips and tricks to help you do more with SAS. Now that you’ve gotten a peek into the goodness that awaits, it’s time to dive into SAS Explore 2022 on demand! 

SAS training: 5 free sessions technologists love was published on SAS Users.

12月 222022
 

The addition of the PYTHON procedure and Python editor in SAS Viya enables users to execute Python code in SAS Studio. This new capability in SAS Viya adds another tool to SAS's existing collection. With this addition I thought, how can I utilize this new found power?

In this example, I'll keep it simple. I want to create a Microsoft Excel report using a combination of SAS, Python and SQL. I'll use data that's stored in a SAS library; however, the library could be using data stored anywhere, like a path, database or in the cloud. I'll write a program that executes the following:

All code used in this post is located on GitHub, here.

Set folder path and file name

To begin, I'll create a macro variable to specify the output folder path and Microsoft Excel workbook name.

%let path=/*Enter your output folder path*/;
%let xlFileName = myExcelReport.xlsx;

Prepare data

Further, I'll prepare the data using the SAS DATA step. I'll use the available sashelp.cars table, create a new column named MPG_Avg, and drop unnecessary columns. Instead of using the DATA step you can use Python or SQL to prepare the data. Whatever tool works best for you.

data work.cars;
    set sashelp.cars;
    MPG_Avg=mean(MPG_City, MPG_Highway);
    drop Wheelbase Weight Length;
run;

Create the Microsoft Excel workbook

After the data is ready, I'll use the ODS EXCEL statement to create the Excel spreadsheet. The following ODS options are used:

  • FILE - specifies the file path and name.
  • STYLE - modifies the appearance of the SAS output
  • EMBEDDED_TITLES - specifies titles should appear in the worksheet
  • SHEET_INTERVAL - enables manual control when to create a new worksheet
ods excel file="&path./&xlFileName" 
		  style=ExcelMidnight   
		  options(embedded_titles="on");

Worksheet 1

Print the data using SAS

With the ODS EXCEL destination open I'll name the first worksheet Data, and manually specify when a new sheet is created. Next, I'll use the PRINT procedure to print the detailed data to Excel. The PRINT procedure will print the entire SAS data set with the associated formats and styles to Excel.

* Sheet 1 - Print the data using SAS *;
ods excel options(sheet_name='Data' sheet_interval='none');
title height=16pt color=white "Detailed Car Data";
proc print data=work.cars noobs;
run;

Worksheet 2

Create violin plots using Python

Next, I want to create violin plots on a new worksheet named Origin_MPG. Now, these can be created in SAS, but I personally found the matplotlib package in Python a bit easier to use . With the PYTHON procedure, I can include the Python code within the SAS program (or you can reference a .py file) to create the visualization. Then I'll use the SAS.pyplot method to save and render the visualization. Since the pyplot callback renders the image in the results tab, it exports the image to the Excel workbook by default.

First I'll use ODS EXCEL to create the new worksheet and the TITLE statement to add a title to the Excel worksheet.

ods excel options(sheet_name='Origin_MPG' sheet_interval='now');
title justify=left height=16pt color=white "Analyzing MPG by Each Car Origin";

Then I'll execute the PYTHON procedure to execute my Python code.

* Create violin plots using Python *;
proc python;
submit;
 
##
## Import packages and options
##
 
import pandas as pd
import matplotlib.pyplot as plt
plt.style.use('fivethirtyeight')
outpath = SAS.symget('path')
 
##
## Data prep for the visualization
##
 
## Load the SAS table as a DataFrame
df = (SAS
      .sd2df('work.cars')                 ## SAS callback method to load the SAS data set as a DataFrame
      .loc[:,['Origin','MPG_Avg']]        ## Keep the necessary columns
)
 
 
## Create a series of MPG_Avg for each distinct origin for the violin plots
listOfUniqueOrigins = df.Origin.unique().tolist()
 
mpg_by_origin = {}
for origin in listOfUniqueOrigins:
    mpg_by_origin[origin] = df.query(f'Origin == @origin ').MPG_Avg
 
 
##
## Create the violin plots
##
 
## Violin plot
fig, ax = plt.subplots(figsize = (8,6))
ax.violinplot(mpg_by_origin.values(), showmedians=True)
 
## Plot appearance
ax.set_title('Miles per Gallon (MPG) by Origin')
rename_x_axis = {'position': [1,2,3], 'labels':listOfUniqueOrigins}
ax.set_xticks(rename_x_axis['position'])
ax.set_xticklabels(rename_x_axis['labels']);
 
## Save and render the image file
SAS.pyplot(plt, filename='violinPlot',filepath=outpath)
 
endsubmit;
quit;
title;

SQL Aggregation

SQL is an extremely common and useful language for data analysts and scientists. I find using SQL for aggregation easy, so I will create a simple aggregation and add it below the visualization on the same worksheet in the the Excel report.

* SQL Aggregation *;
title justify=left "Average MPG by Car Makes";
proc sql;
select Origin, round(mean(MPG_Avg)) as AverageMPG
	from work.cars
	group by Origin
	order by AverageMPG desc;
quit;
title;

Add text

At the end of the same worksheet I'll add some simple text using the ODSTEXT procedure to give some information about the data.

proc odstext;
   heading 'NOTES';
   p 'Using the SASHELP.CARS data. The following car Origins were analyzed:';
   list ;
      item 'Asia';
      item 'Europe';
      item 'USA';
   end;    
   p 'Created by Peter S';
quit;

Close the Excel workbook

Lastly, I'll close the ODS EXCEL destination since I am done writing out to Excel.

ods excel close;

Results

That's it! Now I'll execute the entire program and view the Excel workbook.

Summary

With the capabilities of SAS and the new ability to execute Python code in SAS Studio, teams have a variety of tools in SAS Viya for their analytic needs.

Additional resources

PYTHON Procedure documentation
SAS opens its code editor interface to Python users
Using PROC PYTHON to augment your SAS programs
ODS Excel Statement

Creating a Microsoft Excel report using SAS, Python and SQL! was published on SAS Users.

12月 202022
 

Change is hard and can be a bit scary. We tend to resist change as we get comfortable with our routines and how things work. However, many times we fail to see the benefits of that something new. Imagine if we didn't move from MS DOS to Windows? If we'd chose beta over VHS? If we still drove station wagons instead of SUVs? I could go on, but I think you get the point. Change is many times beneficial and necessary, even if it's different.

So, this brings me to the topic of migrating from SAS 9 to SAS Viya. Much has been written on the benefits of SAS Viya, so I'll not cover that here. What I do plan to pass along is that SAS has created many helpful tools to ease the migration. I'll cover two of these tools in detail: the Enterprise Session Monitor and SAS 9 Content Assessment.

Enterprise Session Monitor (ESM) and SAS 9 Content Assessment (S9CA) are part of the Ecosystem Diagnostics Family providing glass view into your SAS Environment. 

Key features of ESM include maximizing resource utilization, substantially increasing your effective operating capacity through advanced batch schedule optimization, performance tuning, and enabling cooperative resource sharing. Moreover, S9CA is a collection of applications designed to help you understand your SAS 9.4 deployment and content, gather and prepare SAS 9.4 content for migration, and import SAS 9.4 content.

ESM and S9CA work together offering a complete picture of your SAS Environment, but can also work independently. ESM provides capacity planning information which helps in the sizing of SAS environments. The SAS 9 Content Assessment provides information on the types of artifacts in your SAS Environment.  Together, ESM and S9CA provide information on how to successfully migrate from SAS 9.4 to SAS Viya.

Let's explore some of the features of each tool.

Reports

ESM

ESM provides the following information:

  • Number of Distinct SAS Users
  • SAS Users consuming resources
  • Capacity Report – CPU. Memory, I/O and sessions
  • Sizing Reports
  • Reports for proactive migration of jobs and users to SAS Viya
  • SAS Jobs being executed
  • Real-time workload identification
  • Self-service administration

S9CA

S9CA provides the following information:

  • Counts and collects data about what exists on the SAS 9 system
  • Granular details about selected artifacts
  • Validates code will migrate to SAS Viya
  • Finds internationalization issues in SAS programs
  • Prepares results for reporting
  • Summaries SAS 9 logs steps to analyze SAS Code for optimization
  • Counts the number of users for SAS application on SAS Workspace Servers
  • Analyzes SAS 9.4 system and configuration details to identify known deployment issues.

How it works

ESM

A prerequisite for ESM installation and configuration is an ESM server and agent. The agents connect to the server. There are various tools like proc-mon to collect the data. The ESM server receives events from the ESM agents at a rate of every two seconds. The impact to the servers with ESM agents is less than 20 percent of one CPU. The ESM agent's data is stored in a Postgres database for historical reporting. The ESM Maintenance Utility is installed and used to extract data from the agents.

S9CA

There are many applications that make up the SAS Content Assessment Tool. For example, the inventory application counts SAS artifacts defined to the SAS Metadata Server, SAS Mid-tier and the file system. The profile application provides more granular details for the SAS artifacts i.e., Enterprise Guide Projects, Enterprise Miner, Stored Processes etc. After each application runs via the command line, the published application creates an aggregated data mart for reporting.  After each application runs, the published application is used to create an aggregated data mart for reporting.  By default, Personal Identifiable Information (PII) data is obfuscated when the publish application runs, so this information is protected.

Timeline

ESM

Customer and SAS Activities Time Commitment Notes
Install/Config 4 hours/per environment If SAS resource is involved.
ESM Extraction After 7 days/1 hour After 90 days/1 hour

 

S9CA

Customer and SAS Activities Time Commitment Notes
Install/Config 1 hour/per environment Ensure to get the latest version of the software
Execute each application Time depends on Data being scanned  

 

Data Collection

ESM

ESM collects data on these SAS platforms

  • SAS x
  • SAS Viya 3
  • SAS Viya 4

S9CA

SAS artifacts scanned by S9CA

  • SAS code
  • SAS Stored Process
  • SAS Enterprise Guide
  • SAS Enterprise Miner
  • SAS Catalogs
  • SAS Object Spawner
  • SAS Workspace Server
  • SAS Metadata Server
  • Number of SAS data sets

As mentioned earlier, they can work independently, but they are better together. Together, ESM and S9CA assist with knowing the state of your SAS Environment and make the migration process to SAS Viya from SAS 9.4 straightforward.

ESM and S9CA: Useful Tools for Migration from SAS 9 to SAS Viya was published on SAS Users.

12月 202022
 

Welcome back to my SAS Users blog series CAS Action! - a series on fundamentals. The previous posts show how to use the simple.freq CAS action to generate, save and group simple frequency tables. In this post I will show you how to use the freqTab.freqTab CAS action to generate more advanced one-way frequency and crosstabulation tables.

In this example, I will use the CAS language (CASL) to execute the freqTab CAS action. 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. By default the Samples caslib should be available in your SAS Viya environment. I'll load the table to the Casuser caslib and then I'll clean up the CAS table by renaming and dropping columns to make the table easier to use. For more information how to rename columns check out my previous post. Lastly I'll execute the fetch action to preview 5 rows.

proc cas;
   * Specify the input/output CAS table *;
   casTbl = {name = "WARRANTY_CLAIMS", caslib = "casuser"};
 
   * Load the CAS table into memory *;
    table.loadtable / 
        path = "WARRANTY_CLAIMS_0117.sashdat", caslib = "samples",
        casOut = casTbl + {replace=TRUE};
 
* Rename columns with the labels. Spaces replaced with underscores *;
 
   *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 *;
   listElementCounter = 0;
   do columnMetadata over cr.ColumnInfo;
	listElementCounter = listElementCounter + 1;
	convertColLabel = tranwrd(columnMetadata['Label'],' ','_');
	renameColumns[listElementCounter] = {name = columnMetadata['Column'], rename = convertColLabel, label=""};
   end;
 
   * Rename columns *;
   keepColumns = {'Campaign_Type', 'Platform','Trim_Level','Make','Model_Year','Engine_Model',
                  'Vehicle_Assembly_Plant','Claim_Repair_Start_Date', 'Claim_Repair_End_Date'};
   table.alterTable / 
	name = casTbl['Name'], caslib = casTbl['caslib'], 
	columns=renameColumns,
	keep = keepColumns;
 
   * Preview CAS table *;
   table.fetch / table = casTbl, to = 5;
quit;

The results above show a preview of the warranty_claims CAS table.

One-way frequency tables

To create more advanced one-way frequency tables, use the freqTab.freqTab CAS action. In the freqTab action, use the table parameter to specify the CAS table and the tabulate parameter to specify the column, or columns, to analyze. The tabulate parameter is extremely flexible and provides a variety of ways to analyze your data. In this example, I'll specify the warranty_claims CAS table and the Campaign_Type and Make columns as a list in the tabulate parameter.

proc cas;
   * CAS table reference *;
   casTbl = {name = "WARRANTY_CLAIMS", caslib = "casuser"};
 
   * One-way frequency tables *;
   freqTab.freqTab / 
       table = casTbl,
       tabulate = {'Campaign_Type','Make'};
quit;

Results

The results above show the freqTab action generates a variety additional information compared to the freq action. Information including the number of observations used, variable level and timing. The freqTab action frequency tables contain the expected total frequency of each value; moreover, it also includes the total percentage, cumulative frequency and cumulative percent.

Two-way crosstabulation tables

Instead of producing one-way frequency tables, you can also create two-way crosstabulation tables. One way is to continue to add elements in the list in the tabulate parameter. Here a one-way frequency table will be created for Campaign_Type and Make as seen before. Then, I'll add a dictionary in the list. The vars key specifies the Make column, and the cross key specifies the Campaign_Type and Model_Year columns. The columns in the cross key are paired with those specified in vars. This example will produce a two-way crosstabulation between Make by Campaign_Type and Make by Model_Year.

proc cas;
   * CAS table reference *;
   casTbl = {name = "WARRANTY_CLAIMS", caslib = "casuser"};
 
   * One-way frequency and two-way crosstabulation tables *;
   freqTab.freqTab / 
      table = casTbl,
      tabulate = {
 		'Campaign_Type',
		'Make',
              	{vars = 'Make', cross = {'Campaign_Type', 'Model_Year'}}
      };
quit;

Partial results

The results above show the freqTab action returns the one-way frequency tables for Campaign_Type and Make as shown earlier and displays the two-way crosstabulation between Make by Campaign_Type and Make by Model_Year.

While this is great, what if I want to avoid the total row and display the Make for each row in the two-way crosstabulation as a single table?

Two-way crosstabulation as a single table

To present the results as a single table and remove the total row, add the tabDisplay parameter with the value list. In the code, I'll remove the one-way frequencies and add the tabDisplay parameter.

proc cas;
   * CAS table reference *;
   casTbl = {name = "WARRANTY_CLAIMS", caslib = "casuser"};
 
   * Two-way crosstabulation as a single table *;
   freqTab.freqTab / 
       table = casTbl,
       tabulate = {
              	{vars = 'Make', cross = {'Campaign_Type', 'Model_Year'}}
       }, 
       tabDisplay='list';
quit;

Partial results

The results show each two-way crosstabulation as a single table and the total row is removed.

Next, what if you want to create a three-way crosstabulation table? Well this is a bit tricky.

Three-way crosstabulation

To produce a three-way crosstabulation, specify the columns as a list within the vars parameter, with the tabDisplay parameter equal to list. If you do not specify the tabDisplay parameter, the freqTab action will return three two-way crosstabulation tables. Each table will be created for each distinct group of the first column specified in the list. This example will create a crosstabulation of Model_Year by Campaign_Type by Make.

proc cas;
   * CAS table reference *;
   casTbl = {name = "WARRANTY_CLAIMS", caslib = "casuser"};
 
   * Three-way crosstabulation table *;
   freqTab.freqTab / 
       table = casTbl,
       tabulate = {
		{vars={'Model_Year','Campaign_Type','Make'}}
       }, 
       tabDisplay='list';
quit;

Partial results

Notice in the results above a three-way crosstabulation table was created. The order of the columns is created by the order of the columns specified in the list.

Summary

The freqTab.freqTab CAS action provides a variety of ways to create frequency and crosstabulation tables in the distributed CAS server. There are a variety of parameters you can add to modify it to meet your objectives. We've only scratched the surface!

Additional resources

freqTab 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! Advanced Frequency Tables - Part 4 was published on SAS Users.

12月 162022
 

Welcome back to my SAS Users blog series CAS Action! - a series on fundamentals. In my previous part 1 and part 2 posts I reviewed how to use the simple.freq CAS action to generate frequency distributions for one or more columns and how to save the results. In this post I will show you how to group the results of the freq action.

In this example, I will use the CAS language (CASL) to execute the freq 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. By default the Samples caslib should be available in your SAS Viya environment. I'll load the table to the Casuser caslib and then I'll clean up the CAS table by renaming and dropping columns to make the table easier to use. For more information how to rename columns check out my previous post. Lastly I'll execute the fetch action to preview 5 rows.

proc cas;
   * Specify the input/output CAS table *;
   casTbl = {name = "WARRANTY_CLAIMS", caslib = "casuser"};
 
   * Load the CAS table into memory *;
    table.loadtable / 
        path = "WARRANTY_CLAIMS_0117.sashdat", caslib = "samples",
        casOut = casTbl + {replace=TRUE};
 
* Rename columns with the labels. Spaces replaced with underscores *;
 
   *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 *;
   listElementCounter = 0;
   do columnMetadata over cr.ColumnInfo;
	listElementCounter = listElementCounter + 1;
	convertColLabel = tranwrd(columnMetadata['Label'],' ','_');
	renameColumns[listElementCounter] = {name = columnMetadata['Column'], rename = convertColLabel, label=""};
   end;
 
   * Rename columns *;
   keepColumns = {'Campaign_Type', 'Platform','Trim_Level','Make','Model_Year','Engine_Model',
                  'Vehicle_Assembly_Plant','Claim_Repair_Start_Date', 'Claim_Repair_End_Date'};
   table.alterTable / 
	name = casTbl['Name'], caslib = casTbl['caslib'], 
	columns=renameColumns,
	keep = keepColumns;
 
   * Preview CAS table *;
   table.fetch / table = casTbl, to = 5;
quit;

The results above show a preview of the warranty_claims CAS table.

Add a grouping column

What if you want a frequency distribution of each Model_Year by Make? You can easily do that with the freq action. The key is adding the groupBy sub parameter when referencing the CAS table. Then use the grouped CAS table in the freq action and specify the column to analyze. In this example, the CAS table is grouped by Model_Year and the freq action specifies the Make column.

proc cas;
   * Reference the CAS table and group by Model_Year *;
   casTbl = {name = "WARRANTY_CLAIMS", 
             caslib = "casuser",
	     groupby = "Model_Year"};
 
   * Model_Year by Make frequency *;
   simple.freq / table = casTbl, input = 'Make';
quit;

 

Partial results

The above results show that the freq action returns a separate table for each distinct Model_Year. While this is great information, what if I want a single table with the Model_Year by Make?

Saving the results as a CAS table

One option is saving the results as a CAS table. This works similarly to my previous post CAS-Action! Saving Frequency Tables - Part 2. Simply add the casOut parameter to the freq action. I'll add a label to the new CAS table to give it a description and then preview the new CAS table with the fetch action.

proc cas;
   * Reference the CAS table and group by Model_Year *;
   casTbl = {name = "WARRANTY_CLAIMS", 
             caslib = "casuser",
	     groupby = "Model_Year"};
 
   * Specify the output CAS table information *;
   outputTbl = {name = "yearByMake", caslib = "casuser"};
 
   * Get a frequency of Model_Year by Make and create a CAS table *;
   simple.freq / 
	table = casTbl, 
	input = 'Make',
	casOut = outputTbl || {label = "Year by Make frequency table"};
 
   * Preview the CAS table *;
   table.fetch / table = outputTbl;
quit;

The results above show the freq action with the casOut parameter returns information about the newly created CAS table, and the fetch action returns a preview of the new CAS table. Notice the analysis is grouped by Model_Year and is consolidated into a single table.

Saving the results as a SAS data set

Instead of saving the results back to the CAS server, you can save them as a SAS data set. I did an example in my previous post CAS-Action! Saving Frequency Tables - Part 2. However, when you save the results of an action summarized by groups, you need to combine each individual group into a single result table. To do that you need to use the COMBINE_TABLES function on the dictionary returned from the CAS server. The COMBINE_TABLES function will combine each individual table in a dictionary and return a single result table. Then you can save the new table by using the SAVERESULT statement. Lastly, I'll view the SAS data set using the PRINT procedure.

proc cas;
   * Reference the CAS table and group by Model_Year *;
   casTbl = {name = "WARRANTY_CLAIMS", 
             caslib = "casuser",
	     groupby = "Model_Year"};
 
   * Specify the output CAS table information *;
   outputTbl = {name = "yearByMake", caslib = "casuser"};
 
   * Get a frequency of Model_Year by Make and store the results in a dictionary *;
   simple.freq result=freq_cr / 
	table = casTbl, 
	input = 'Make';
 
   * Combine all the tables in the dictionary and create a result table *;
   freqTbl = combine_tables(freq_cr);
 
   * Save the result table as a SAS data set *;
   saveresult freqTbl dataout=work.yearByMake;
quit;
 
* Preview the SAS data set *;
proc print data=work.yearByMake;
run;

The results above show the new SAS data set. Once you save the results of the CAS server as a SAS data set, you can use familiar SAS knowledge to continue processing the data on the compute server.

Plot the results of the freq action

Now, let's explore and visualize this data. You can use the SGPLOT procedure to visualize the summarized results from the CAS server that you saved as a SAS data set.

title height=14pt justify=left color=charcoal "Total Number of Warranty Claims by Model Year and Car Make";
title2 "";
proc sgplot data=work.yearByMake
			noborder;
	vline Model_Year / 
			group = CharVar 
			Response=Frequency
			markers;
	format Frequency comma16.;
	keylegend / position=topleft title='Car Makes';
	label Frequency='Warranty Claims';
	xaxis display=(nolabel);
run;

The visualization above shows the Zeus car Make is the primary cause of warranty claims in all years, and in the years 2016, 2017 and 2018 had a huge increase in warranty claims.

Summary

Using the groupBy sub parameter when referencing a CAS table enables you to easily group the results of a CAS action. When using the groupBy parameter:

  • the action will return separate result tables for each distinct grouped value
  • the casOut parameter in the action to creates a single CAS table with all the groups
  • the COMBINE_TABLES function combines each distinct group by result table in the dictionary to create a single result table, and then saves that as a SAS data set

Additional resources

freq action
COMBINE_TABLES function
SAVERESULT statement
Plotting a Cloud Analytic Services (CAS) In-Memory Table
SAS® Cloud Analytic Services: CASL Programmer’s Guide 
SAS® Cloud Analytic Services: Fundamentals
CAS Action! - a series on fundamentals
Getting Started with Python Integration to SAS® Viya® - Index

CAS-Action! Grouping Frequency Tables - Part 3 was published on SAS Users.

12月 122022
 

Welcome back to my SAS Users blog series CAS Action! - a series on fundamentals. In my previous post CAS-Action! Simple Frequency Tables - Part 1, I reviewed how to use the simple.freq CAS action to generate frequency distributions for one or more columns using the distributed CAS server. In this post I will show you how to save the results of the freq action as a SAS data set or a distributed CAS table.

In this example, I will use the CAS language (CASL) to execute the freq 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. By default the Samples caslib should be available in your SAS Viya environment. I'll load the table to the Casuser caslib and then I'll clean up the CAS table by renaming and dropping columns to make the table easier to use. For more information how to rename columns check out my previous post. Lastly I'll execute the fetch action to preview 5 rows.

proc cas;
   * Specify the input/output CAS table *;
   casTbl = {name = "WARRANTY_CLAIMS", caslib = "casuser"};
 
   * Load the CAS table into memory *;
    table.loadtable / 
        path = "WARRANTY_CLAIMS_0117.sashdat", caslib = "samples",
        casOut = casTbl + {replace=TRUE};
 
* Rename columns with the labels. Spaces replaced with underscores *;
 
   *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 *;
   listElementCounter = 0;
   do columnMetadata over cr.ColumnInfo;
	listElementCounter = listElementCounter + 1;
	convertColLabel = tranwrd(columnMetadata['Label'],' ','_');
	renameColumns[listElementCounter] = {name = columnMetadata['Column'], rename = convertColLabel, label=""};
   end;
 
   * Rename columns *;
   keepColumns = {'Campaign_Type', 'Platform','Trim_Level','Make','Model_Year','Engine_Model',
                  'Vehicle_Assembly_Plant','Claim_Repair_Start_Date', 'Claim_Repair_End_Date'};
   table.alterTable / 
	name = casTbl['Name'], caslib = casTbl['caslib'], 
	columns=renameColumns,
	keep = keepColumns;
 
   * Preview CAS table *;
   table.fetch / table = casTbl, to = 5;
quit;

The results above show a preview of the warranty_claims CAS table.

One Way Frequency for Multiple Columns

Next, I'll execute the freq action to generate a frequency distribution for multiple columns.

proc cas;
   casTbl = {name = "WARRANTY_CLAIMS", caslib = "casuser"};
   colNames = {'Model_Year', 
               'Vehicle_Assembly_Plant', 
	       {name = 'Claim_Repair_Start_Date', format = 'yyq.'}
   };
   simple.freq / table= casTbl, inputs = colNames;
quit;

The freq CAS action returns the frequency distribution of each column in a single result. While this is great,  what if you want to create a visualization with the data? Or continue processing the summarized data? How do you save this as a table? Well, you have a few options.

Save the results as a SAS data set

First, you can save the results of a CAS action as a SAS data set. The idea here is the CAS action will process the data in the distributed CAS server, and then the CAS server returns smaller, summarized results to the client (SAS Studio). The summarized results can then be saved as a SAS data set.

To save the results of a CAS action simply add the result option after the action with a variable name. The results of an action return a dictionary to the client and store it in the specified variable. For example, to save the results of the freq action as a SAS data set complete the following steps:

  1. Execute the same CASL code from above, but this time specify the result option with a variable name to store the results of the freq action. Here i'll save the results in the variable freq_cr.
  2. Use the DESCRIBE statement to view the structure and data type of the CASL variable freq_cr in the log (not required).
  3. Use the SAVERESULT statement to save the CAS action result table from the dictionary freq_cr as a SAS data set named warranty_freq. To do this specify the key Frequency that is stored in the dictionary freq_cr to obtain the result table.
proc cas;
   * Reference the CAS table *;
   casTbl = {name = "WARRANTY_CLAIMS", caslib = "casuser"};
 
   * Specify the columns to analyze *;
   colNames = {'Model_Year', 
               'Vehicle_Assembly_Plant', 
               {name = 'Claim_Repair_Start_Date', format = 'yyq.'}
   };
   * 1. Analyze the CAS table and store the results *;
   simple.freq result = freq_cr / table= casTbl, inputs = colNames;
 
   * 2. View the dictionary in the log *;
   describe freq_cr;
 
  * 3. Save the result table as a SAS data set *;
   saveresult freq_cr['Frequency'] dataout=work.warranty_freq;
quit;

SAS Log

In the log, the results of the DESCRIBE statement shows the variable freq_cr is a dictionary with one entry. It contains the key Frequency and the value is a result table. The table contains 22 rows and 6 columns. The NOTE in the log shows the SAVERESULT statement saved the result table from the dictionary as a SAS data set named warranty_freq in the work library.

Once the summarized results are stored in a SAS library, use your traditional SAS programming knowledge to process the SAS table. For example, now I can visualize the summarized data using the SGPLOT procedure.

* Plot the SAS data set *;
title justify=left height=16pt "Total Warranty Claims by Year";
proc sgplot data=work.warranty_freq noborder;
	where Column = 'Model_Year';
	vbar Charvar / 
		response = Frequency
		nooutline;
	xaxis display=(nolabel);
	label Frequency = 'Total Claims';
	format Frequency comma16.;
quit;

Save the Results as a CAS Table

Instead of saving the summarized results as a SAS data set, you can create a new CAS table on the CAS server. To do that all you need is to add the casOut parameter in the action. Here I'll save the results of the freq CAS action to a CAS table named warranty_freq in the Casuser caslib, and I will give the table a descriptive label.

proc cas;
   * Reference the CAS table *;
   casTbl = {name = "WARRANTY_CLAIMS", caslib = "casuser"};
 
   * Specify the columns to analyze *;
   colNames = {'Model_Year', 
               'Vehicle_Assembly_Plant', 
               {name = 'Claim_Repair_Start_Date', format = 'yyq.'}
   };
 
   * Analyze the CAS table and create a new CAS table *;
   simple.freq / 
	table= casTbl, 
	inputs = colNames,
	casOut = {
		name = 'warranty_freq',
		caslib = 'casuser',
		label = 'Frequency analysis by year, assembly plant and repair date by quarter'
	};
quit;

The results above show the freq action returned information about the newly created CAS table. Once you have a CAS table in the distributed CAS server you can continue working with it using CAS, or you can visualize the data like we did before using SGPLOT. The key concept here is the SGPLOT procedure does not visualize data on the CAS server. The SGPLOT procedure returns the entire CAS table back to SAS (compute server) as a SAS data set, then the visualization occurs on the client. This means if the CAS table is large, an error or slow processing might occur. However, in our scenario we created a smaller summarized CAS table, so sending 22 rows back to the client (compute server) isn't going to be an issue.

* Make a library reference to a Caslib *;
libname casuser cas caslib='casuser';
 
 
* Plot the SAS data set *;
title justify=left height=16pt "Total Warranty Claims by Year";
proc sgplot data=casuser.warranty_freq noborder;
	where _Column_ = 'Model_Year';
	vbar _Charvar_ / 
		response = _Frequency_
		nooutline;
	xaxis display=(nolabel);
	label _Frequency_ = 'Total Claims';
	format _Frequency_ comma16.;
quit;

Summary

Using the freq CAS action enables you to generate a frequency distribution for one or more columns and enables you to save the results as a SAS data set or a CAS table. They keys to this process are:

  • CAS actions execute on the distributed CAS server and return summarized results back to the client as a dictionary. You can store the dictionary using the result option.
  • Using dictionary manipulation techniques and the SAVERESULT statement you can save the summarized result table from the dictionary as a SAS data set. Once you have the SAS data set you can use all of your familiar SAS programming knowledge on the traditional compute server.
  • Using the casOut parameter in a CAS action enables you to save the summarized results in the distributed CAS server.
  • The SGPLOT procedure does not execute in CAS. If you specify a CAS table in the SGPLOT procedure, the entire CAS table will be sent back to SAS compute server for processing. This can cause an error or slow processing on large tables.
  • Best practice is to summarize large data in the CAS server, and then work with the summarized results on the compute server.

Additional resources

freq action
DESCRIBE statement
SAVERESULT statement
Plotting a Cloud Analytic Services (CAS) In-Memory Table
SAS® Cloud Analytic Services: CASL Programmer’s Guide 
SAS® Cloud Analytic Services: Fundamentals
CAS Action! - a series on fundamentals
Getting Started with Python Integration to SAS® Viya® - Index

 

CAS-Action! Saving Frequency Tables - Part 2 was published on SAS Users.

12月 072022
 

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 generate frequency distributions for one or more columns using the simple.freq CAS action.

In this example, I will use the CAS language (CASL) to execute the freq 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. By default the Samples caslib should be available in your SAS Viya environment. I'll load the table to the Casuser caslib and then I'll clean up the CAS table by renaming and dropping columns to make the table easier to use. For more information how to rename columns check out my previous post. Lastly I'll execute the fetch action to preview 5 rows.

proc cas;
   * Specify the input/output CAS table *;
   casTbl = {name = "WARRANTY_CLAIMS", caslib = "casuser"};
 
   * Load the CAS table into memory *;
    table.loadtable / 
        path = "WARRANTY_CLAIMS_0117.sashdat", caslib = "samples",
        casOut = casTbl + {replace=TRUE};
 
* Rename columns with the labels. Spaces replaced with underscores *;
 
   *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 *;
   listElementCounter = 0;
   do columnMetadata over cr.ColumnInfo;
	listElementCounter = listElementCounter + 1;
	convertColLabel = tranwrd(columnMetadata['Label'],' ','_');
	renameColumns[listElementCounter] = {name = columnMetadata['Column'], rename = convertColLabel, label=""};
   end;
 
   * Rename columns *;
   keepColumns = {'Campaign_Type', 'Platform','Trim_Level','Make','Model_Year','Engine_Model',
                  'Vehicle_Assembly_Plant','Claim_Repair_Start_Date', 'Claim_Repair_End_Date'};
   table.alterTable / 
	name = casTbl['Name'], caslib = casTbl['caslib'], 
	columns=renameColumns,
	keep = keepColumns;
 
   * Preview CAS table *;
   table.fetch / table = casTbl, to = 5;
quit;

The results above show a preview of the warranty_claims CAS table.

One-way frequency table for a single column

To create a simple one-way frequency for a single column use the simple.freq CAS action. In the freq action, use the table parameter to specify the CAS table and the inputs parameter to specify the column to analyze. Here I'm using the warranty_claims CAS table and analyzing the Make column.

proc cas;
   casTbl = {name = "WARRANTY_CLAIMS", caslib = "casuser"};
   simple.freq / table= casTbl, inputs = 'Make';
quit;

The freq action generates a simple one-way frequency table in the distributed CAS server and returns the results to the client. The results of the freq action include:

  • the column that was analyzed in the Column column
  • the distinct values for that column are shown in the Character Value column
  • if a format is associated with that column it appears in the Formatted Value column; if no format exists, you see the same values
  • the Frequency column represents the number of times that value occurs in the column

In the results above, we see the Zeus car make has the most warranty claims.

One-way frequency for multiple columns

To specify multiples columns in the freq action, add a list of columns to the inputs parameter. Here, I'll create a variable named colNames to store a list. In the list, I'll specify the Model_Year, Vehicle_Assembly_Plant and Engine_Model columns, and then use the variable in the inputs parameter.

proc cas;
   casTbl = {name = "WARRANTY_CLAIMS", caslib = "casuser"};
   colNames = {'Model_Year', 'Vehicle_Assembly_Plant', 'Engine_Model'};
   simple.freq / table= casTbl, inputs = colNames;
quit;

In the results above, we see the action returns a single result table with all three columns summarized. The column that was analyzed is shown in the Column column.

Apply a SAS format in the freq action

What if you want to apply a SAS date format to a column during analysis? For example, the Claim_Repair_Start_Date column contains a SAS date value with the DATE9 format. Instead of the detailed DATE9 format, what if I want to see the total number of repairs by year and quarter? Or by year? Or by year and month? You can easily apply a SAS format when using CAS actions.

Let's start by executing the freq CAS action on the Claim_Repair_Start_Date column.

proc cas;
   casTbl = {name = "WARRANTY_CLAIMS", caslib = "casuser"};
   simple.freq / table= casTbl, inputs = 'Claim_Repair_Start_Date';
quit;

The results above show the action created a one way frequency of Claim_Repair_Start_Date using the DATE9 format and stored it in the CAS table. The Numeric Value column shows the raw SAS date values and the Formatted Value column shows the formatted dates.

Now, this analysis is too detailed. I don't want to see repairs by start date. Instead, I'll apply the YYQ format to summarize the dates by year and quarter. You can apply the format within the inputs parameter. In the inputs parameter specify a list of dictionaries. Here I'll use a single dictionary in the list and apply the YYQ format to the Claim_Repair_Start_Date column.

proc cas;
   casTbl = {name = "WARRANTY_CLAIMS", caslib = "casuser"};
   simple.freq / 
	table= casTbl, 
	inputs = {
		{name = 'Claim_Repair_Start_Date', format = 'yyq.'}
	};
quit;

The results above display the frequency by year and quarter. The ability to apply a SAS format during execution enables us to quickly summarize data in a variety of ways.

Create a calculated column in the freq action

Lastly, you can also create calculated columns within an action for ad-hoc analysis. Here I'll create a new column named Make_Platform that concatenates the Make and Platform columns by specifying an expression in the variable calculateMakePlatform. Then I'll add the calculation to the computedVarsProgram parameter in my CAS table reference. Finally, I'll add the new column name to the inputs parameter in the freq action. For more information about creating calculated columns in a CAS table, check out my previous post.

proc cas;
   calculateMakePlatform = 'Make_Platform = catx("-",Make,Platform)';
   casTbl = {name = "WARRANTY_CLAIMS", 
             caslib = "casuser",
             computedVarsProgram = calculateMakePlatform};
   simple.freq / 
	table= casTbl,
	inputs = 'Make_Platform';
quit;

 

The results above show the Zeus-XE has the highest amount of warranty claims.

While viewing the results of the analysis is great, how can a work with these results? Maybe I want to create a visualization? What about creating another CAS table or SAS data set with these results? What about an Excel report? How can we do this? Well, stay tuned for part 2!

Summary

Using the freq CAS action enables you to generate a frequency distribution for one or more columns, apply SAS formats during analysis, and even create calculated columns. CAS actions are optimized to run in the distributed CAS server, are flexible, and can be executed in a variety of languages like Python and R!

Additional resources

freq 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! Simple Frequency Tables - Part 1 was published on SAS Users.