SAS Viya

12月 042019
 

Site relaunches with improved content, organization and navigation.

In 2016, a cross-divisional SAS team created developer.sas.com. Their mission: Build a bridge between SAS (and our software) and open source developers.

The initial effort made available basic information about SAS® Viya® and integration with open source technologies. In June 2018, the Developer Advocate role was created to build on that foundation. Collaborating with many of you, the SAS Communities team has improved the site by clarifying its scope and updating it consistently with helpful content.

Design is an iterative process. One idea often builds on another.

-- businessman Mark Parker

The team is happy to report that recently developer.sas.com relaunched, with marked improvements in content, organization and navigation. Please check it out and share with others.

New overview page on developer.sas.com

The developer experience

The developer experience goes beyond the developer.sas.com portal. The Q&A below provides more perspective and background.

What is the developer experience?

Think of the developer experience (DX) as equivalent to the user experience (UX), only the developer interacts with the software through code, not points and clicks. Developers expect and require an easy interface to software code, good documentation, support resources and open communication. All this interaction occurs on the developer portal.

What is a developer portal?

The white paper Developer Portal Components captures the key elements of a developer portal. Without going into detail, the portal must contain (or link to) these resources: an overview page, onboarding pages, guides, API reference, forums and support, and software development kits (SDKs). In conjunction with the Developers Community, the site’s relaunch includes most of these items.

Who are these developers?

Many developers fit somewhere in these categories:

  • Data scientists and analysts who code in open source languages (mainly Python and R in this case).
  • Web application developers who create apps that require data and processing from SAS.
  • IT service admins who manage customer environments.

All need to interact with SAS but may not have written SAS code. We want this population to benefit from our software.

What is open source and how is SAS involved?

Simply put, open source software is just what the name implies: the source code is open to all. Many of the programs in use every day are based on open source technologies: operating systems, programming languages, web browsers and servers, etc. Leveraging open source technologies and integrating them with commercial software is a popular industry trend today. SAS is keeping up with the market by providing tools that allow open source developers to interact with SAS software.

What is an API?

All communications between open source and SAS are possible through APIs, or application programming interfaces. APIs allow software systems to communicate with one another. Software companies expose their APIs so developers can incorporate functionality and send or request data from the software.

Why does SAS care about APIs?

APIs allow the use of SAS analytics outside of SAS software. By allowing developers to communicate with SAS through APIs, customer applications easily incorporate SAS functions. SAS has created various libraries to aid in open source integration. These tools allow developers to code in the language of their choice, yet still interface with SAS. Most of these tools exist on github.com/sassoftware or on the REST API guides page.

A use case for SAS APIs

A classic use of SAS APIs is for a loan default application. A bank creates a model in SAS that determines the likelihood of a customer defaulting on a loan based on multiple factors. The bank also builds an application where a bank representative enters the information for a new potential customer. The bank application code uses APIs to communicate this information to the SAS model and return a credit decision.

What is a developer advocate?

A developer advocate is someone who helps developers succeed with a platform or technology. Their role is to act as a bridge between the engineering team and the developer community. At SAS, the developer advocate fields questions and comments on the Developers Community and works with R&D to provide answers. The administration of developer.sas.com also falls under the responsibility of the developer advocate.

We’re not done

The site will continue to evolve, with additions of other SAS products and offerenings, and other initiatives. Check back often to see what’s new.
Now that you are an open source and SAS expert, please check out the new developer.sas.com. We encourage feedback and suggestions for content. Leave comments and questions on the site or contact Joe Furbee: joe.furbee@sas.com.

developer.sas.com 2.0: More than just a pretty interface was published on SAS Users.

11月 152019
 

“The future is already here — it's just not very evenly distributed.”  ~ William Gibson, author The same can be said for climate change – global warming is here, in a big way, but its effects are still an arm's length away for many of us. How is climate change [...]

Climate change: It's all about the CO2 was published on SAS Voices by Leo Sadovy

11月 152019
 

Designing interactive reports can be a fun and unique challenge. As user interface experience designers can attest, there are several aspects that go into developing a successful and effective self-service tool. Granted I’m not designing the actual software, but reports require a similar approach to be sure that visualizations are clear and that users can get to the answers they are looking for. Enter prompts.

Reports prompt users to better understand trends, how their data points compare to the whole, and to narrow the scope of data. Being able to pick the placement of these prompts quickly and easily will open the possibilities of your report layouts! I’m specifically speaking about Report and Page level prompts. Traditionally, these global prompt controls were only able to be placed at the top; see the yellow highlighted areas below.

Let’s take a look at an example report with the traditional Report and Page prompt layout. The Report prompts are extremely easy to pick out, since they sit above the pages, but the Page prompts can sometimes blend in with other prompts contained in the report body.

Introduced in the SAS Visual Analytics 8.4 release is the ability to control the layout position of these prompts. Using my example report, let’s change the placement of these prompts. In Edit mode, open the Options pane and use the top level drop-down to select the report name. This will activate the report level, and the report level Options will display. Next, under the Report Controls subgroup, move the placement radio button to the west cardinal point.

Depending on the type of control objects you are using in your report, you may not like this layout yet. For instance, you can see here that my date slider is taking up too much space.

When you activate the slide control, use the Options pane to alter the Slider Direction and Layout. You can even use the Style option to change the font size. You can see that after these modifications, the Report prompt space can be configured to your liking.

Next, let’s change the placement for the Page prompts, for demonstration purposes. From the Options pane, use the top drop-down to select the page name. This will activate the page level, and the page level Options will display. Next, under the Page Controls subgroup, move the placement radio button to the west cardinal position.

You can see that the direction of the button bar control was automatically changed to vertical. Now we can clearly see which prompts belong to the page level.

If I switch to view mode, and adjust the browser size, you can get a better feel for the Report and Page prompt layout changes.

