The data platform landscape has obviously seen many new entrants in recent years. One of the most popular ones around is Databricks who are promoting the lakehouse concept, a storage that brings together the best qualities of a data lake and a data warehouse. The beauty of versatile storage is flexible data loading for almost any kind of data. Within that easiness also lies the risk of uncontrolled data hoarding, as we already witnessed in the heydays of Hadoop. With great data comes great responsibility and that responsibility is best implemented with data governance. SAS has helped many of our customers connect these two powerful data and analytics platforms together and embrace them with seamless data access, data governance, and data quality. Let’s face it, data is only useful when you can properly access it. SAS Viya provides many powerful ways to access all the commonly used data sources today. While SAS Viya provides integration with most data sources, in this blog we focus on Databricks. It can be accessed through a specific Databricks data connection, the Spark connection or the JDBC connection. I’m calling them connections for simplicity, while in most cases there is a SAS/ACCESS interface under the hood.   The image above is from SAS Viya’s Data Explorer and shows how simple it is to define new data connections. My colleague Cecily explains this hands-on in her blog: SAS and Databricks: Your Practical Guide to Data Access and Analysis For clarity, SAS Viya’s connection to Spark is delivered with a JDBC driver for Databricks and enables out-of-the-box connectivity. Using this embedded driver, a Databricks connection can be achieved in either by defining a Spark LIBNAME statement that specifies the connection options OR by defining a Spark LIBNAME statement that specifies a JDBC URL for the target data source in the URL= option.   SAS/ACCESS interfaces in general offer very good performance but finding the optimal configuration may take some planning and testing, as creating a connection from SAS Viya to Databricks is possible through at least ODBC, JDBC and Spark interfaces. SAS continuously updates and improves our SAS/ACCESS interfaces to provide continued compatibility and optimized performance.   Once connected with the data, SAS Viya provides capabilities to track the lineage of data from various sources, such as Databricks. This helps organizations understand how data is used and where it comes from, supporting transparency and traceability. Lineage also helps to understand the effect of planned changes to the data process. A typical scenario would be a requested change to the data model of a source table.  That change of course needs to be carried out through the data process all the way to the end result. The example below shows an example of a simple data process. By following the steps in the lineage flow we learn the following things: Source tables are accessed in Databricks with the target table under the same schema A 2-table join is executed by Databricks, and result data also remains in Databricks The result table is then loaded into SAS Viya’s in-memory engine called CAS (Cloud Analytics Server) A SAS Visual Analytics report has been created based on the CAS in-memory table In addition to providing Lineage, SAS Viya offers robust access control mechanisms to ensure that only authorized users have access to Databricks (and any other) data that has been introduced to SAS Viya. SAS Viya allows organizations to define and enforce their data governance policies. This includes policies related to data quality, security, and compliance. With role-based access control and tight integration with enterprise authentication systems, SAS Viya can smoothen your access to Databricks. Single sign-on is available to authenticate connections to from SAS Viya to Databricks in Azure by utilizing a Microsoft Entra ID token that is obtained and utilized by SAS Viya’s credential services to allow seamless access.   SAS Viya supports data quality monitoring and profiling of any data, including Databricks. This makes it simple for Data Engineers and Data Stewards to assess and monitor the quality of data, identify any data anomalies, and have the necessary tools take corrective actions, for example with Clean Data and Parse Data steps in SAS Studio flows.   My colleague Patric has explained the data quality process in detail in his data quality blog here:  Data Brilliance Unleashed: SAS Data Quality against Databricks - Precision, Performance, Perfection In his blog Patric takes you through the whole quality improvement process, including identification, splitting, standardization, match code creation, clustering and entity resolution, so it’s a wholeheartedly recommended read!   For those developers who prefer to do their data quality in code, SAS Studio includes a collection of data quality code snippets. They can be run as-is or embedded into SAS Studio flows as code steps. You can read more about efficient use of snippets here: Working with snippets   A data catalog is a central metadata repository that helps users discover, understand, and manage all their data assets. SAS Viya includes SAS Information Catalog to discover catalogued data from any supported data source, for example Databricks, making it much easier for the data users to find relevant data and understand its context. If you come from SAS9 background, you are most likely familiar with the concept of metadata. While SAS Viya does not have a similar Metadata Server as SAS9 to manage both technical and business metadata, rest assured, it’s still there. SAS Information Catalog is based on discovery agents set up by the platform administrator that work hard to gather metadata on the data assets connected to your environment. As SAS Viya’s discovery agents gather the metadata, they also go through a data profiling process that extracts the descriptive data metrics and quality indicators on your data. SAS Information Catalog provides a centralized view of all your metadata, thus helping you to understand the characteristics of your data. A good understanding of the total data asset is key in building comprehensive data governance, and the asset dashboard in SAS Information Catalog does exactly that:   The above image is borrowed from the great blog post SAS Information Catalog: All your information assets under one roof by my colleague Rajeeve Narula. What is great about SAS Information Catalog, once you find the data you’re looking for, you can instantly view the analyzed data metrics with a one click drill-down, an example of a typical column level analysis below: Much like the SAS Viya platform in general, SAS Information Catalog provides REST APIs accessible from SAS, Python, or shell scripts. SAS Information Catalog REST APIs enable searching and identification of files, tables and other assets based on specific criteria. Developers and data engineers can leverage these APIs to incorporate files and tables into data management tasks, as well as trigger actions or automate workflows. These REST APIs can gather insightful metadata and provide a comprehensive view of your data landscape. With the information gained, data users can explore their data ecosystem effectively, providing a high-level overview of assets and enabling informed decision making. Depending on your task, you can interact with several REST endpoints, examples of these in the image below: For further insight how to utilize SAS Information Catalog’s REST APIs, have a look at my colleague Bogdan Teleuca’s informative blog post: Leveraging SAS® Information Catalog REST APIs: Programmatically Discovering Data. Having firm control of your metadata is crucial, but it’s difficult to understand the big picture without a link to the real world. This is where a data glossary comes in with the ability to manage your business terms and link them with your data assets. SAS Information Catalog has a glossary component that enables you to manage business terms and more importantly build connections to the actual data assets. The glossary supports a collaborative approach to managing this information and allows you to: Create and maintain and add attributes to term types Create new terms and import delimited lists of terms Establish relationships between terms and term types Review terms and term types in the Glossary window Assign terms to SAS Information Catalog assets Search for terms in the Search field A typical hierarchy of business terms in the SAS Viya Glossary looks like in the image below: With solutions like above, you can bring control and governance to your data. The key thing about having a lot of data is finding the right way to make it work for you. If you’re not a data engineer like me, and do it for the kicks, in the real world there is always a use case to implement and a business goal to accomplish. Data without access, quality and governance is just idle ones and zeroes. No matter where you collect, store, and maintain your data, you will always need tools to manage your data in a controlled and governed manner. Having established control and monitoring procedures for your data lake, there is less chance of it gradually becoming a data dump. A solid and governed data foundation lets you both sleep better at night and get faster from data to value!   Learn more about SAS and Databricks Harness the analytical power of your Databricks platform with SAS Data everywhere and anyhow! Gain insights from across the clouds with SAS Elevated efficiency and reduced cost: SAS in the era of Cloud Adoption SAS and Databricks: Your Practical Guide to Data Access and Analysis Data to Databricks? No need to recode - get your existing SAS jobs to SAS Viya in the cloud Maximize Coding and Data Freedom with SAS, Python and Databricks Data Brilliance Unleashed: SAS Data Quality against Databricks - Precision, Performance, Perfection Unlock Seamless Efficiency: SAS Viya's No-Code/Low-Code Experience to Democratize Databricks Seamless Power, Dual Brilliance: SAS Analytics and Data Management, Now Within Databricks   ... View more