But as with many things, just because you can, doesn’t mean you should. This is where the report designer’s creativity and style can really take flight. Here is the same report, but with my preferred styling.

Notice that I kept the Report prompts along the top but moved the Page prompts to the left of the report. I also added two containers and configured a gray border for each container to better separate the objects. This helps the user quickly see that the drop-down will filter the word cloud is only. I also used the yellow highlighting through styling and a display rule to emphasize the selected continent. The bar chart is fed from an aggregated data source which is why the report prompt is not filtering out the other continents.

Feel free to send me your latest report design ideas!

Additional material related to Report and Page prompts:

New control prompt placement option in SAS Visual Analytics was published on SAS Users.

11月 052019
 

Editor’s note: This is the third article in a series by Conor Hogan, a Solutions Architect at SAS, on SAS and database and storage options on cloud technologies. This article covers the SAS offerings available to connect to and interact with the various database options available in Microsoft Azure. Access all the articles in the series here.

The series

This is the next iteration of a series covering database as a service (DBaaS) and storage offerings in the cloud, this time from Microsoft Azure. I have already published two articles on Amazon Web Services. One of those articles covers the DBaaS offerings and the other covers storage offerings for Amazon Web Services. I will cover Google Cloud Platform in future articles. The goal of these articles is to supply a breakdown of these services to better understand the business requirements of these offerings and how they relate to SAS. I would encourage you to read all the articles in the series even if you are already using a specific cloud provider. Many of the core technologies and services are offered across the different cloud providers. These articles focus primarily on SAS Data Connectors as part of SAS Viya, but all the same functionality is available using a SAS/ACCESS Interface in SAS 9.4. SAS In-Database technologies in SAS Viya, called the SAS Data Connect Accelerator, are synonymous with the SAS Embedded Process.

As companies move their computing to the cloud, they are also moving their storage to the cloud. Just like compute in the cloud, data storage in the cloud is elastic and responds to demand while only paying for what you use. As more technologies move to a cloud-based architecture, companies must consider questions like: Where do I store my data? What cloud services best meet my business requirements? Which cloud vendor should I use? Can I migrate my applications to the cloud? If you are looking to migrate your SAS infrastructure to Azure, look at the SAS Viya QuickStart Template for Azure to see a rapid deployment pattern to get the SAS Viya platform up and running in Azure.

SAS integration with Azure

SAS has extended SAS Data Connectors and SAS In-Database Technologies support to Azure database variants. A database running in Azure is much like your on-premise database, but instead Microsoft manages the software and hardware. Azure’s DBaaS offerings takes care of the scalability and high availability of the database with minimal user input. SAS integrates with your cloud database even if SAS is running on-premise or with a different cloud provider.

Azure databases

Azure offers database service technologies familiar to many users. If you read my previous article on SAS Data Connectors and Amazon Web Services, you are sure to see many parallels. It is important to understand the terminology and how the different database services in Azure best meet the demands of your specific application. Many common databases already in use are being refactored and provided as service offerings to customers in Azure. The advantages for customers are clear: no hardware to manage and no software to install. Databases that scale automatically to meet demand and software that updates and creates backups means customers can spend more time creating value from their data and less time managing their infrastructure.

For the rest of this article I cover various database management systems, the Azure offering for each database type, and SAS integration. First let's consider the diagram below depicting a decision flow chart to determine integration points between Azure database services and SAS. Trace you path in the diagram and read on to learn more about connection details.

Integration points between Azure database services and SAS

Relational Database Management System (RDBMS)

In the simplest possible terms, an RDBMS is a collection of managed tables with rows and columns. You can divide relational databases into two functional groups: online transaction processing (OLTP) and online analytical processing (OLAP). These two methods serve two distinct purposes and are optimized depending in how you plan to use the data in the database.

Transactional Databases (OLTP)

Transactional databases are good at processing reads, inserts, updates and deletes. These queries usually have minimal complexity, in large volumes. Transactional databases are not optimized for business intelligence or reporting. Data processing typically involves gathering input information, processing the data and updating existing data to reflect the collected and processed information. Transactional databases prevent two users accessing the same data concurrently. Examples include order entry, retail sales, and financial transaction systems. Azure offers several types of transactional database services. You can organize the Azure transactional database service into three categories: enterprise licenses, open source, and cloud native.

Enterprise License

Many customers have workloads built around an enterprise database. Azure is an interesting use case because Microsoft is also a traditional enterprise database vendor. Amazon, for example, does not have existing on-premise enterprise database customers. Oracle cloud is the other big player in the enterprise market looking to migrate existing customers to their cloud. Slightly off topic, but it may be of interest to some, SAS does support customers running their Oracle database on Oracle Cloud Platform using their SAS Data Connector to Oracle. Azure offers a solution for customers looking to continue their relationship with Microsoft without refactoring their existing workflows. Customers bring an existing enterprise database licenses to Azure and run SQL Server on Virtual Machines. SAS has extended SAS Data Connector support for SQL Server on Virtual Machines. You can also use your existing SAS license for SAS Data Connector to Oracle or SAS Data Connector to Microsoft SQL Server to interact with SQL Server on Virtual Machines.

Remember you can install and manage your own database on a virtual machine. For example, support for both SAS Data Connector to Teradata and SAS Data Connect Accelerator for Teradata is available for Teradata installed on Azure. If there is not an available database as a service offering, the traditional backup and update responsibilities are left to the customer.

SQL Server Stretch Database is another service available in Azure. If you are not prepared to add more storage to your existing on-premise SQL Server database, you can add capacity using the resources available in Azure. SQL Server Stretch will scale your data to Azure without having to provision any more servers on-premise. New SQL Server capacity will be running in Azure instead of in your data center.

Open Source