Recent times have seen the rise of innovative data and analytics companies such as Databricks, who provide a cloud-based data platform built on Apache Spark with distributed data processing capabilities for data engineering, large-scale data analytics and machine learning. SAS Viya and Databricks both cater to data engineers and data scientists. The two platforms have somewhat different focuses, features, and often serve different user personas and use cases. In my opinion, what is often missed, is how well these two platforms can complement each other when working side by side in unison.   SAS Viya naturally fits together with existing SAS solutions and allows seamless integration of modern in-memory analytics into existing SAS 9 environments. It’s less commonly known that modern day SAS also provides tight integration with open-source languages and tools, such as the ability to write Python code natively in SAS Studio (the SAS developer’s IDE) making it possible to embed Python code alongside with SAS code. Databricks was built on top of Apache Spark so it’s tightly coupled with the Spark ecosystem and naturally supports several programming languages like Scala and Python.   Both SAS Viya and Databricks are very good at scalability, as both can handle large datasets and provide parallel processing. Thinking practically, majority of SAS users today have SAS Enterprise Guide as the foremost tool in their data engineering and analytics toolbox. While intuitive to use for almost any data or analytics use case, SAS Enterprise Guide, is more of a Swiss-army knife than a full-fledged analytical hammer for big data processing. SAS being a developer focused software company is striving to provide each developer persona with the optimal tool for their specific working method. It’s reassuring to know that SAS Viya will support your preferred development method, be it code, low code or no code. And anyone coming from SAS background will appreciate the fact that Almost ALL of your SAS9 code runs on SAS Viya as there are in fact two analytical processing engines, SAS Compute for backwards compatibility with SAS9 and Cloud Analytics Service (CAS) for in-memory processing. SAS Viya continues this tradition with a focus on developer-friendly analytics and model building interfaces while maintaining the power and openness of SAS for anyone developing code in SAS Studio or any of the graphical user interfaces. Giving the power in the coder’s hands follows the current trend of coding as preferred choice for many developers and as evidence of that, many like to work with Databricks using a notebook interface. Many opting for Databricks regard it well for flexibility and developer-friendliness for dedicated data scientists and analysts. To support the data scientist, Databricks supports MLlib, the machine learning library for Spark that can provide a variety of machine learning algorithms and can handle large-scale distributed machine learning tasks. Having worked with data processing architectures for some time, I know that performance comparisons always must be taken with a grain of salt. Having said that, I see SAS Viya’s cloud-native, modular, containerized architecture as both cost-effective and highly performing. Fortunately, you don’t have to rely on my word, since an independent consultancy, Futurum Group, ran a series of analytical benchmarks for SAS Viya and direct competitors in 2023 and stated on performance of SAS Viya: “SAS Viya’s efficiency and performance result in its ability to dramatically outperform competing solutions, particularly at scale with large and complex datasets.” Have a look at the full study: Performance at Scale - Comparing AI/ML Performance of SAS Viya vs. Alternatives Ultimately, the choice between SAS Viya and Databricks depends on your specific business requirements, existing infrastructure, and the preferred toolset for your analytics and machine learning tasks. I know from experience that many organizations that have used SAS for years have recently opted to have both platforms alongside, making the best of both worlds. I think SAS is very good at productionalizing analytics with a factory-like DataOps/ModelOps approach. Key part of the SAS methodology is the Analytics Life Cycle as depicted in the image below. It provides a framework for continuous development, governance, collaboration, and monitoring. This ensures that analytical models deliver reliable and accurate results throughout the model’s lifecycle. In many organizations I’ve seen data scientists on Databricks take a less structured approach of experimenting on analytics notebooks, writing code, visualizing results, and then sharing their data insights.   At SAS we see the end-to-end governed data and analytics platform as the biggest differentiator. It starts with you needing answers to your analytical questions, always involves accessing and managing data, and for the results often includes modeling and/or visualization. Finally, deployment of analytical models on your actual data provides you with the insights needed to make better decisions that will make a difference. To learn more about how the SAS approach to governed analytics provides value for your business and ultimately for your customer, have a look at what S-Bank is saying about SAS Viya: “Working with SAS provides us a more structured way of working with analytics and has helped align our processes, methods and tools”  https://www.sas.com/en_us/customers/s-bank.html ... View more