Azure provides service offerings for common open source databases like MySQL, MariaDB, and PostgreSQL. You can use your existing SAS license for SAS Data Connector to MYSQL to connect to Azure Database for MYSQL and SAS Data Connector to PostgreSQL to interface with Azure Database for PostgreSQL. SAS has not yet formally supported Azure Database for MariaDB. MariaDB is a variant of MySQL, so validation of support for SAS Data Connector is coming soon. If you need support for MariaDB in Azure database, please comment below and I will share your feedback with product management and testing.

Cloud Native

Azure SQL Database is an iteration of Microsoft SQL Server built for the cloud, combining the performance and availability of traditional enterprise databases with the simplicity and cost-effectiveness of open source databases. SAS has extended SAS Data Connector support for Azure SQL Database. You can use your existing license for SAS Data Connector to Microsoft SQL Server to connect to Azure SQL Database.

Analytical Databases (OLAP)

Analytical Databases optimize on read performance. These databases work best from complex queries in smaller volume. When working with an analytical database you are typically doing analysis on multidimensional data interactively from multiple perspectives. Azure SQL Data Warehouse is the analytical database service offered by Azure. The SAS Data Connector to ODBC combined with a recent version of the Microsoft-supplied ODBC driver is currently the best way to interact with Azure SQL Data Warehouse. Look for the SAS Data Connector to Microsoft SQL Server to support SQL Data Warehouse soon.

NoSQL Databases

A non-relational or NoSQL database is any database not conforming to the relational database model. These databases are more easily scalable to a cluster of machines. NoSQL databases are a more natural fit for the cloud because the loose dependencies make the data easier to distribute and scale. The different NoSQL databases are designed to solve a specific business problem. Some of the most common data structures are key-value, column, document, and graph databases. If you want a brief overview of these database structures, I cover them in my AWS database blog.

For Microsoft Azure, CosmosDB is the option available for NoSQL databases. CosmosDB is multi-model, meaning you can build out your databases to fit the NoSQL model you prefer. Use the SAS Data Connector to ODBC to interact with your Data in Azure CosmosDB.

Hadoop

The traditional deployment of Hadoop is changing dramatically with the cloud. Traditional Hadoop vendors may have a tough time keeping up with the service offerings available in the cloud. Hadoop still offers reliable replicated storage across nodes and powerful parallel processing of large jobs without much data movement. Azure offers HDInsights as their Hadoop as a service offering. Azure HDInsights supports both SAS Data Connector to Hadoop and SAS Data Connect Accelerator for Hadoop.

Finally

It is important to think about the use case for your database and the type of data you plan to store before you select an Azure database service. Understanding your workloads is critical to getting the right performance and cost. When dealing with cloud databases, remember that you will be charged for the storage you use and for the data that you move out of the database. Performing analysis and reporting on your data may require data transfer. Be aware of these costs and think about how you can lower these by keeping frequently accessed data cached somewhere or remain on-premise. Another strategy I’ve seen becoming more popular is taking advantage of the SAS Micro Analytics Service to move the models you have built to run in the cloud provider where your data is stored. Data transfer is cheaper if that data moves between cloud services instead of outside of the cloud provider. Micro Analytics Service allows you to score the data in place without movement from a cloud provider and without having to do an install of SAS.

Additional Resources
1. Support for Databases in SAS® Viya® 3.4
2. Support for Cloud and Database Variants in SAS® 9.4

Accessing Databases in the Cloud – SAS Data Connectors and Microsoft Azure was published on SAS Users.

11月 052019
 

Editor’s note: This is the third article in a series by Conor Hogan, a Solutions Architect at SAS, on SAS and database and storage options on cloud technologies. This article covers the SAS offerings available to connect to and interact with the various database options available in Microsoft Azure. Access all the articles in the series here.

The series

This is the next iteration of a series covering database as a service (DBaaS) and storage offerings in the cloud, this time from Microsoft Azure. I have already published two articles on Amazon Web Services. One of those articles covers the DBaaS offerings and the other covers storage offerings for Amazon Web Services. I will cover Google Cloud Platform in future articles. The goal of these articles is to supply a breakdown of these services to better understand the business requirements of these offerings and how they relate to SAS. I would encourage you to read all the articles in the series even if you are already using a specific cloud provider. Many of the core technologies and services are offered across the different cloud providers. These articles focus primarily on SAS Data Connectors as part of SAS Viya, but all the same functionality is available using a SAS/ACCESS Interface in SAS 9.4. SAS In-Database technologies in SAS Viya, called the SAS Data Connect Accelerator, are synonymous with the SAS Embedded Process.

As companies move their computing to the cloud, they are also moving their storage to the cloud. Just like compute in the cloud, data storage in the cloud is elastic and responds to demand while only paying for what you use. As more technologies move to a cloud-based architecture, companies must consider questions like: Where do I store my data? What cloud services best meet my business requirements? Which cloud vendor should I use? Can I migrate my applications to the cloud? If you are looking to migrate your SAS infrastructure to Azure, look at the SAS Viya QuickStart Template for Azure to see a rapid deployment pattern to get the SAS Viya platform up and running in Azure.

SAS integration with Azure

SAS has extended SAS Data Connectors and SAS In-Database Technologies support to Azure database variants. A database running in Azure is much like your on-premise database, but instead Microsoft manages the software and hardware. Azure’s DBaaS offerings takes care of the scalability and high availability of the database with minimal user input. SAS integrates with your cloud database even if SAS is running on-premise or with a different cloud provider.

Azure databases

Azure offers database service technologies familiar to many users. If you read my previous article on SAS Data Connectors and Amazon Web Services, you are sure to see many parallels. It is important to understand the terminology and how the different database services in Azure best meet the demands of your specific application. Many common databases already in use are being refactored and provided as service offerings to customers in Azure. The advantages for customers are clear: no hardware to manage and no software to install. Databases that scale automatically to meet demand and software that updates and creates backups means customers can spend more time creating value from their data and less time managing their infrastructure.