Have you ever run into the need of hiding something in your data? If yes, SAS Studio has been able to provide an easy solution to your problem since version 2023.07 with the Mask Data step. With the Mask Data step you can hide and obfuscate data in three different ways:  Masking is often used by companies to hide partial information, such as all the numbers in credit card identifier except the last four digits (XXXX XXXX XXXX 1234) Hashing is a one-way encryption method and prevents anyone from analysing the obfuscated data. This Hashing is the transformation of data into a short fixed-length value (known as a digest) that represents the original string. Hashing is used for example in database indexing due to performance gains in using the hashed key than using the original value. Hashing functions are typically used in file comparison and detection of file corruption and tampering. You just create a hash for file A and file B and then compare if the hash is identical for both. Substitution enables you to analyze obfuscated data without compromising data integrity. The custom step enables you to implement substitution with a lookup table. You can specify a key column and a value column for each column that you want to substitute. If needed, the original data can always be restored by querying the lookup table.     Mask Data step is located in the Transform Data section of SAS Studio steps:     I created an example of using the Mask Data step to explore all three variations of data obfuscation.                     Masking   There are a lot of masking definitions to choose from, and you can see them all explained here in SAS documentation:                   In my first swimlane (btw. Swimlanes are very handy to define the running order in SAS Studio) I run mask data step with a definition called Mask Partial String. This works well with number fields such as the PHONE field in my sample data and would work great also for a credit card number as it will mask all but the four last digits of a string. It is very easy to configure as seen in the picture below:                           Note that I chose to create a new column instead of replacing the existing column. When running it, the result is easy to see in the data that all but last four digits have been replaced and obfuscated.               Hashing   My second example is about hashing ie. Encrypting the data using using a one-way non-reversible cipher, such as CRC32 or SHA256.                                     In the image we select Hashing as type and then choose the CONTACT column as target. I’m running both CRC32 and SHA256 algorithms to show the difference between the two methods.                 Hashing with a more complex algorithm like SHA produces a longer hash as result that will take more space in storage. Use the one that suits your use case best.   Substitution   The third example uses substitution as method and requires a lookup table to be used for the conversion. I use a simple lookup that contains only the given name of the CONTACT thus replacing the full name with only the given name.   Substitution uses key matching to find the matching rows between source and lookup – it does not use the QKB at all. Mask data step by default only has one input port, but when linking up another table it will add a separate port for the lookup table. If you want to make sure that you got the correct port you can use the Expand feature to actually show the port names.   Substitution requires three columns to be mapped to the masking step. First two are from the lookup table: 1) Key column is the one that matches your target data and 2) Value column is the value that will substitute the original value. Third column Substitute obviously is the target column that will be substituted with the lookup value.                                                   Note that here I’m ok with replacing the original value with the substitute. As we want to remove some information we only keep the first name of the CONTACT:                                   I understand that these are not overly complicated operations to achieve by writing code and using SAS functions. I personally like the Mask Data step for the ease of use and the fact that it offers so many variations of data masking. By using the Mask Data step, Santa’s secrets will be safe… at least until the big night 😄 ... View more

You may have heard of SAS Information Catalog. The cool data discovery and profiling tool in SAS Viya that provides agent-based metadata crawling with an easy search interface. It is accessible right from the ‘hamburger menu’ under ‘Discover Information Assets’.           In this juletip I will focus on the search functionality that is in many ways obvious but contains some hidden gems. All the examples here were tested with SAS Viya version Stable 2022.09. With SAS Viya monthly release cycle, there probably are even more features in your version if you are reading this later on! I promise to keep this one short, so you won’t be late for holidays 😊   If you have not logged in to SAS Information Catalog before, I’m sure you’ll appreciate the asset dashboard that provides an overview of ALL the assets that have been discovered in your SAS Viya estate:         At least I love being able to see right away what is available in my SAS Viya environment on one glance. The indicators allow drill-thru with a mouse click as well!                     For a data nerd like myself a summary across all catalogued data is so cool to have! And from here you can drill-thru further on to see all the tables from a specific type category! Assuming that your SAS administrator has already created some data agents to crawl your data libraries you will have an easy way to search the gathered metadata for content with the free text search tool that SAS Information Catalog provides.           Temptingly it’s asking you… ‘What assets are you looking for?’. Go ahead… type in a search term! Note that since it’s an Information catalog you can search for any type of asset, not just your tables and datasets. For example, SAS Studio flows, analytical models, decision models, rule sets etc. are all included as searchable assets.   Free-text search is very simple, just type in any keyword. While it’s easy to browse and see if any of the assets was what I was searching for, it helps to edit the columns in ‘Manage Columns’ to show the information that you’re most comfortable working with. It’s a small icon easy to miss, but it looks like this:               I’m an old school file explorer -minded personality, so I always prefer to see at least the name, library, extension, asset type, size and rows for my files:               Just to show you an example, how well this worked out in my case - I got exactly the kind of output I was hoping for! (only few first lines are shown):                 Sorted by size descending, this gives me a good overview of the largest datasets available corresponding with my chosen search term. Apart from data sizes, it’s interesting to see that I have both In-Memory tables and good old sas7bdat files in my catalog.   While this is good, sometimes you might want to do more specific search. In SAS Information Catalog this is called faceted search. Often you want to filter your search based on some attribute like file size or library and faceted search is built just for that purpose. Just clicking on the search box brings the basic facets on display:           There is however, a long list of available search facets and clicking on help will bring you to the SAS Information Catalog User Documentation. I’m giving here simple examples of some facets that I have found useful:   Data size is always of interest, for example to find tables over 20GB use (in bytes, sorry about the zeroes): DatasetSize:>20000000000   If you need to know what tables contain less than 100 rows use: RowCount: <100   If you need to find a range between numbers, this is the way: RowCount: [100<200]   If you already know what specific library contains your assets, you can search for a specific library like ‘sashelp’: Library.name: sashelp   Data privacy regulation makes it important to know which tables contain personal data: Column.informationPrivacy: private Tag ‘private’ helps you find all the tables that have been tagged as sensitive:       You might want to know where all your sas7bdat datasets are: FileExtension: sas7bdat   If you have been away las week and want to catch up of the latest stuff, use: DateModified: "Previous week"   To find all of my own creations I would use: CreatedBy: "Jarno Lindqvist" And if you only want to see a certain resource type, you can search for example all SAS Studio flows, use: AssetType: dataflow Again, check the SAS Information Catalog User Documentation for all the facets and more examples on what keywords can be used. When you drill down to a specific table from your search results, you sometimes get the following message:       This means that that the table has been updated after the latest analysis by a SAS Information Catalog agent. No need to sweat though, under the ‘Actions’ button you can run an on-demand analysis for that particular data:                   Also note that there is a history for the analyses. By collection history from recurring runs SAS Information Catalog can build a trend on the data quality indicators. Under the Action button you of course have the options of continuing work with that data in other SAS Viya applications, for example entering Lineage view that provides an overview of relationships with other linked assets.   Not wanting to spoil anything but after finding your data, there are many other cool things that you can do with data using SAS Information Catalog. I’ll give one more tip though. Defining data ownership has value in any data governance endeavor. In SAS Information Catalog you can now easily do this by assigning responsible contacts for your data:           But I promised to keep this Juletip short(ish), so I’m saving further goodies for my next blog post. May your holidays be happy and your data always at the tip of your hand! 🎅 ... View more