For the rest of this article I cover various database management systems, the Azure offering for each database type, and SAS integration. First let's consider the diagram below depicting a decision flow chart to determine integration points between Azure database services and SAS. Trace you path in the diagram and read on to learn more about connection details.

Integration points between Azure database services and SAS

Relational Database Management System (RDBMS)

In the simplest possible terms, an RDBMS is a collection of managed tables with rows and columns. You can divide relational databases into two functional groups: online transaction processing (OLTP) and online analytical processing (OLAP). These two methods serve two distinct purposes and are optimized depending in how you plan to use the data in the database.

Transactional Databases (OLTP)

Transactional databases are good at processing reads, inserts, updates and deletes. These queries usually have minimal complexity, in large volumes. Transactional databases are not optimized for business intelligence or reporting. Data processing typically involves gathering input information, processing the data and updating existing data to reflect the collected and processed information. Transactional databases prevent two users accessing the same data concurrently. Examples include order entry, retail sales, and financial transaction systems. Azure offers several types of transactional database services. You can organize the Azure transactional database service into three categories: enterprise licenses, open source, and cloud native.

Enterprise License

Many customers have workloads built around an enterprise database. Azure is an interesting use case because Microsoft is also a traditional enterprise database vendor. Amazon, for example, does not have existing on-premise enterprise database customers. Oracle cloud is the other big player in the enterprise market looking to migrate existing customers to their cloud. Slightly off topic, but it may be of interest to some, SAS does support customers running their Oracle database on Oracle Cloud Platform using their SAS Data Connector to Oracle. Azure offers a solution for customers looking to continue their relationship with Microsoft without refactoring their existing workflows. Customers bring an existing enterprise database licenses to Azure and run SQL Server on Virtual Machines. SAS has extended SAS Data Connector support for SQL Server on Virtual Machines. You can also use your existing SAS license for SAS Data Connector to Oracle or SAS Data Connector to Microsoft SQL Server to interact with SQL Server on Virtual Machines.

Remember you can install and manage your own database on a virtual machine. For example, support for both SAS Data Connector to Teradata and SAS Data Connect Accelerator for Teradata is available for Teradata installed on Azure. If there is not an available database as a service offering, the traditional backup and update responsibilities are left to the customer.

SQL Server Stretch Database is another service available in Azure. If you are not prepared to add more storage to your existing on-premise SQL Server database, you can add capacity using the resources available in Azure. SQL Server Stretch will scale your data to Azure without having to provision any more servers on-premise. New SQL Server capacity will be running in Azure instead of in your data center.

Open Source

Azure provides service offerings for common open source databases like MySQL, MariaDB, and PostgreSQL. You can use your existing SAS license for SAS Data Connector to MYSQL to connect to Azure Database for MYSQL and SAS Data Connector to PostgreSQL to interface with Azure Database for PostgreSQL. SAS has not yet formally supported Azure Database for MariaDB. MariaDB is a variant of MySQL, so validation of support for SAS Data Connector is coming soon. If you need support for MariaDB in Azure database, please comment below and I will share your feedback with product management and testing.

Cloud Native

Azure SQL Database is an iteration of Microsoft SQL Server built for the cloud, combining the performance and availability of traditional enterprise databases with the simplicity and cost-effectiveness of open source databases. SAS has extended SAS Data Connector support for Azure SQL Database. You can use your existing license for SAS Data Connector to Microsoft SQL Server to connect to Azure SQL Database.

Analytical Databases (OLAP)

Analytical Databases optimize on read performance. These databases work best from complex queries in smaller volume. When working with an analytical database you are typically doing analysis on multidimensional data interactively from multiple perspectives. Azure SQL Data Warehouse is the analytical database service offered by Azure. The SAS Data Connector to ODBC combined with a recent version of the Microsoft-supplied ODBC driver is currently the best way to interact with Azure SQL Data Warehouse. Look for the SAS Data Connector to Microsoft SQL Server to support SQL Data Warehouse soon.

NoSQL Databases

A non-relational or NoSQL database is any database not conforming to the relational database model. These databases are more easily scalable to a cluster of machines. NoSQL databases are a more natural fit for the cloud because the loose dependencies make the data easier to distribute and scale. The different NoSQL databases are designed to solve a specific business problem. Some of the most common data structures are key-value, column, document, and graph databases. If you want a brief overview of these database structures, I cover them in my AWS database blog.

For Microsoft Azure, CosmosDB is the option available for NoSQL databases. CosmosDB is multi-model, meaning you can build out your databases to fit the NoSQL model you prefer. Use the SAS Data Connector to ODBC to interact with your Data in Azure CosmosDB.

Hadoop

The traditional deployment of Hadoop is changing dramatically with the cloud. Traditional Hadoop vendors may have a tough time keeping up with the service offerings available in the cloud. Hadoop still offers reliable replicated storage across nodes and powerful parallel processing of large jobs without much data movement. Azure offers HDInsights as their Hadoop as a service offering. Azure HDInsights supports both SAS Data Connector to Hadoop and SAS Data Connect Accelerator for Hadoop.

Finally

It is important to think about the use case for your database and the type of data you plan to store before you select an Azure database service. Understanding your workloads is critical to getting the right performance and cost. When dealing with cloud databases, remember that you will be charged for the storage you use and for the data that you move out of the database. Performing analysis and reporting on your data may require data transfer. Be aware of these costs and think about how you can lower these by keeping frequently accessed data cached somewhere or remain on-premise. Another strategy I’ve seen becoming more popular is taking advantage of the SAS Micro Analytics Service to move the models you have built to run in the cloud provider where your data is stored. Data transfer is cheaper if that data moves between cloud services instead of outside of the cloud provider. Micro Analytics Service allows you to score the data in place without movement from a cloud provider and without having to do an install of SAS.

Additional Resources
1. Support for Databases in SAS® Viya® 3.4
2. Support for Cloud and Database Variants in SAS® 9.4

Accessing Databases in the Cloud – SAS Data Connectors and Microsoft Azure was published on SAS Users.