There is an old saying among analysts: garbage in, garbage out. It means that you can have the best model in the world, and the most perfect analytical process—but if the quality of your data is poor, your results are likely to be rubbish. This is widely known. It is even widely accepted. The chief analyst of a major financial organization once told me they spend up to 90% of their time in preparing data and solving quality issues! Think about what that means for analyst productivity. We also often find that many people simply assume that their data quality is good, without ever asking the right questions. This is probably not wholly unreasonable since non-analysts want to simply get the right data and go. They don’t want to be worrying about data quality, or any of those other data preparation steps that aren’t part of their motivation or skill set. Fortunately, with SAS Viya - like many other SAS products - it is possible to take data quality almost for granted. There are several reasons for that, and you really need to know about them.     1. SAS Viya incorporates and enhances many of the great data quality features that were available in SAS9 About 20 years ago, SAS acquired DataFlux, which at that time was a market leader in data quality. DataFlux’s quality algorithms were gradually incorporated into SAS9 Data Management solutions taking the platform very capable in from data quality perspective. When we started visioning the Viya platform, we knew that we couldn’t simply import the data quality framework from SAS 9 as it is, as the architecture was fundamentally different. However, we kept focus on embedding the data quality knowhow, reliable algorithms, and proven ways of working with data quality in SAS Viya.   2. There are lots of different ways to manage data quality in SAS Viya SAS Viya has a number of tools and options for managing data quality. For example, SAS Data Studio has many data quality transformations. These include standardization, parsing, and duplicate record removal. There are even AI-based data quality suggestions that can help you to detect points of quality improvement. SAS is always transparent so data quality actions can be programmatically called from SAS code. All these data quality features are based on the SAS Quality Knowledge Base: an agreed repository of definitions for data quality. Unlike SAS9, all language locales are all included in Viya, so there is no need to pick and choose between them.   3. Data quality is now being introduced into SAS Studio Flow via data quality custom steps Now that SAS Viya is moving towards SAS Studio for developers, the data quality features are gradually being introduced into Studio as well. This is happening via data quality custom steps, which are add-on capabilities that are sourced either direct from SAS, or via the SAS community. The first custom steps will have added enrichment capabilities. They include Verify & Geocode Addresses, Verify Phone Numbers and Verify Email addresses. Survivorship and Entity Resolution are in the pipeline.   4. SAS Information Catalog makes it possible to search for data across sources One of the issues with data quality is being able to obtain and bring together data from multiple sources, especially heterogenous data sources such as databases and cloud data storage. The challenge is to find the data you want and need, especially if you don’t know exactly what data you are looking for, or where it is stored. The SAS Information Catalog, embedded into SAS Viya 4, makes this easy. It is based on data agents that extract metadata at regular intervals and provides a data content and quality overview with descriptive measures and data content driven graphs. It behaves a bit like a Google search to your own data and enables you to easily find all possible tables related to your search string, in prioritized order by ‘best match’.   5. SAS Lineage provides an overview of data items to show you connections between them Lineage helps you understand the origins of data, and the relationships with other data objects. When you open up the tool from one data item, be it a table, data job or model, you can see all its connections. This helps you to understand the interconnections and dependencies in your data. This is important, because it shows you how changes in one piece of data or table can affect others, both upstream and downstream. It therefore helps you to understand the data ‘big picture’ more clearly. This was a very brief introduction to the SAS data quality tools available in SAS Viya. You can find out more about the SAS approach to data quality here. ... View more