10月 312019
 

Buy my costume, Georgie!

While growing up in the 80's, I watched The Golden Girls on TV with my Grandma Betty. Now, when my sister visits, we binge watch reruns on TV Land. I was excited when I saw for this Halloween, you could buy Golden Girls costumes! Too bad they sold out right away, making them one this year's most popular costumes.

For the record, I wasn't planning to dress up tonight as a Golden Girl, but the news got me to thinking how Halloween costumes have changed over the years. What was popular when? Hence, this post. I explain how to use SAS REST APIs to append a table containing historic costume data with this year's most popular costumes (including the Golden Girls and Pennywise from It). While looking at costume data in this example, consider the append steps as a template to translate any table needing updates in SAS Viya, using REST APIs.

The data

There I am, in the middle

I created a data set containing the most popular Halloween costumes from the last 50 years (1968-2018). I compiled the data from several sources who couldn't seem to agree on the best-selling costume for a given year, so I combined the lists. Many years have two entries. The data here isn't as important as the append table procedure. What fun to review the costumes list! It was not hard to tell in what year certain movies (and their sequels) were released. Only one costume I wore made the list – my 1979 Ace Frehley outfit!

The process

The procedures in this example run on SAS Viya, utilizing the Cloud Analytics Services (CAS) REST APIs. CAS REST APIs use CAS actions to perform statistical methods across a variety of SAS products. You can learn more about CAS REST APIs and CAS Actions in the Using SAS Cloud Analytics Service REST APIs to run CAS Actions post or on developer.sas.com.

Below, I'll detail each REST call along with sample code. I originally used Postman to organize my calls. This allowed me to utilize pre and post-call scripting to handle responses and create variables. You can find the entire Postman collection here on GitHub. For ease-of-display purposes in this post, I'll use equivalent cURL commands.

Pre-requisites

I registered my client, obtained access token, and added it as an environment variable ACCESSTOKEN. For more information on registering a client or getting an access token, see my earlier post Authentication to SAS Viya: a couple of approaches.

Create a CAS session

Before running any CAS actions, we need to establish a connection to the SAS Viya server.

curl -X POST https://sasserver:8777/cas/sessions \
  -H 'Authorization: Bearer $ACCESSTOKEN' \
  -H 'Content-Type: application/vnd.sas.cas.session+json'

The result of this call is a session id in the form of a089ce2b-8116-7a40-b3e3-6e39b7b5566d. This will be used in all subsequent REST calls. You could easily create another variable for further use. In the examples below I substitute the actual session id with <session-id>. You will need to substitute this place holder when attempting the steps on your own.

Create a global Caslib HALLOWEEN

Data in CAS is stored in a Caslib. In the step below, I create a Caslib called HALLOWEEN and link it to a physical server path (/home/sasdemo/halloween), where the table is stored.

curl -X POST https://sasserver:8777/cas/sessions/<session-id>/actions/table.addCaslib \
  -H 'Authorization: Bearer $ACCESSTOKEN' \
  -H 'Content-Type: application/json' \
  -d {"name":"HALLOWEEN","path":"/home/sasdemo/halloween","description":"HALLOWEEN","subDirectories":"false","permission":"PUBLICWRITE","session":"false","dataSource":{"srcType":"path"},"createDirectory":"true","hidden":"false","transient":"false"}

Note that I created the directory ~/halloween and set permissions as needed. Further, since the Caslib is global, other users have access to the data. This step (and the next step) are one time requests. If you were to repeat this process you would not need to create the Caslib nor upload the data.

Copy data set costumesByYear into HALLOWEEN's path

Now that we have a Caslib and a path, we load the data table to the server. In this instance, I copy the costumesByYear.xlsx file into /home/sasdemo/halloween. There are multiple ways to upload data to the server. You can read more about the various methods in the SAS documentation.

Create a temporary Caslib TEMP

While our data lives in the HALLOWEEN Caslib, we want to create a temporary Caslib to run the append step. We will then save the appended table back into HALLOWEEN. The following code creates a new Caslib called TEMP.

curl -X POST https://sasserver:8777/cas/sessions/<session-id>/actions/table.addCaslib \
  -H 'Authorization: Bearer $ACCESSTOKEN' \
  -H 'Content-Type: application/json' \
  -d {"name":"TEMP","path":"/home/sasdemo/temp","description":"TEMP","subDirectories":"false","permission":"PUBLICWRITE","session":"false","dataSource":{"srcType":"path"},"createDirectory":"true","hidden":"false","transient":"false"}

Now we're ready to load the data into memory and append the table.

Load costumesByYear into memory

First, we load costumesByYear into memory in the TEMP Caslib.

curl -X POST https://sasserver:8777/cas/sessions/<session-id>/actions/table.loadTable
  -H 'Authorization: Bearer $ACCESSTOKEN' \
  -H 'Content-Type: application/json' \
  -d {"path":"costumesByYear.xlsx","caslib":"HALLOWEEN","importOptions":{"fileType":"EXCEL"},"casOut":{"caslib":"TEMP","name":"costumesByYear","promote":"true"}}

Create a temporary table data2019 with containing append data

Next, we create a new table called data2019 with costume data for 2019 in TEMP.

curl -X PUT https://sasserver:8777/cas/sessions/<session-id>/actions/upload
  -H 'Authorization: Bearer $ACCESSTOKEN' \
  -H 'Content-Type: text/plain' \
  -H 'JSON-Parameters: {"casOut":{"caslib":"TEMP","name":"data2019","promote":"true"},"importOptions":{"fileType":"CSV"}}' \
  --data-binary $'Year,Costume\n2019,The Golden Girls\n2019,Pennywise'

Run data step to append data2019 to costumesByYear table

Finally, we run data step code to append table data2019 to table costumesByYear.

curl -X POST https://sasserver:8777/cas/sessions/<session-id>/actions/runCode \
  -H 'Authorization: Bearer $ACCESSTOKEN' \
  -H 'Content-Type: application/json' \
  -d {"code": "data temp.costumesbyyear(append=force) ; set temp.data2019;run;"}

Save the costumesByYear table back to the HALLOWEEN CASlib

Now that we have successfully appended the costumesByYear table in the TEMP Caslib, we are ready to save back to the HALLOWEEN Caslib.

curl -X POST https://sasserver:8777/cas/sessions/<session-id>/actions/table.save \
  -H 'Authorization: Bearer $ACCESSTOKEN' \
  -H 'Content-Type: application/json' \
  -d {"table":{"name":"costumesbyyear","caslib":"TEMP","singlePass":"false"},"name":"costumesbyyear","replace":"true","compress":"false","caslib":"HALLOWEEN","exportOptions":{"fileType":"BASESAS"}}

Delete TEMP Caslib

The TEMP Caslib is just that, temporary. With the code below we drop the Caslib and all its data.

curl -X POST https://sasserver:8777/cas/sessions/<session-id>/table.dropCaslib \
  -H 'Authorization: Bearer $ACCESSTOKEN' \
  -H 'Content-Type: application/json' \
  -d {"caslib":"TEMP"}

Delete the CAS session

The final step is to close our connection to the CAS server.

curl -X DELETE https://sasserver:8777/cas/sessions/<session-id> \
  -H 'Authorization: Bearer $ACCESSTOKEN'

Wrapping it up

There you have it. With a few simple commands we were able to load, append, and save a table. This example is fairly simple in scope, but translates into more complex use cases. The steps for my 2 x 50 table are the same as it would be for a 5GB table with hundreds of columns and millions of rows.

I have asked my mother to send the Polaroid photo of me as Ace in 1979. She just has to dig it out of a photo album. Check back in a week so you can gain fodder and poke fun at me.

Additional Resources

developer.sas.com - developers site for SAS
GitHub resources - GitHub repository for code used in this post

Append tables in SAS® Viya® with REST APIs – a treat, no tricks was published on SAS Users.

10月 222019
 

PROC SQL

PROC SQL is a very powerful ANSI 92 compliant version of SQL that also allows us to leverage many unique SAS capabilities. Recently I was asked if the PROC SQL in Figure 1 could be refactored into PROC FedSQL so it could run faster by leveraging SAS Viya’s in-memory engine CAS (SAS® Cloud Analytic Services). I was struggling to find a way to refactor this into PROC FedSQL, so I reached out to the SAS Jedi (aka Mark Jordan) for help.

/* Original SQL Statements */
proc sql; create table BenchMark as
     select count(*) as ItemCount
     , sum( abs( nhits - nruns ) < 0.1*natbat )   as DIFF_10
     from sashelp.baseball;
run;

Figure 1. Original PROC SQL

In Figure 2, we can review the SAS Log of our PROC SQL code.

  • It is line 77 that we want to refactor into PROC FedSQL so we can benefit performance improvements by running that code distributed in CAS.
  • On line 77, we use the alias DIFF_10 to create the new column name that is calculated by the two SAS functions SUM and ABS.
  • The expression on line 77 will cause SQL to return a value of 1 if the condition is true and a value of 0 if the condition is false.
  • The alias DIFF_10 will contain the summation of the value returned by the condition (i.e. 0 or 1) for all rows in our data set SASHELP.BASESBALL.

In Figure 5, we can review the results of our PROC SQL statement.

Figure 2. SAS Log of PROC SQL

PROC FedSQL

PROC FedSQL is ANSI 99 compliant without any of the unique SAS capabilities that PROC SQL contains, but PROC FedSQL is CAS enabled, which allows us to leverage SAS Viya’s distributed computing capabilities to improve run-times. Figure 3 is the refactored PROC FedSQL code that the SAS Jedi came up with.

/* PROC FedSQL code */
proc fedsql sessref=casauto; 
   create table BenchMark as
     select count(*) as ItemCount
     , sum(case 
           when (abs (nhits - nruns ) < (0.1*natbat)
                ) is true then 1 end 
          ) as DIFF_10
     from baseball;
quit;

Figure 3. CAS-enabled PROC FedSQL

Figure 4 contains the SAS Log of our CAS enabled PROC FedSQL.

  • Notice on lines 77 we added a CASE statement to the SUM function for our alias DIFF_10.
  • On lines 78-79, the WHEN statement return a value of 1 when the condition is true and a value of 0 when it is false.
  • The alias DIFF_10 will contain the summation, of the value returned by the CASE statement (i.e. 0 or 1) for all rows in our CAS table BASESBALL.

In Figure 5 we can review the results of our PROC FedSQL statement.

Figure 4. SAS log of PROC FedSQL code

Figure 5. Validation that the values from the refactoring of PROC SQL into PROC FedSQL match

Conclusion

As we adopt SAS Viya, a goal is to leverage CAS to speed up the processing of routines written in PROC SQL. To accomplish this, refactor PROC SQL code into PROC FedSQL code. For PROC SQL that cannot be refactored, simply run that PROC SQL code as-is in SAS Viya’s SAS Programming Run-time Environment (SPRE).

SAS® Viya®: How to Emulate PROC SQL Using CAS-Enabled PROC FedSQL was published on SAS Users.

10月 212019
 

Image by rawpixel from Pixabay

When my younger son grabs a book or a toy from his older siblings without permission, his line of defense is always the same: “Sharing is caring!” Our kids' schools teach and reinforce this philosophy. Likewise, our family has rules to ensure peaceful, orderly sharing.

Similarly, many organizations value collaboration. They encourage researchers, data owners, data scientists and business analysts to share work product and ideas and facilitate it among their teams. As with families, they often find it easier said than done.

A big part of my job is to meet customers and advise them on how SAS technology can help solve their business challenges. A recurring topic has been around SAS Viya, the analytics capabilities collectively known as the SAS® Platform. I emphasize how SAS Viya seamlessly enables collaboration across diverse users and teams.