In SAS Studio on SAS Viya there are many ready-made steps at your disposal. There are times however, when the existing steps don’t cover your needs. Starting from Viya 2021 there is a possibility to create custom steps. If you’re a seasoned DI Studio user, think of this as a new way to create user-written transforms. Steps that are user built to cover a specific need. They are a great way to bring more power and capabilities to SAS Studio Flows. There a great example of creating a new custom step here: SAS Demo | Create a Custom Step in SAS Studio on SAS Viya    I was thinking of a suitable example and figured that many times there is need for describing a table structure. In SAS there is the good old PROC CONTENTS that tells many things about a table. In Python Pandas library there is df.info() function that does similar things to a dataframe, look here: Pandas DataFrame: info() function    So I got the idea of creating a custom step that allows user to connect a table and run either PROC CONTENTS or Pandas df.info to get information about table structure. First I opened up the dialogue to create a new custom step:                 Selecting “Custom step quick start” opens the custom step designer. There are three different tabs: Designer allows to create a UI layout for the new step and define names and labels to UI components Prompt UI tab creates a .json in the background while you add components to UI Program tab is where you put the actual code that your custom step will run I added to UI components to my designer page. Input table obviously allows you to connect a source table to your custom step. Note that it will not show in the actual step, when you use it. It only provides an input port to your custom step. The second component I added is the drop down list. That is also self-explanatory, you can add as many selections as needed and they will appear in custom steps options page when you use it.   I am adding two options to my drop-down list. First selection is “PROC_CONTENTS” that will call the code that runs the SAS PROC. Note that I have used an underscore, best to use proper variable names since these are the actual ones that are used in the code. My second selection is “Pandas_info”. This will run the Python code that run the df.info() function:                     I’m happy with these two UI components, they’re all that I need to get my describe custom step done. Now I can take a look at the .json code that was created behind the scenes while I added my components. I omitted the start and ending but you can see the references to the two UI components that we created in the .json code:                       Next step is to create the actual program code that will match with the variables provided by the UI. In this example we only use 2 variables: InputTable: this references the source table that is mapped to the input port InputType: this references one of the two possible selections from the drop-down menu The code below is simple but does all that we need for this example. It’s a macro called “describeData” that will choose either of the if-then code blocks and run the reference part of code. %MACRO describeData; %IF &inputType EQ PROC_Contents %THEN %DO; proc contents data=&inputTable; run; %END; %IF &inputType EQ Pandas_info %THEN %DO; PROC PYTHON infile="/srv/nfs/kubedata/compute-landingzone/ssfjal/myscript.py"; quit; %END; %MEND; %describeData; First IF statement runs when &inputType equals PROC_Contents and uses &inputTable as data. Second IF statement run when &inputType equals Pandas_info and uses the same &inputTable variable.   This uses the PROC PYTHON procedure where you can place Python code between submit; and endsubmit; statements. I tried of course to inset my code here but got the error message that the submit block cannot be placed inside a SAS macro. So I resorted to the infile statement and placed the actual submit block inside myscript.py file that is accessible from a path in the filesystem. This worked exactly as I wanted. You can read more about this in the PROC PYTHON documentation: PYTHON Procedure   Here’s the actual Python code submit block: inputTable1 = SAS.symget('inputTable') df=SAS.sd2df(inputTable1) print(df.info(verbose=True)) I noticed that the macro variable is not directly visible to PROC PYTHON so I needed to define variable inputTable1 and use SAS.symget function to transfer it over to Python. The next line uses SAS.sd2df function to copy the SAS table into a Python dataframe. Then I call the Python df.info function to provide a description of the table.   Now that we have finished creating our new custom step it’s time to save it as “Describe Data” and give it a spin! I insert the custom step on the workflow canvas and use the good old class dataset as source:                   It’s very simple, just connect source table, select either PROC_Contents or Pandas_info and run!   The result form SAS PROC CONTENTS is exactly what you would expect. Here’s the output from the Pandas df.info() function: <class 'pandas.core.frame.DataFrame'> RangeIndex: 19 entries, 0 to 18 Data columns (total 5 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 Name 19 non-null object 1 Sex 19 non-null object 2 Age 19 non-null float64 3 Height 19 non-null float64 4 Weight 19 non-null float64 dtypes: float64(3), object(2) memory usage: 888.0+ bytes None >>> While this simple example may be limited in its applicability, I hope it shows how easy it is to create a custom step in SAS Studio! And the best thing is, if you place your custom step in the public folder, other users in your Viya platform can use and run it and even develop it further! So simple that Santa can do it while preparing for the big day! 🎅 ... View more

Data profiling is a great method for understanding new data. In both SAS 9 and Viya, there are GUI tools for evaluating data and creating basic data metrics such as missing values, uniqueness, minimum, maximum, average, frequencies and patterns. When deploying things to production, running data profiling in code makes it simple to schedule a regularly running data profile. Before SAS Viya 3.5 there has not been an easy way to accomplish this. Enter PROC DATAMETRICS that makes it simple to run a profile on a full table or selected columns. See SAS documentation for PROC DATAMETRICS here: PROC DATAMETRICS documentation   PROC DATAMETRICS produces an output table with RowID column that references to the chart in the above link, so you will not find descriptions in the output table. What I did was import the descriptions to a SAS table PROC_DATAMETRICS_DICTIONARY as seen in the picture below and then joined it with PROC SQL with the metrics output table using RowID.   To save you the trouble of importing the descriptions, you can download at the end of this post 😉   I made a very simple example on SAS Viya 3.5 using the simplest sample data that is provided with SAS, the famous CLASS-dataset. I have found out it’s best not to use too many columns with PROC DATAMETRICS to keep it understandable. In my example I’m using only three columns: Name, Sex and Age. Also, to group metrics per column, I’m sorting the output first by ColID, RowId and Count. This results in a bit easier to read output table.   Here is the full SAS code that I used:   CAS; CASLIB _ALL_ ASSIGN; PROC DATAMETRICS DATA=CASUSER.CLASS out=CASUSER.PROFILE_METRICS; IDENTITIES qkb='/opt/sas/spre/home/share/refdata/qkb/ci/31' locale='ENUSA' def='Field Content'; VARIABLES Name Sex Age; RUN; PROC SQL; CREATE TABLE WORK.PROFILE_METRICS_JOINED AS SELECT t1.COLID, t2.'RowID'n, t1.'COUNT'n, t2.Description, t1.CHARVALUE, t1.DOUBLEVALUE FROM CASUSER.PROFILE_METRICS t1 INNER JOIN CASUSER.PROC_DATAMETRICS_DICTIONARY t2 ON (t1.'ROWID'n = t2.'RowID'n) ORDER BY t1.COLID, t2.'RowID'n, t1.'COUNT'n DESC; QUIT; RUN; Here is an example of the output data:   Note that in the picture the COLID is 2, so this refers to the second column, Age, in my source data. Frequencies for Age column are depicted here, but the full data contains all the other metrics for selected columns as well.   Have fun with PROC DATAMETRICS and please share if you find it useful with your own data! 😊   As promised, here’s the PROC DATAMETRICS definitions in csv-format which you can easily join with your output data using RowID as join key:   RowID;Description 0;Number Of Columns 1;Number Of Rows 3;Columns In Data Source 4;Date Created 5;Date Modified 6;Encoding 7;Compressed 10;View 11;Lib 12;Source 999;Format Name 1000;Column Name 1001;Data Type 1002;Count Of Values 1003;Name Of The Column Containing The Profiled Column Values 1004;Frequency Distribution (High) 1005;Frequency Distribution (Low) 1006;Maximum Value 1007;Maximum Length 1008;Mean 1009;Median 1010;Minimum Value 1011;Minimum Length 1012;Mode 1013;Number Of Blank Values 1014;Null Count 1016;Outlier High Values 1017;Outlier Low Values 1018;Pattern Frequency Distribution (High) 1019;Pattern Frequency Distribution (Low) 1020;Unique Patterns 1021;Primary Key Candidate 1022;Standard Deviation 1023;Standard Error 1025;Unique Count 1028;Id Analysis 1029;Actual Bool 1030;Actual Date 1031;Actual Integer 1032;Actual Real 1033;Actual String 1034;Actual Designated 1035;Determined Type % 1037;Data Length 1038;Ordinal 1039;Number Of Decimal Places Specified In Format 1040;Null Percent 1041;Unique Percent 1042;Pattern Unique Percent 1043;Blank Percent 1100;Error Message 1101;Metadata Allows Nulls ... View more

A customer recently asked how they could provide SAS datasets to their R and R Studio users. Remembering they already have SAS Federation Server licensed and in use, the ODBC driver for Federation Server could make this possible.   So, to help my customer I started up a test environment to investigate this. Federation Server was already up and running so didn't need to do any configuration server-side. Also the Federation Server driver for ODBC was installed in my Windows environment. If you don't have it installed, contact your SAS administrator for advice how to get it installed. It should be part of the SAS Federation Server software depot.   The windows client did not yet have the Federation Server data source defined, so I looked into the ODBC DSN administration panel.   IMPORTANT: In Windows there is the 32bit and 64bit DSN administration panel, so if you plan to run 64bit R, then use the 64bit ODBC admin panel, like I did.     Choose a descriptive name for the new data source, I used "FEDODBC" in this example. Make sure to enter correct server hostname and port for your Federation Server. And check in your Federation Server administration console for the correct Fed Server DSN to use. I use "Company" in this example.   Then you can click "Test Connection":   I didn't have R and R Studio installed, so I went to cran for R: https://cran.r-project.org/bin/windows/   And to rstudio.com for R Studio: https://rstudio.com/products/rstudio/download/     Installer will as if you want both 32bit or 64bit R Studio installed, I only installed the 64bit binaries.   When you start R Studio the first thing you'll need is the ODBC package.   Fortunately it's simple to add packages in R, I used the following command:   # Install the latest odbc release from CRAN: > install.packages("odbc")   This started the download of R tools first and then proceeded to load the odbc package (some parts of log have been omitted)   WARNING: Rtools is required to build R packages but is not currently installed. Please download and install the appropriate version of Rtools before proceeding: https://cran.rstudio.com/bin/windows/Rtools/ also installing the dependencies ‘ellipsis’, ‘digest’, ‘glue’, ‘bit’, ‘vctrs’, ‘pkgconfig’, ‘bit64’, ‘blob’, ‘DBI’, ‘hms’, ‘rlang’, ‘Rcpp’, ‘BH’ There is a binary version available but the source version is later: binary source needs_compilation rlang 0.4.5 0.4.6 TRUE Binaries will be installed . . . trying URL 'https://cran.rstudio.com/bin/windows/contrib/4.0/odbc_1.2.2.zip' Content type 'application/zip' length 1297511 bytes (1.2 MB) downloaded 1.2 MB package ‘odbc’ successfully unpacked and MD5 sums checked The downloaded binary packages are in C:\Users\sas\AppData\Local\Temp\2\Rtmps50ErW\downloaded_packages   Looking at the log, odbc packege was now installed.   Then I proceeded to R Studio Connections panel:     There I can see my "FEDODBC" data source readily available as we already configured that on the Windows side as the first step. Opening that I can see the configuration for that data source:   There you can see the connection string for the FEDODBC connection:   library(DBI) con <- dbConnect(odbc::odbc(), "FEDODBC", timeout = 10) Clicking on "Test" we see the following:     Success! So we know the connection from R Studio to the ODBC client works and we can open up a data source on the Federation Server:     I opened a test table named "ORION_PROFIT" under the data source Company/SASData/Finance and I can readily see the data in the R Studio data panel. Your path and data are of course what you have defined in your Federation Server administration console.   That's all! It really is this easy 👍   Let me know if you end up trying this in the comments below. As a SAS employee I of course recommend the use of SAS for all your analytics needs, but realize the R is very popular in many organizations and often the need to make use of SAS datasets in R comes up. There are other way to share data between these tools but this is by far the easiest way if you already have SAS Federation Server available.                           ... View more

A SAS customer recently approached me with a data management question. They are using SAS Viya among many other popular commercial and open-source data science and reporting tools. The question was in the line of:   “We have spent a lot of time and effort preparing good quality data sets on SAS Viya. At the same time, we have a open source developers and reporting users who duplicate the effort of creating very similar data sets with their tools. Is there any common API to share this data between the platforms?”   Knowing they are open to the concept of APIs, I started thinking SAS Viya offers a rich set of functionality via it's REST APIs and that would easily fulfill the need for one common API. In SAS Viya there are dedicated APIs for R and Python available, but use their bespoke integration libraries called SWAT (SAS Scripting Wrapper for Analytics Transfer). However, using a REST API would answer their need for sharing their data in a common format, which typically is JSON.   If a REST API is not open to the whole wide world, it requires authentication before allowing user to perform any operations. In SAS Viya's case this involves a need to 1) register the client and 2) obtain an access token. Best way for me to learn is to follow a good example, so I turned to this excellent SAS blog on authentication.   The details are all there, so I'm not deep diving there in this blog. After mastering those steps, you are ready to make your SAS Viya Cloud Analytic Services (CAS) API calls. I used cURL for experimenting with the REST API. There are many other API testing tools as well, such as Postman that seems more user friendly and is likely to save some of the copy-paste work compared to working with cURL from the command line.   Getting back to the original question on how my customer was able to share their data, I investigated the long list of available CAS actions. As I needed one action that simply returns all the rows from a given table in JSON format, a SAS Viya CAS action called table.fetch was the a match. If you’re new to SAS Viya, a CAS action is the smallest unit of work for the CAS server. Actions can load data, transform data, compute statistics, perform analytics, create output and many other purposes. You configure each action by specifying a set of input parameters.     Before I can call my CAS action, I need to create a CAS session and know the session ID. As it happens, there exists a superb SAS blog describing the needed steps.   To make things short, there are two steps involved:   Create a CAS session, I use <mytoken> to refer to the access token acquired in the previous step:   curl -X POST http://yourserver:8777/cas/sessions \ -H 'Authorization: Bearer <mytoken>'          This will return the session ID that I'm calling <mysession> in the next step. It should look like:   { "session": "16dd9ee7-3189-1e40-8ba7-934a4a257fd7" }     Fetch the table:        curl --data-binary "@body.json" -X POST http://yourserver:8777/cas/<mysession>/actions/table.fetch/ \ -H "Authorization: Bearer <mytoken>" \ -H "Accept: application/json" \ -H "Content-Type: application/json"     I used an external JSON file I named body.json to describe the payload i.e. the parameters to provide for the table.fetch action.   My body.json contains only the parameters "table", "to" and "maxRows":      { "table":"<tablename>", "to":"<last row to return>", "maxRows":"<maximum number of returned rows>" }     The REST APIs and CAS REST APIs in SAS Viya are very powerful for extending analytics services and data to external applications. In my opinion, re-usability is the key: create once and call from whatever application you need. In my example, the customer already encountered the trouble of perfecting their data once, so it makes good sense to extend this data for use with other applications where needed.   I recommend checking out what REST APIs on SAS Viya can offer you. You might be surprised how easy it is once you past the somewhat complex looking syntax of REST calls. ... View more