SAS Viya collaboration use case with Commitments of Traders data

How does it work in real life? Here is an example to demonstrate how SAS coders and business analysts can easily collaborate on SAS Viya. I am using a publicly available data set known as Commitments of Traders (COT) that the U.S. Commodity Futures Trading Commission (CFTC) publishes on its website (https://www.cftc.gov/MarketReports/CommitmentsofTraders/index.htm.) Traders and researchers closely watch and analyze this data set for trends and price movements in the commodities market.

A SAS programmer readies the data

Figure 1: Drop-down menu

First, I need to bring the original COT file, saved in ‘.txt’ format, to my enterprise SAS environment. On SAS Viya, I have a choice of using a programmatic or graphical user interface (GUI) approach to import data and perform data wrangling/preparation. Both interfaces are easily accessible from a drop-down menu on SAS Drive, a web-based central hub for SAS Viya applications (see Figure 1). I choose the programmatic approach by clicking on Develop SAS Code from the drop-down menu.

SAS® Studio, the programming interface for SAS Viya, is a web-based development environment that includes code autocomplete, a library of frequently used code snippets, pre-built GUI wizards for numerous analytical routines, etc.

In SAS Studio, I prepare the data with five steps: 1) Import the raw text file; 2) Reduce the number of variables; 3) Compute the traders’ net position; 4) Import the mapping table; and 5) Add the commodity category variable from the mapping table. For all of these steps, I use SAS DATA Step and PROC statements that have been around for 40+ years – old school SAS.

The last two steps in my program, Step 6 and Step 7, are unique to SAS Viya (see Figure 2 below.) Those lines of code start the Cloud Analytics Server (CAS) session and load the final curated table into CAS, an in-memory distributed analytical engine that I consider the “heart and soul” of SAS Viya. Once the data is in CAS, an authorized user of my enterprise SAS Viya environment can easily locate data in CAS library/catalog for data exploration, modeling, or developing business intelligence content.

Figure 2: CAS

A business analyst creates and shares an interactive dashboard

Now it's the business analyst's turn. In this example, my teammate wants to build an interactive dashboard using curated COT data that I (the programmer) loaded into CAS. The analyst will access SAS Drive, select Explore and Visualize Data option from the menu, and be directed to SAS Visual Analytics, a web-based application that allows you to explore data and build point-and-click, interactive visualizations, no coding skills required.

Figure 3 and Figure 4 below show examples of the types of drillable dashboards and reports you can easily develop in SAS Visual Analytics with a few clicks. You can share the report internally via web link, view it in MS Office products, or publish it on the intranet or external facing website.

Figure 3: COT Dashboard

 

Figure 4: Monthly Trend

Collaboration bridges diverse skill sets, fosters successful projects

Studies show that success of any analytical project requires multi-disciplinary teams that include database administrators, data scientists, analysts, subject matter experts, management and IT support. SAS Viya helps them capitalize on their strengths to promote frictionless collaboration in a secure and controlled environment.

This post, focused on collaboration between SAS coders and business analysts, only scratches the surface of SAS Viya's collaboration and knowledge sharing capabilities. Likewise, open source coders (R and Python) and business analysts can collaborate on SAS Viya too.

Free trials below give programmers and business analysts a taste of what's possible with SAS Viya. Try one and tell us about it in the comments.

SAS® Visual Analytics on SAS® Viya® | Try it as a business analyst! SAS® Visual Analytics on SAS® Viya® | Try it as a programmer!

How SAS® Viya fosters collaboration was published on SAS Users.

10月 212019
 

Image by rawpixel from Pixabay

When my younger son grabs a book or a toy from his older siblings without permission, his line of defense is always the same: “Sharing is caring!” Our kids' schools teach and reinforce this philosophy. Likewise, our family has rules to ensure peaceful, orderly sharing.

Similarly, many organizations value collaboration. They encourage researchers, data owners, data scientists and business analysts to share work product and ideas and facilitate it among their teams. As with families, they often find it easier said than done.

A big part of my job is to meet customers and advise them on how SAS technology can help solve their business challenges. A recurring topic has been around SAS Viya, the analytics capabilities collectively known as the SAS® Platform. I emphasize how SAS Viya seamlessly enables collaboration across diverse users and teams.

SAS Viya collaboration use case with Commitments of Traders data

How does it work in real life? Here is an example to demonstrate how SAS coders and business analysts can easily collaborate on SAS Viya. I am using a publicly available data set known as Commitments of Traders (COT) that the U.S. Commodity Futures Trading Commission (CFTC) publishes on its website (https://www.cftc.gov/MarketReports/CommitmentsofTraders/index.htm.) Traders and researchers closely watch and analyze this data set for trends and price movements in the commodities market.

A SAS programmer readies the data

Figure 1: Drop-down menu

First, I need to bring the original COT file, saved in ‘.txt’ format, to my enterprise SAS environment. On SAS Viya, I have a choice of using a programmatic or graphical user interface (GUI) approach to import data and perform data wrangling/preparation. Both interfaces are easily accessible from a drop-down menu on SAS Drive, a web-based central hub for SAS Viya applications (see Figure 1). I choose the programmatic approach by clicking on Develop SAS Code from the drop-down menu.

SAS® Studio, the programming interface for SAS Viya, is a web-based development environment that includes code autocomplete, a library of frequently used code snippets, pre-built GUI wizards for numerous analytical routines, etc.

In SAS Studio, I prepare the data with five steps: 1) Import the raw text file; 2) Reduce the number of variables; 3) Compute the traders’ net position; 4) Import the mapping table; and 5) Add the commodity category variable from the mapping table. For all of these steps, I use SAS DATA Step and PROC statements that have been around for 40+ years – old school SAS.

The last two steps in my program, Step 6 and Step 7, are unique to SAS Viya (see Figure 2 below.) Those lines of code start the Cloud Analytics Server (CAS) session and load the final curated table into CAS, an in-memory distributed analytical engine that I consider the “heart and soul” of SAS Viya. Once the data is in CAS, an authorized user of my enterprise SAS Viya environment can easily locate data in CAS library/catalog for data exploration, modeling, or developing business intelligence content.

Figure 2: CAS

A business analyst creates and shares an interactive dashboard

Now it's the business analyst's turn. In this example, my teammate wants to build an interactive dashboard using curated COT data that I (the programmer) loaded into CAS. The analyst will access SAS Drive, select Explore and Visualize Data option from the menu, and be directed to SAS Visual Analytics, a web-based application that allows you to explore data and build point-and-click, interactive visualizations, no coding skills required.

Figure 3 and Figure 4 below show examples of the types of drillable dashboards and reports you can easily develop in SAS Visual Analytics with a few clicks. You can share the report internally via web link, view it in MS Office products, or publish it on the intranet or external facing website.

Figure 3: COT Dashboard

 

Figure 4: Monthly Trend

Collaboration bridges diverse skill sets, fosters successful projects

Studies show that success of any analytical project requires multi-disciplinary teams that include database administrators, data scientists, analysts, subject matter experts, management and IT support. SAS Viya helps them capitalize on their strengths to promote frictionless collaboration in a secure and controlled environment.

This post, focused on collaboration between SAS coders and business analysts, only scratches the surface of SAS Viya's collaboration and knowledge sharing capabilities. Likewise, open source coders (R and Python) and business analysts can collaborate on SAS Viya too.

Free trials below give programmers and business analysts a taste of what's possible with SAS Viya. Try one and tell us about it in the comments.

SAS® Visual Analytics on SAS® Viya® | Try it as a business analyst! SAS® Visual Analytics on SAS® Viya® | Try it as a programmer!

How SAS® Viya fosters collaboration was published on SAS Users.

10月 152019
 

In a previous post, I discussed using logs to troubleshoot problems in your Viya environment. In this post, I will look at some additional ways to troubleshoot using some of the tools provided by the Viya Operations Infrastructure. With applications, servers and numerous micro-services all working together and generating their own logs in Viya, it can be difficult to find relevant logs. In order to manage the large number of logs and to enable you to locate messages of interest, the operations infrastructure provides components to collect and store log messages.

The collection process is illustrated in the diagram below.

Co-ordinated by the operations infrastructure:

  • sas-watch log continuously collects and sends log messages to the RabbitMQ exchange
  • sas-stream pulls the messages from RabbitMQ and writes them to disk as a tab-separated value (TSV) file
  • Every five minutes, the sas-ops-agentsrv runs the DatamartEtl task to extract log messages from the TSV file and load them into the VIYALOGS CAS-indexed search table

SAS Environment Manager uses the information in the VIYALOGS table and the VIYALOGS_SOURCES tables to display log messages and graphs that contain the frequency and trends of messages. The SAS Environment Manager LOG’s interface makes it really easy to search and analyze log messages. Using the interface, you can view, subset and search logs. The interface has the filtering capabilities on the left hand side and displays the messages on the right. By default, the filter is set to display all messages from all applications and services from the last 30 minutes.

You can modify the filter to extend or shorten the timeframe, subset the level of messages displayed or the source (service/application) that the messages are coming from. You can also search for any text within a message.

Many administrators would prefer a command-line interface, and the good news is there is one.

sas-ops is a command-line interface which allows for the monitoring of the operational infrastructure in a SAS Viya deployment environment.

I have found the sas-ops log command very useful to troubleshoot problems. The sas-ops log command can be used to stream log messages that are generated by SAS Viya applications and services. The messages can be streamed to a terminal window or piped to a file. The sas-ops logs command is located at /opt/sas/viya/home/bin and can be run from any machine in a Viya environment that is included in the CommandLine.

When would you use sas-ops logs to stream log messages? Some potential scenarios are to:

  • troubleshoot a poorly performing report or analysis
  • debug problems in the environment such as logon issues
  • monitor access to resources

In these cases, using sas-ops logs you can stream the log messages from all services to a single file or terminal.

In its simplest form, the command live streams all log messages from a Viya environment to the terminal. Selecting CTRC+C will stop the streaming.

./sas-ops logs

Partial output from the stream is shown below.

If you want to save the output, you can redirect the stream to a file.

./sas-ops logs &gt; /tmp/mylog.log

You can get more creative and achieve more complex tasks. You can change the format of the message output using –format. For example, to create a file with json which could be read by another process use:

./sas-ops logs –format pretty &gt; mylogs.json

You can also:

  • stream messages for just a specific Viya service
  • filter logs messages by text in a regular expression
  • stream for a specific duration

The duration is specified using the format 0h0m0s0ms, but you can also use individual parts of the specification, for example 30s for 30 seconds or 5m for 5 minutes.

Consider the situation where we want to monitor access to a particular CAS table over a specific period of time. The command below will output to a file all messages that contain the table name HR_SUMMARY for a period of 5 minutes.

./sas-ops logs –match HR_SUMMARY –timeout 5m &gt; /tmp/hr_summary_access.log

The output shows all the CAS actions that were performed on the table during the time period.

You can subset the stream to one service.

Consider a case where a user is having an issue logging in and you suspect you have an issue with the LDAP setup. To check the problem, you can firstly enable DEBUG logging on com.sas.identities. Then stream the log messages from the identities service.

./sas-ops logs –format pretty –source identities &gt; logonerrors.json

Viewing the output shows that there is something wrong with the LDAP query.

I think you will agree that sas-ops logs is a very useful tool for monitoring and troubleshooting issues in a Viya environment. For more information, check out the following resources:

I would like to thank Bryan Ellington for his helpful input with this post.

Capturing log messages from Viya deployments was published on SAS Users.