We've posted a few times about referencing files directly from CAS actions in CASL code (1,2,3). It's a great way to utilize CAS action functionality in programs without explicitly loading tables to CAS. The referenced file is transiently loaded to CAS and dropped immediately after the requested CAS action is run. For context, when referencing files directly, the CASL code looks like this:   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.     As you can see, the simple.Summary action references the SASHDAT file directly instead of an explicitly loaded CAS table. SAS7BDAT, CSV, XLSX, and parquet files can also be referenced in this way. Referencing files directly is great for traditional programming where we don't need tables loaded for extended ad hoc analysis and/or reporting. However, there is a more efficient way for most scenarios, setting singlepass=true. With singlepass=true, the files aren't loaded in whole. Their data are read by block as the CAS action requires and are released immediately. This generally makes for faster processing.     For our 500,000 row, 24 column SASHDAT table, setting singlepass=true reduces our total execution time from 2.56 to 1.55 seconds. Examining the log, we see that while setting singlepass=true increased CPU on the summary action, real time for both the load and the summary decreased.         The same pattern exists for the other file types, SAS7BDAT, CSV, XLSX, and parquet. CAS CPU on the summary action increases but overall performance for the action improves.   As the name implies, "singlepass=true" can only be used on CAS actions that perform their processing with a single pass of the input data. Actions that require multiple passes will error as shown below.       To see if a CAS action is eligible for the singlepass=true option setting, check the doc. For example, the option is available for the append action but is not available for the loadTable action.    Find more articles from SAS Global Enablement and Learning here. ... View more

As with most things these days, you have two options when it comes to processing parquet files, the CAS server and the traditional SAS engine now called "Compute." Let's see how we use both and any special considerations we should take into account in choosing one over the other.   Performance Comparison -- Data Summarization   Examining basic data summarization, we see both engines performing exceptionally well and almost identically. Operating on the 5 million row, 26 column order_fact table parquet file both engines summarized 2 columns by 1 column in about 2 seconds. Performance drops modestly for both as variables are added to the analyses. Summarizing 4 columns by 4 columns takes about 5 seconds.   CAS' performance might surprise some here because of its need to load data prior to processing. However, as we can see below, the simple.Summary action references the parquet file directly. This triggers the parquet file to be loaded "transiently" into memory in its native parquet format and only the necessary fields are loaded. There is no expansion to the SASHDAT format.   CAS Code -- Simple.Summary     Base SAS Code -- PROC MEANS   libname pqtdtm parquet "/gelcontent/data/AppalachianChocolate_datamart" ; proc means data=pqtdtm.order_fact noprint; var total_line_item_sale_amt item_qty; class order_channel; output out=summaryMC sum(total_line_item_sale_amt)= sum(item_qty)=; run;   Load Time and Size Comparison   Next we'll look at simply loading the parquet file to each server's native format, SAS7BDAT for Compute and DVR SASHDAT for CAS. We do this since we'll seldom process third party data in one step. Typically we augment it, enhance it, and transform it. This will typically require the data be transformed into the native format at some point. Additionally, certain CAS actions like DATA Step and FedSQL can not reference parquet files directly. So parquet files need to be loaded for their use.   CAS loads the 5 million row order_fact parquet file in ~15 seconds to DVR CAS table format which takes 0.44GB of space; Compute (base SAS) loads it in ~13 seconds to SAS7BDAT which takes 1GB of space.   Conclusion   Both Compute and CAS read and process parquet files extremely efficiently. When it comes time to load the parquet data into CAS or SAS format for more complex processing however, CAS can store it more efficiently with DVR and can generally process it more efficiently as well.   Code Used for this Analysis   *Create a large parquet table; libname sasdtm "/gelcontent/data/AppalachianChocolate_datamart" ; libname pqtdtm parquet "/gelcontent/data/AppalachianChocolate_datamart" ; data order_fact; set sasdtm.order_fact; run; data customer_dim; set sasdtm.customer_dim; run; data pqtdtm.order_fact ; set order_fact order_fact order_fact order_fact order_fact order_fact order_fact order_fact order_fact order_fact ; run ; data pqtdtm.customer_dim; set customer_dim; run; *Process the large parquet table with SAS; *2 var, one class; proc means data=pqtdtm.order_fact noprint; var total_line_item_sale_amt item_qty; class order_channel; output out=summaryMC sum(total_line_item_sale_amt)= sum(item_qty)=; run; *4 var, 4 class; proc means data=pqtdtm.order_fact noprint; var total_line_item_sale_amt item_qty line_item_discount_amt list_price_amt; class order_channel order_type retail_transaction_type_cd store_geo_id; output out=summaryMC sum(total_line_item_sale_amt)= sum(item_qty)= sum(line_item_discount_amt)= sum(list_price_amt)=; run; proc datasets lib=work; run; *Process the large parquet table with CAS; cas mysession sessopts=(metrics=true); caslib _all_ assign; caslib dmAppChoc path="/gelcontent/data/AppalachianChocolate_datamart"; *2 var, 1 class; proc cas ; simple.summary result=r status=s / inputs={"total_line_item_sale_amt","item_qty"}, subSet={"SUM"}, table={ name="order_fact.parquet" caslib="dmAppChoc" groupBy={"order_channel"} }, casout={name="summaryMC", replace=True, replication=0} ; quit ; * 4 var, 4 class; proc cas ; simple.summary result=r status=s / inputs={"total_line_item_sale_amt","item_qty", "line_item_discount_amt","list_price_amt"}, subSet={"SUM"}, table={ name="order_fact.parquet" caslib="dmAppChoc" groupBy={"order_channel" "order_type" "retail_transaction_type_cd" "store_geo_id"} }, casout={name="summaryMC", replace=True, replication=0} ; quit ; *Load Test; proc cas; table.loadtable caslib="dmAppChoc" path="order_fact.parquet" casout={caslib="dmAppChoc" name="order_fact" replication=0}; quit; proc cas; table.tabledetails caslib="dmAppChoc" name="order_fact" level="sum"; quit; data sasdm.order_fact; set pqtdtm.order_fact; run; proc datasets lib=sasdm; run; proc contents data=sasdm.order_fact; run; cas mysession terminate;   Find more articles from SAS Global Enablement and Learning here. ... View more

Let's look at the different processing engines in the SAS portfolio that perform the joins, aggregations, lookups, analytics, and other data processing behind the code and visual interfaces. Of course, "engine" is also a loaded term so here we'll take it in the broadest sense. . SAS 9   The term, "SAS 9," is generally used to refer to Base SAS and its many extensions, SAS/CONNECT, SAS/ACCESS, SAS/STAT, IML, SAS/GRAPH, etc.. All Together, these components are formally known as the SAS Compute Server and form one of the most used data processing and analytics platforms in the world as well as the back-end for many of SAS' visual interfaces including:   Enterprise Miner DI Studio Enterprise Guide   The SAS processing engine has the following distinguishing characteristics:   SMP/Single Threaded Engine with Multi-Engine Scaling Many of SAS PROCs support multi-threading, however, certain analyses concerned with row order will single thread. To improve vertical scaling as well as offer horizontal scaling SAS/CONNECT and/or SAS Grid can spin up additional SAS 9 instances to spread the load. Row Processing, SQL, PROCs While SAS does support two implementations of SQL, PROC SQL and PROC FEDSQL, one of SAS' strengths is its unique and flexible implementation of row processing (DATA Step) as well as its library of data transformation and analysis procedures (PROCs). Batch, Web Service, Back-End Service, ... While originally written as a batch engine, SAS has been extended to support every imaginable usage pattern including web services, client server, and even for transaction processing. Regardless of how it's called, its strengths are in bulk processing. Some Dynamic I/O Capabilities While SAS is primarily designed to read/write static data like files and database tables, it also has some abilities to handle dynamic sources/targets like pipes, message queues, sockets, and more. Big Data Capable  With or without its Grid extensions, the SAS 9 engine can handle truly massive data loads   CAS   CAS (Cloud Analytic Server) is the "next generation" SAS processing engine. While CAS functionality already goes beyond SAS 9 in many ways (image processing as an example), CAS is also meant to offer all the functionality of SAS 9 and more but on a modern, open, high performance, multi-machine, massively parallel processing architecture. CAS is multi-threaded by design and scales processing over multiple machines automatically. Designed to be cloud native, CAS can add or remove resources as needed. CAS forms the back-end for all of the Viya applications including:   Visual Analytics Data Prep Model Studio   The CAS processing engine has the following distinguishing characteristics:   MPP Engine CAS is massively parallel by design. It automatically scales both vertically and horizontally to optimally utilize hardware resources when solving analytics and performing data transformation. SAS + more Built on a paradigm of fine grained "CAS Actions" that do single, specific operations like aggregate data or run a regression, CAS offers most of the functionality of the SAS engine plus more. Where CAS does not offer the specific functionality that SAS does, there is generally a way to get the equivalent result with CAS. Viya also includes a SAS9 compute server to complement CAS where necessary. CAS offers a massively parallel version of DATA Step, its own library of procedures, as well as a programming language, CASL. Back-End Service Unlike SAS, CAS was built from the ground-up as a back-end Viya service. As such, it offers a more robust platform for integration with client applications. CAS offers numerous APIs that allow for integration of CAS processing into SAS applications, web applications, java, etc.. Static I/O While CAS continues to get enhancements like pass-through SQL and SingleStore integration which enhance its dynamic I/O capabilities, CAS' data connector model offers less dynamic I/O options than SAS. CAS' usage model is generally: 1. Load data. 2. Process data. Big Data Focused With its massively parallel design, elastic capabilities, and optimized processing algorithms, CAS is truly a platform for big data analytics and data transformation.   ESP   SAS Event Stream Processing transforms raw event streams (e.g. stock trades) into useful output event streams (e.g. stocks you should buy) in real time as the events come in. ESP is not meant to process a big data set and then return a result. It constantly reads input streams and streams results as it processes. So, while it can handle big data, it does so record by record (event by event).   The ESP processing engine has the following distinguishing characteristics:   SMP Engine with multi-engine scaling ESP is multi-threaded at its "project" (~program) level and an ESP server can run multiple projects. Thus vertical scaling is possible at the project level, across multiple projects, or by running multiple ESP servers. Horizontal scaling is possible using the ESP router or the ESP Cluster Manager to distribute events to different ESP engines on different machines. ESP can even be deployed en mass on edge devices. Transformation and Analytics Windows ESP offers a rich set of "windows" that offer everything from basic computations to pattern analysis, geofencing, and high-end analytics. Back-End Service ESP can either be deployed as a stand-alone server or via containers. ESP can also be called from the command line but this is mostly to support development and testing. Dynamic I/O ESP is designed specifically to read dynamic sources and write dynamic outputs via a rich set of connectors and adapters. However, for maximum integration, ESP does interface with some static sources and targets like HDFS and SASHDAT. Low Latency, High Throughput While SAS and CAS are primarily focused on processing static inputs and producing static results, ESP is focused on reading, processing, and streaming results as fast as possible as input events stream in.   . MAS   When deployed outside of ESP, the SAS Micro Analytic Service is a web application that hosts REST predictive modeling and decisioning web services, called "MAS modules." As REST services, MAS modules can be easily integrated into web applications. For example, you might create a module to calculate a customer's life time value and integrate it into a call center application. So, while SAS and CAS are focused on bulk processing and ESP is focused on streaming, MAS is focused on returning small result sets from small input sets (e.g. LTV for a customer, weather for a zip code, etc.) quickly.   The MAS processing engine has the following distinguishing characteristics:   SMP Engine MAS is multi-threaded by design and scales vertically to handle numerous calls in parallel. Horizontal scaling is possible by deploying mulitple MAS services while modules can be moved between MAS instances via the Viya transfer service. DS2 + Python with hierarchical data support The MAS offers thread programming via SAS' Data Step 2 language (PROC DS2) as well as Python for high-performance transaction processing in real time. While SAS and ESP both contain mechanisms to parse and construct hierarchical data structures, MAS contains considerable capabilities for reading and writing the hierarchical data that is native to the REST paradigm. Back-End Service The MAS is a Viya micro service. POST request and response MAS is designed for real time I/O but in a different paradigm than ESP. Instead of inputs streaming in and outputs streaming out, Requests messages are posted to the MAS which returns response messages. Low Latency "Transactions" Like ESP, MAS is focused on returning results in real time. However, instead of reading an event stream, MAS responds to individual requests with individual responses.   All the Rest   Do you think that's a lot? Well there's more. We haven't even discussed DM Server, In-Database, Federation Server, LASR, and others. Data Management server was gained through the acquisition of DataFlux and has many of the same features as SAS as well as its own distinguishing functionality. Federation Server presents multiple data sources as a single seamless data model while adding an abstraction layer for additional security and obfuscation. In-database embeds SAS functionality into or alongside 3rd party data servers via a few different mechanisms to minimize data movement. LASR is, of course, the predecessor to CAS in many ways. The Broader Picture   All of these engines transform data -- turning observations into statistics, blending disparate data sources into integrated views, etc. -- but they are each focused on specific use cases. All together they offer complete data transformation and analytics coverage from the desktop level to big data, from real time streaming to real time services. . ... View more

On my last post, a commenter asked about performing a left join with CAS versus foundation SAS ("Compute"). So I figured I'd give it a shot. I used the Orion Star's Order Fact and Customer Dim tables for the join and manipulated the data to achieve various record counts. The code is included below so you can see the actual tests. The results are summarized in the following table. CAS vs Compute (Foundation SAS) Left Join Execution Time Order Fact Row Count Customer Dim Row Count CAS Foundation 96,118,569 9,085,354 15.2 06:28.0 238,868,919 22,578,454 38.3 08:18.4 476,786,169 45,066,954 01:49.1 21:03.2 714,703,419 67,555,454 08:40.4 ****** 952,620,669 90,043,954 11:35.4 ****** 1,190,537,919 112,532,454 15:06.6 ******   ******SAS Foundation ran out of space on the test since it is limited to a single server.   Note the following: For CAS, we used a hash object which was shown to be the fastest left join CAS method previously. For Foundation SAS (aka "Compute"), we used a Proc SQL Left Join. The hash object was preferred but failed due to physical server limitations at low volumes. Likewise a DATA step MERGE approach also ran out of space rather quickly because of the need to sort the data which temporarily creates two copies doubling the space required. CAS easily outperformed foundation SAS in every data volume tested. CAS was able to process much larger data volumes due to its multi-server nature. Compute was unable to process the equivalent data volumes simply because they wouldn't fit on a single server.   There was also a concern about CAS potentially thrashing at data volumes higher than available memory. I explored this a bit and didn't see much evidence of thrashing. For example in final test, the combined size of the order fact and customer dim tables was 264GB which is well over the combined available memory for the three worker nodes, 186GB. Despite this the performance curve seems more or less linear -- 714M rows at 8:40, 952M rows at 11:35, and 1.19B rows at 15:06.   Want to see the code?   If you want to the code from these tests so you can try something similar in your environment, use the code provided below.  You'll have to customize it to fit your data but it should give you a huge headstart.   CAS Left Join Test code:   options cashost="sas-cas-server-default-client" casport=5570; CAS mySession SESSOPTS=(metrics=true); CASLIB _all_ assign; libname geldat cvp "/gelcontent/data/OrionStarData/orstar"; libname sasdm "/gelcontent/data"; /* Remove the formats and the Ceda*/ data sasdm.custDimUTF; format Customer_Country $4. Customer_Gender $2.; set geldat.customer_dim; run; data sasdm.orderFactUTF; format Order_Type 12.; set geldat.order_fact; run; /* Load the tables to CAS */ proc casutil ; load casdata="orderFactUTF.sas7bdat" incaslib="dm" casout="orderFactUTF" outcaslib="dm" copies=0 importoptions=(filetype="basesas" dataTransferMode="parallel") ; quit ; proc casutil ; load casdata="custDimUTF.sas7bdat" incaslib="dm" casout="custDimUTF" outcaslib="dm" copies=0 importoptions=(filetype="basesas" dataTransferMode="parallel") ; quit ; /* Increase the row count */ data dm.custDimBig(copies=0 duplicate=yes replace=yes) ; set dm.custDimUTF; do i = 0 to 1250; customer_id = customer_id + (i * 100000); output; end; run; data dm.orderFactBig(copies=0 replace=yes) ; set dm.orderFactUTF; do i = 0 to 1250; customer_id = customer_id + (i * 100000); output; end; run; proc cas; table.tableDetails / caslib="dm" table="custDimBig"; table.tableDetails / caslib="dm" table="orderFactBig"; quit; /* Run the test */ /* hash join */ data dm.joinHash(copies=0 replace=yes ) ; length customer_id 8 customer_country $4 customer_gender $2; if _N_=1 then do ; declare hash h(dataset: "dm.custDimBig") ; h.defineKey('customer_id') ; h.defineData('Customer_Country','Customer_Gender') ; h.defineDone() ; end ; set dm.orderFactBig; rc=h.find() ; run ; proc cas; table.fetch / to=500 table={caslib="dm" name="joinHash"}; quit;   Foundation SAS Left Join Test code:   libname geldat cvp "/gelcontent/data/OrionStarData/orstar"; libname sasdm "/gelcontent/data"; /* Remove the formats and the Ceda*/ data sasdm.custDimUTF; format Customer_Country $4. Customer_Gender $2.; set geldat.customer_dim; run; data sasdm.orderFactUTF; format Order_Type 12.; set geldat.order_fact; run; /* Increase the row count */ data sasdm.custDimBig ; set sasdm.custDimUTF; do i = 0 to 250; customer_id = customer_id + (i * 100000); output; end; run; data sasdm.orderFactBig; set sasdm.orderFactUTF; do i = 0 to 250; customer_id = customer_id + (i * 100000); output; end; run; /* Run the test */ /* sql join */ proc sql noprint; create table sasdm.sqlJoin as select a.*, b.customer_country, b.customer_gender from sasdm.orderFactBig a left join sasdm.custDimBig b on a.customer_id = b.customer_id; quit; proc print data=sasdm.joinHash(obs=500); run;   ... View more

Inspired by Nicolas Robert's CAS Data Manipulation series, I'll focus this post on performing lookups in CAS. By "lookup," I mean a non-Cartesian, left join of a big table with a small table. Basically augmenting a transaction/analytical table with some reference data like adding a product description to an order table.    Sample Lookup   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Like Nicolas' other scenarios, CAS gives us numerous ways to perform lookups. Let's explore some of the options here and see which one works best. .   The Tests   This post’s test results were produced on a 4 worker node CAS system.   The data used in the tests consists of:   A 14 Million row Sales table (our "big" table that we're augmenting) Three "lookup" tables a Facility table with 130 rows/unique IDs A Product table with 779 rows/unique IDs a Vendor table with 148,567 rows/unique IDs . The Lookup Techniques   The following techniques were considered and tested on all three lookup tables against the sales table:   DS2 Merge DATA Step Merge FedSQL Join Format Join Hash Object Lookup   While the code changed slightly for each different lookup table (e.g. productSKU is used to lookup productMake and facility is used to lookup facilityStateProv, ...), the basic structure of each lookup technique is shown below. In each case, we are looking up the productMake field from the insightProduct table by the productSKU field to add it to the insightToySales table. . DS2 Merge   proc ds2 sessref=mySession ; thread join_th / overwrite=yes ; method run() ; merge dm.insightToySales(in=a) dm.insightProduct (in=b keep=(productSKU productMake)) /RETAIN; by productSKU ; if a; end ; endthread ; data dm.joinDS2(copies=0) / overwrite=yes ; dcl thread join_th t ; method run() ; set from t threads=200 ; end ; enddata ; run ; quit ;   DATA Step Merge   data dm.JoinMerge; merge dm.insightToySales (in=a) dm.insightProduct (in=b keep=productSKU productMake); by productSKU; if a; run;   FedSQL Left Join   proc fedsql sessref=mySession _method; create table dm.joinSQL {options replace=true} as select a.*, b.productMake from dm.insightToySales a left join dm.insightProduct b on a.productSKU = b.productSKU; quit;   Format   proc format cntlin=dm.insightProduct (rename=(productSKU=start productMake=Label)) sessref=mySession casfmtlib="USERFORMATS2"; run; data dm.joinformat(copies=0 replace=yes); set DM.INSIGHTTOYSALES; productMake = put(productSKU,$Product.); run;   Hash Object   data dm.joinHash(copies=0 replace=yes ) ; length productSKU $6 productMake $16; if _N_=1 then do ; declare hash h(dataset: "dm.insightProduct") ; h.defineKey('productSKU') ; h.defineData('productMake') ; h.defineDone() ; call missing(productSKU,productMake) ; end ; set dm.insightToySales ; rc=h.find() ; run ;   Results   .   Conclusions and Observations While the format join marginally beat the hash object on the smallest lookup table, the hash object seems to be the best lookup technique for low cardinality lookups. Loading the format increased substantially for the Vendor table which had by far the most rows. The format load went from 0.19 seconds for the 779 row product table to 5+ seconds for the 148,567 row Vendor table, while the format application (DATA Step with the put() statement) took roughly the same time for both. The FedSQL Join Optimizer chose the hash join method for all three lookup tables. This makes sense since all of the lookup tables are relatively small. Indexing should not improve any technique's performance since CAS indexes are only used for subsetting operations and not join optimization. At lower data volumes (1.4 million rows instead of 14 million), all of the techniques performed more-or-less the same. All of the lookup techniques ran 100% in CAS and in a multi-threaded manner. All of the lookup techniques, except the format, can easily accommodate returning multiple fields from the lookup table. For example, the hash defineData method can accommodate multiple fields. Returning multiple field values using a single format requires some clever string manipulation. All of the lookup techniques can accommodate performing lookups to different tables in a single pass of the transaction/analytical table. To do this with the hash object, you define multiple hashes within the driver DATA Step. To do it with the format, you load the formats in separate steps and then apply them all within the same DATA Step. The format technique requires some pre-planning as you need to add a fmtName field with the format name value (e.g. '$Product') to your lookup table. Remember that PROC SQL does not run in CAS and if you use it or you use non-CAS-enabled DATA Step functionality, your CAS data will be pulled from CAS into the SAS Compute server and your performance will be far worse than anything shown above. Remember also that, to get good performance, you must minimize disk I/O and unnecessary passes of the data. So where possible, you'll want to do as much in a single step as possible. We set the threads parameter in the DS2 SET FROM statement to 200 so that it would default to the highest number possible on the environment. In our case, it was adjusted down to 4. .   Find more articles from SAS Global Enablement and Learning here. ... View more

Back in the LASR days, you could choose to back LASR in-memory tables with Hadoop or not. The Hadoop backing store primarily served as a means to extend LASR's table space beyond the LASR servers' available memory via HDFS SASHDAT file memory-mapping . Without Hadoop, total LASR table size was limited to available server memory (RAM). This was particularly limiting for SMP (single server) LASR since Hadoop integration was not available there.   CAS evolved beyond LASR's memory mapping capabilities in that it could memory map directly from files on the local file systems without the need for Hadoop. And, even when it couldn't memory map a source table directly, it could load the table into a local memory mapped file location, CAS_DISK_CACHE.   With these file memory mapping strategies, CAS was designed from the beginning to store data beyond its servers' available RAM. However, all this time later, I still hear objections that CAS and its in-memory tables are limited by available memory.   Let's Test It   Well, let's take a look on a live CAS system. On our system, we have a 3 CAS worker environment with approximately 150GB of available memory and 186GB of total memory (used or otherwise) according to the Linux free command.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Now, let's load more than 186GB of CAS tables and see what happens. Remember 186GB is all the memory that the three workers have available, used or otherwise.   To get us above 186GB, we'll load 1300 copies of the orderFactMem table which is approximately 150MB.     What does the server memory look like after the load? Have we run out of memory?     No, we haven't run out of memory. In fact we still have about 100GB immediately available.   What's going on?????? Well, as discussed above, the CAS tables were loaded to the CAS Disk Cache location which is mapped to memory but is decidedly on disk. Want to see? Here is a view of the data size of the /cas/cache (CAS_DISK_CACHE) directory before and after the load.   Before     After     The CAS Disk Cache directories are much more full after the load than before. They are full of memory mapped files.   If you want to see them, you'll need the lsof command which gives us the ability to see memory cache files. .   See for yourself?   If you'd like to perform this analysis yourself or expand it, I'm including the Linux commands and SAS program below. As usual, these scripts aren't meant to run blindly. You'll need to supervise them and analyze the results.   Linux commands:   kubectl get pods --all-namespaces kubectl describe pod sas-cas-server-default-controller -n gelenv kubectl exec -it sas-cas-server-default-controller -c cas -n gelenv -- /bin/bash -c "free -m" kubectl exec -it sas-cas-server-default-worker-0 -c cas -n gelenv -- /bin/bash -c "free -m" kubectl exec -it sas-cas-server-default-worker-1 -c cas -n gelenv -- /bin/bash -c "free -m" kubectl exec -it sas-cas-server-default-worker-2 -c cas -n gelenv -- /bin/bash -c "free -m" /* Copy lsof to the pods from the local server */ kubectl cp /usr/sbin/lsof gelenv/sas-cas-server-default-controller:/tmp kubectl cp /usr/sbin/lsof gelenv/sas-cas-server-default-worker-0:/tmp kubectl cp /usr/sbin/lsof gelenv/sas-cas-server-default-worker-1:/tmp kubectl cp /usr/sbin/lsof gelenv/sas-cas-server-default-worker-2:/tmp kubectl exec -it sas-cas-server-default-worker-0 -c cas -n gelenv -- /bin/bash -c "df -m /cas/cache" kubectl exec -it sas-cas-server-default-worker-1 -c cas -n gelenv -- /bin/bash -c "df -m /cas/cache" kubectl exec -it sas-cas-server-default-worker-2 -c cas -n gelenv -- /bin/bash -c "df -m /cas/cache" kubectl exec -it sas-cas-server-default-worker-0 -c cas -n gelenv -- /bin/bash -c "/tmp/lsof -a +L1" kubectl exec -it sas-cas-server-default-worker-1 -c cas -n gelenv -- /bin/bash -c "/tmp/lsof -a +L1" kubectl exec -it sas-cas-server-default-worker-2 -c cas -n gelenv -- /bin/bash -c "/tmp/lsof -a +L1"   SAS Code:   /* Create a CSV file for the memory test */ /* Need a non-hdat source to remove any possibility of memory mapping */ proc casutil incaslib="dm" outcaslib="dm"; load casdata="order_fact.sashdat" casout="orderFactMem"; save casdata="orderFactMem" casout="orderFactMem.csv"; droptable casdata="orderFactMem" incaslib="dm"; quit ; /* Load the table multiple times */ %macro load; %do i=1 %to 1300; proc casutil incaslib="dm" outcaslib="dm"; load casdata="orderFactMem.csv" casout="orderFactMem&i" copies=0; quit ; %end; %mend; %load; proc cas; table.tableinfo caslib="dm" name="orderFactMem1" ; table.tabledetails caslib="dm" name="orderFactMem1" level="sum"; table.tabledetails caslib="dm" name="orderFactMem1" level="node"; run; proc cas; accessControl.assumeRole / adminRole="SUPERUSER"; builtins.getCacheInfo; run; quit; cas mysession terminate;   Conclusion   So, don't be afraid of running out of memory with CAS and use it for your data processing needs. It's fast. . Want More?   If you want specifics on CAS tables and memory, see this post. .   Find more articles from SAS Global Enablement and Learning here. ... View more

Building on my "CAS is fast" post, today, we'll compare match code generation between foundation SAS (a.k.a the Compute Server) and CAS and we'll be using data of varying size in response to concerns that "CAS is only for big data." On each row, we'll generate a match code for a customer name field at 95% sensitivity.   Test Results   From the results below, we see that CAS does indeed process the data more slowly at extremely small volumes (hundreds and thousands of rows) but quickly overtakes Compute around volumes of tens of thousands of records.                                           DQ Match Code Generation Comparison Test Results Engine Rows Size (mb) Match Code Generation Time Compute (Foundation SAS) 599 0.256 0.31s CAS 599 0.153 10.63s Compute 5990 1.5 3.02s CAS 5990 1.5 10.99s Compute 59900 14 25.85s CAS 59900 15 11.85s Compute 599000 140 4:04m CAS 599000 153 27.14s Compute 5990000 1392 50:25m CAS 5990000 1533 (238 DVR) 1:55m . From this graphical representation, we can also see that performance degrades more abruptly for Compute than CAS as data volumes increase.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.     Conclusions   At extremely small "PoC" data volumes Foundation SAS (Compute) outperforms CAS on match code generation. However, at more realistic volumes CAS outperforms Compute by orders of magnitude. .   Project Environment, Code, and Log Snipets   The environment contains the latest LTS Viya software deployed with 3 CAS worker nodes and one controller each with 4 virtual cores and 32MB of RAM.   Project Code -- Not Production Quality; Not Meant to Run Without Intervention   CAS mySession SESSOPTS=(metrics=true); CASLIB _all_ assign; /* Prepare the compute server test */ data sasdm.customer_list; length id 8 name $32 address $50 'zip code'n $10 phone $10 city $32 country $32 notes $50 sid 8; set saspgdvd.customer_list; run; data sasdm.customer_list10 ; set sasdm.customer_list sasdm.customer_list sasdm.customer_list sasdm.customer_list sasdm.customer_list sasdm.customer_list sasdm.customer_list sasdm.customer_list sasdm.customer_list sasdm.customer_list; run; data sasdm.customer_list100; set sasdm.customer_list10 sasdm.customer_list10 sasdm.customer_list10 sasdm.customer_list10 sasdm.customer_list10 sasdm.customer_list10 sasdm.customer_list10 sasdm.customer_list10 sasdm.customer_list10 sasdm.customer_list10; run; data sasdm.customer_list1000; set sasdm.customer_list100 sasdm.customer_list100 sasdm.customer_list100 sasdm.customer_list100 sasdm.customer_list100 sasdm.customer_list100 sasdm.customer_list100 sasdm.customer_list100 sasdm.customer_list100 sasdm.customer_list100; run; data sasdm.customer_list10000; set sasdm.customer_list1000 sasdm.customer_list1000 sasdm.customer_list1000 sasdm.customer_list1000 sasdm.customer_list1000 sasdm.customer_list1000 sasdm.customer_list1000 sasdm.customer_list1000 sasdm.customer_list1000 sasdm.customer_list1000; run; proc contents data=sasdm.customer_list;run; proc contents data=sasdm.customer_list10;run; proc contents data=sasdm.customer_list100;run; proc contents data=sasdm.customer_list1000;run; proc contents data=sasdm.customer_list10000;run; /*Prepare the CAS test*/ data dm_pgdvd.customer_list (copies=0 promote=yes); set sasdm.customer_list; run; data dm_pgdvd.customer_list10 (copies=0 promote=yes); set sasdm.customer_list10; run; data dm_pgdvd.customer_list100 (copies=0 promote=yes); set sasdm.customer_list100; run; data dm_pgdvd.customer_list1000 (copies=0 promote=yes); set sasdm.customer_list1000; run; data dm_pgdvd.customer_list10000 (copies=0 promote=yes); set sasdm.customer_list10000; run; proc cas; table.copyTable / table={name="customer_list10000" caslib="dm_pgdvd"} casOut={name="customer_list10000dvr" caslib="dm_pgdvd" memoryFormat="DVR" replace=True replication=0}; run; /* Table Stats */ proc cas; table.fileInfo / caslib="dm" ; quit ; proc cas; table.tableInfo / caslib="dm_pgdvd" name="customer_list" ; table.tableInfo / caslib="dm_pgdvd" name="customer_list10" ; table.tableInfo / caslib="dm_pgdvd" name="customer_list100" ; table.tableInfo / caslib="dm_pgdvd" name="customer_list1000" ; table.tableInfo / caslib="dm_pgdvd" name="customer_list10000" ; table.tableInfo / caslib="dm_pgdvd" name="customer_list10000dvr" ; quit ; proc cas; table.columnInfo / table={caslib="dm_pgdvd" name="customer_list"} ; table.columnInfo / table={caslib="dm_pgdvd" name="customer_list10"} ; table.columnInfo / table={caslib="dm_pgdvd" name="customer_list100"} ; table.columnInfo / table={caslib="dm_pgdvd" name="customer_list1000"} ; table.columnInfo / table={caslib="dm_pgdvd" name="customer_list10000"} ; table.columnInfo / table={caslib="dm_pgdvd" name="customer_list10000dvr"} ; quit ; proc cas; table.tabledetails / caslib="dm_pgdvd" name="customer_list" level="SUM"; table.tabledetails / caslib="dm_pgdvd" name="customer_list10" level="SUM"; table.tabledetails / caslib="dm_pgdvd" name="customer_list100" level="SUM"; table.tabledetails / caslib="dm_pgdvd" name="customer_list1000" level="SUM"; table.tabledetails / caslib="dm_pgdvd" name="customer_list10000" level="SUM"; table.tabledetails / caslib="dm_pgdvd" name="customer_list10000dvr" level="SUM"; quit ; /* Load the DQ locale */ %DQLOAD(DQLOCALE=(ENUSA), DQSETUPLOC='/opt/sas/viya/home/share/refdata/qkb/QKB CI 32/qkb-ci-32.1.3-qkb-viya.qarc'); /* Run the compute server test */ data sasdm.customerMatchCode; length mcName $100; set sasdm.customer_list; mcName=dqMatch(name,'NAME',95,'ENUSA') ; run; data sasdm.customerMatchCode; length mcName $100; set sasdm.customer_list10; mcName=dqMatch(name,'NAME',95,'ENUSA') ; run; data sasdm.customerMatchCode; length mcName $100; set sasdm.customer_list100; mcName=dqMatch(name,'NAME',95,'ENUSA') ; run; data sasdm.customerMatchCode; length mcName $100; set sasdm.customer_list1000; mcName=dqMatch(name,'NAME',95,'ENUSA') ; run; data sasdm.customerMatchCode; length mcName $100; set sasdm.customer_list10000; mcName=dqMatch(name,'NAME',95,'ENUSA') ; run; /* Run the CAS test */ data dm_pgdvd.customerMatchCode ; length mcName $100; set dm_pgdvd.customer_list; mcName=dqMatch(name,'NAME',95,'ENUSA') ; run; data dm_pgdvd.customerMatchCode ; length mcName $100; set dm_pgdvd.customer_list10; mcName=dqMatch(name,'NAME',95,'ENUSA') ; run; data dm_pgdvd.customerMatchCode ; length mcName $100; set dm_pgdvd.customer_list100; mcName=dqMatch(name,'NAME',95,'ENUSA') ; run; data dm_pgdvd.customerMatchCode ; length mcName $100; set dm_pgdvd.customer_list1000; mcName=dqMatch(name,'NAME',95,'ENUSA') ; run; data dm_pgdvd.customerMatchCode ; length mcName $100; set dm_pgdvd.customer_list10000; mcName=dqMatch(name,'NAME',95,'ENUSA') ; run;     Test Log -- Compute Running on 5.9M Rows   79 data sasdm.customerMatchCode; 80 length mcName $100; 81 set sasdm.customer_list10000; 82 mcName=dqMatch(name,'NAME',95,'ENUSA') ; 83 run; NOTE: There were 5990000 observations read from the data set SASDM.CUSTOMER_LIST10000. NOTE: The data set SASDM.CUSTOMERMATCHCODE has 5990000 observations and 10 variables. NOTE: DATA statement used (Total process time): real time 50:25.07 cpu time 50:34.12   Test Log -- CAS Running on 5.9M Rows   79 data dm_pgdvd.customerMatchCode ; 80 length mcName $100; 81 set dm_pgdvd.customer_list10000; NOTE: Executing action 'table.tableInfo'. NOTE: Action 'table.tableInfo' used (Total process time): NOTE: real time 0.016233 seconds NOTE: cpu time 0.017619 seconds (108.54%) NOTE: total nodes 4 (32 cores) NOTE: total memory 251.04G NOTE: memory 1.58M (0.00%) NOTE: Executing action 'table.tableInfo'. NOTE: Action 'table.tableInfo' used (Total process time): NOTE: real time 0.010916 seconds NOTE: cpu time 0.013365 seconds (122.43%) NOTE: total nodes 4 (32 cores) NOTE: total memory 251.04G NOTE: memory 1.58M (0.00%) NOTE: Executing action 'table.columnInfo'. NOTE: Action 'table.columnInfo' used (Total process time): NOTE: real time 0.031052 seconds NOTE: cpu time 0.026757 seconds (86.17%) NOTE: total nodes 4 (32 cores) NOTE: total memory 251.04G NOTE: memory 3.41M (0.00%) 82 mcName=dqMatch(name,'NAME',95,'ENUSA') ; 83 run; NOTE: Running DATA step in Cloud Analytic Services. NOTE: Executing action 'sessionProp.getSessOpt'. NOTE: Action 'sessionProp.getSessOpt' used (Total process time): NOTE: real time 0.018381 seconds NOTE: cpu time 0.014225 seconds (77.39%) NOTE: total nodes 4 (32 cores) NOTE: total memory 251.04G NOTE: memory 851.53K (0.00%) NOTE: Executing action 'sessionProp.setSessOpt'. NOTE: Action 'sessionProp.setSessOpt' used (Total process time): NOTE: real time 0.012184 seconds NOTE: cpu time 0.015754 seconds (129.30%) NOTE: total nodes 4 (32 cores) NOTE: total memory 251.04G NOTE: memory 1.07M (0.00%) NOTE: The DATA step will run in multiple threads. NOTE: Executing action 'dataStep.runBinary'. NOTE: There were 5990000 observations read from the table CUSTOMER_LIST10000 in caslib DM_PGDVD. NOTE: The table customerMatchCode in caslib DM_PGDVD has 5990000 observations and 10 variables. NOTE: Action 'dataStep.runBinary' used (Total process time): NOTE: real time 114.835489 seconds NOTE: cpu time 2115.218305 seconds (1841.96%) NOTE: data movement time 0.055814 seconds NOTE: total nodes 4 (32 cores) NOTE: total memory 251.04G NOTE: memory 4.04G (1.61%) NOTE: bytes moved 2.01G NOTE: Executing action 'sessionProp.setSessOpt'. NOTE: Action 'sessionProp.setSessOpt' used (Total process time): NOTE: real time 0.011197 seconds NOTE: cpu time 0.014895 seconds (133.03%) NOTE: total nodes 4 (32 cores) NOTE: total memory 251.04G NOTE: memory 1.07M (0.00%) NOTE: DATA statement used (Total process time): real time 1:55.11 cpu time 0.53 seconds   Notes:  We ran both DATA Step and PROC DQMatch on Compute and both performed similarly. We tested both DVR and non-DVR format in CAS. Performance on both formats was similar. These tests were run on virtual hardware and should not be treated as benchmarks. Find more articles from SAS Global Enablement and Learning here. ... View more

In my last article, I equated SID global variables to SID lookup tables and showed how to update a global variable via REST. Well, it turns out the REST API for updating SID lookup tables is a bit different than the API for global variables. Let's look at the differences here and create a script to update SID lookup tables.   In order to update our lookup table, we'll have to perform the following steps. Some of these were covered in the previous post. Some are different. Some are new.   Find the lookup table URI Post a new version to the lookup table. Add lookup table entries to the new version. Activate the new version.   But First, a Word on Authentication Like Xavier Bizoux  did here, we'll use the oauth_bearer = sas_services PROC HTTP option within SAS Studio to piggy back on the authentication already established by the user interface. Of course, if you run the script outside of SAS Studio, you'll need to authenticate using some other mechanism. To explore Viya authentication options, see Joe Furbee's post here. .   1. Find the Lookup Table URI In the previous post, we looked up the URI using the SID user interface. Here we do it with code. First we use PROC HTTP to retrieve all of the visible lookup tables ("reference data domains") and then a DATA Step filters the list to find the one we want, name = "test". Finally the URI is placed in a macro variable, lkpURI.   filename lookups temp; proc http url = "https://live.lts.gel.race.sas.com/referenceData/domains/" out= lookups oauth_bearer = sas_services; headers "Accept"="application/vnd.sas.collection+json"; run; libname lookups json; data _null_; set lookups.items; where name="test"; call symput('lkpURI',id); run; %put &lkpURI;   For information on the API call, see the doc here.   For information on using PROC HTTP to call the Viya API, see Xavier Bizoux's article here.   2. Post a New Version to the Lookup Table Unlike global variables, we can't update the (current version of the) lookup table directly. We can only update lookup table versions. So, instead of trying to find the specific version we want update, we'll just create a new one.   Below we POST a simple message to our lookup table URI contents to create a new version labelled "Yet Another One." We set the status to "developing" so that we can add entries to it later.   Once our new version is created, we put its URI into the macro variable, vrsnURI.   proc http method = "POST" url = https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents in = '{"label": "Yet Another One","status":"developing"}' out= response oauth_bearer = sas_services; headers "Accept"="application/json" "Content-Type"="application/json;charset=utf-8"; run; libname response json; data _null_; set response.root; call symput('vrsnURI',id); run; %put &vrsnURI;   3. Add Lookup Table Entries Now we'll add lookup table entries to our new lookup table version. These will, of course, be different to active version's lookup table entries. For example, say our lookup table has the current entries,   1 Eeny 2 Meeny 3 Miney 4 Moe   ...and we want to change them to:   1 Oneay 2 Wotay 3 Eathray 4 Ourfay   ...the API request would look like the below code. Again, we post the desired entries to the lookup table's version entries endpoint, /referenceData/domains/&lkpURI/contents/&vrsnURI/entries.   filename entryRes temp; proc http method = "POST" url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents/&vrsnURI/entries" in = '[{"key": "1","value": "Oneay"}, {"key": "2","value": "Wotay"}, {"key": "3","value": "Eathray"}, {"key": "4","value": "OurFay"}]' out= entryRes oauth_bearer = sas_services; headers "Accept"="application/json" "Content-Type"="application/json;charset=utf-8"; run; libname entryRes json;   4. Activate the New Version Unlike SID global variables, there is no activate action for lookup tables. Instead we update the status of lookup table version we want to activate. . 4.1 GET the Lookup Table Version ETag Any time we want to update an object, we need to get it's ETag. We need the ETag when we perform the update in the next step. REST uses the ETag to help avoid update collisions.   The Etag is output in the response header which unfortunately doesn't come out as nicely formatted json. So we do a bit of DATA Step manipulation to grab it. I've output the entire response header to the log, posted below, for your inspection.   filename lkpvrsn temp; filename lkpvhdr temp; proc http url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI" out=lkpvrsn headerout=lkpvhdr oauth_bearer = sas_services; headers "Accept"="application/json" "Content-Type"="application/json;charset=utf-8"; run; libname lkpvrsn json; data _null_; infile lkpvhdr; input; put _infile_; if length(_infile_) > 4 then if substr(_infile_,1,4)="ETag" then do; call symput('etag',scan(_infile_,2)); end; run; %put &etag;   4.2 Update the Lookup Table Version Status With both the lookup table version URI and its ETag, we can now update the lookup table version. The HTTP request is below.   filename prodVrsn temp; proc http method = "PUT" url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents/&vrsnURI" in = '{"status":"production"}' out= prodVrsn oauth_bearer = sas_services; headers "Accept"="application/json" "Content-Type"="application/json;charset=utf-8" "If-Match"=%tslit(&etag); run; libname prodVrsn json;   Verifying the Update Looking in Intelligent Decisioning, we can verify that the "test" lookup table is updated with the new entries and the version with the new entries is active. Although the version label isn't available in the UI, we can verify the version by matching the version URI with the SAS log, posted below.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   . Full Code and Log Below you'll find the full script and execution log. As mentioned before, you'll need to customize the script to add the authentication that makes sense for your situation. You can of course, leave the authentication as is if you plan on running it in SAS Studio. You'll also need to change the initial query conditions (where name="test" in the example) to find your particular lookup table as well as the lookup table entries. .   Code: ** GET Lookup Table URI; filename lookups temp; proc http url = "https://live.lts.gel.race.sas.com/referenceData/domains/" out= lookups oauth_bearer = sas_services; headers "Accept"="application/vnd.sas.collection+json"; run; libname lookups json; data _null_; set lookups.items; where name="test"; call symput('lkpURI',id); run; %put &lkpURI; ** Post a new version to the lookup table; filename response temp; proc http method = "POST" url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents" in = '{"label": "One More","status":"developing"}' out= response oauth_bearer = sas_services; headers "Accept"="application/json" "Content-Type"="application/json;charset=utf-8"; run; libname response json; data _null_; set response.root; call symput('vrsnURI',id); run; %put &vrsnURI; ** Add Entries; filename entryRes temp; proc http method = "POST" url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents/&vrsnURI/entries" in = '[{"key": "1","value": "Oneay"}, {"key": "2","value": "Wotay"}, {"key": "3","value": "Eathray"}, {"key": "4","value": "OurFay"}]' out= entryRes oauth_bearer = sas_services; headers "Accept"="application/json" "Content-Type"="application/json;charset=utf-8"; run; libname entryRes json; ** Get the Lookup Table Version Etag; filename lkpvrsn temp; filename lkpvhdr temp; proc http url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents/&vrsnURI" out=lkpvrsn headerout=lkpvhdr oauth_bearer = sas_services; headers "Accept"="application/json" "Content-Type"="application/json;charset=utf-8"; run; libname lkpvrsn json; data _null_; infile lkpvhdr; input; put _infile_; if length(_infile_) > 4 then if substr(_infile_,1,4)="ETag" then do; call symput('etag',scan(_infile_,2)); end; run; %put &etag; ** Activate the new version (Set Status to "Production"); filename prodVrsn temp; proc http method = "PUT" url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents/&vrsnURI" in = '{"status":"production"}' out= prodVrsn oauth_bearer = sas_services; headers "Accept"="application/json" "Content-Type"="application/json;charset=utf-8" "If-Match"=%tslit(&etag); run; libname prodVrsn json; . Log:   1 %studio_hide_wrapper; 77 78 ** GET Lookup Table URI; 79 80 filename lookups temp; 81 82 proc http 83 url = "https://live.lts.gel.race.sas.com/referenceData/domains/" 84 out= lookups 85 oauth_bearer = sas_services; 86 headers 87 "Accept"="application/vnd.sas.collection+json"; 88 run; NOTE: 200 OK NOTE: PROCEDURE HTTP used (Total process time): real time 0.40 seconds cpu time 0.07 seconds 89 90 libname lookups json; NOTE: JSON data is only read once. To read the JSON again, reassign the JSON LIBNAME. NOTE: Libref LOOKUPS was successfully assigned as follows: Engine: JSON Physical Name: /opt/sas/viya/config/var/tmp/compsrv/default/c4980a69-adae-4d03-af56-6e0706dbd771/SAS_work84D1000000A7_sas-launcher-14b63597-b bee-4180-b29f-f3a4253faf46-qrm4r/#LN00082 91 92 data _null_; 93 set lookups.items; 94 where name="test"; 95 call symput('lkpURI',id); 96 run; NOTE: There were 1 observations read from the data set LOOKUPS.ITEMS. WHERE name='test'; NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 97 98 %put &lkpURI; 47944623-dd84-41de-a7d2-5a4ab507e0c9 99 100 ** Post a new version to the lookup table; 101 102 filename response temp; 103 104 proc http 105 method = "POST" 106 url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents" 107 in = '{"label": "One More","status":"developing"}' 108 out= response 109 oauth_bearer = sas_services; 110 headers 111 "Accept"="application/json" 112 "Content-Type"="application/json;charset=utf-8"; 113 run; NOTE: 201 Created NOTE: PROCEDURE HTTP used (Total process time): real time 0.20 seconds cpu time 0.05 seconds 114 115 libname response json; NOTE: JSON data is only read once. To read the JSON again, reassign the JSON LIBNAME. NOTE: Libref RESPONSE was successfully assigned as follows: Engine: JSON Physical Name: /opt/sas/viya/config/var/tmp/compsrv/default/c4980a69-adae-4d03-af56-6e0706dbd771/SAS_work84D1000000A7_sas-launcher-14b63597-b bee-4180-b29f-f3a4253faf46-qrm4r/#LN00086 116 117 data _null_; 118 set response.root; 119 call symput('vrsnURI',id); 120 run; NOTE: There were 1 observations read from the data set RESPONSE.ROOT. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 121 122 %put &vrsnURI; 84e0d6cd-2ca2-4a19-aee6-c6596c4ec3f7 123 124 ** Add Entries; 125 126 filename entryRes temp; 127 128 proc http 129 method = "POST" 130 url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents/&vrsnURI/entries" 131 in = '[{"key": "1","value": "Oneay"}, 132 {"key": "2","value": "Wotay"}, 133 {"key": "3","value": "Eathray"}, 134 {"key": "4","value": "OurFay"}]' 135 out= entryRes 136 oauth_bearer = sas_services; 137 headers 138 "Accept"="application/json" 139 "Content-Type"="application/json;charset=utf-8"; 140 run; NOTE: 201 Created NOTE: PROCEDURE HTTP used (Total process time): real time 0.18 seconds cpu time 0.02 seconds 141 142 libname entryRes json; NOTE: JSON data is only read once. To read the JSON again, reassign the JSON LIBNAME. NOTE: Libref ENTRYRES was successfully assigned as follows: Engine: JSON Physical Name: /opt/sas/viya/config/var/tmp/compsrv/default/c4980a69-adae-4d03-af56-6e0706dbd771/SAS_work84D1000000A7_sas-launcher-14b63597-b bee-4180-b29f-f3a4253faf46-qrm4r/#LN00089 143 144 ** Get the Lookup Table Version Etag; 145 146 filename lkpvrsn temp; 147 filename lkpvhdr temp; 148 149 proc http 150 url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents/&vrsnURI" 151 out=lkpvrsn 152 headerout=lkpvhdr 153 oauth_bearer = sas_services; 154 headers 155 "Accept"="application/json" 156 "Content-Type"="application/json;charset=utf-8"; 157 run; NOTE: 200 OK NOTE: PROCEDURE HTTP used (Total process time): real time 0.15 seconds cpu time 0.04 seconds 158 159 libname lkpvrsn json; NOTE: JSON data is only read once. To read the JSON again, reassign the JSON LIBNAME. NOTE: Libref LKPVRSN was successfully assigned as follows: Engine: JSON Physical Name: /opt/sas/viya/config/var/tmp/compsrv/default/c4980a69-adae-4d03-af56-6e0706dbd771/SAS_work84D1000000A7_sas-launcher-14b63597-b bee-4180-b29f-f3a4253faf46-qrm4r/#LN00092 160 161 data _null_; 162 infile lkpvhdr; 163 input; 164 put _infile_; 165 if length(_infile_) > 4 then if substr(_infile_,1,4)="ETag" then do; 166 call symput('etag',scan(_infile_,2)); 167 end; 168 run; NOTE: The infile LKPVHDR is: Filename=/opt/sas/viya/config/var/tmp/compsrv/default/c4980a69-adae-4d03-af56-6e0706dbd771/SAS_work84D1000000A7_sas-launcher-1 4b63597-bbee-4180-b29f-f3a4253faf46-qrm4r/#LN00093, Owner Name=UNKNOWN,Group Name=UNKNOWN, Access Permission=-rw-r--r--, Last Modified=19Nov2021:15:32:24, File Size (bytes)=708 HTTP/1.1 200 Date: Fri, 19 Nov 2021 20:32:24 GMT Server: Apache/2.4.6 (Red Hat Enterprise Linux) OpenSSL/1.0.2k-fips Content-Type: application/vnd.sas.data.reference.domain.content+json Content-Security-Policy: default-src 'self'; script-src 'self' 'unsafe-inline' 'unsafe-eval'; img-src 'self' *.sas.com blob: data:; style-src 'self' 'unsafe-inline'; child-src 'self' blob: data: mailto:; ETag: "kw6u9hfg" X-Content-Type-Options: nosniff X-XSS-Protection: 1; mode=block Cache-Control: no-cache, no-store, max-age=0, must-revalidate Pragma: no-cache Expires: 0 Strict-Transport-Security: max-age=15724800; includeSubDomains X-Frame-Options: SAMEORIGIN Keep-Alive: timeout=5, max=100 Connection: Keep-Alive NOTE: 16 records were read from the infile LKPVHDR. The minimum record length was 0. The maximum record length was 203. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 169 170 %put &etag; "kw6u9hfg" 171 172 ** Activate the new version (Set Status to "Production"); 173 174 filename prodVrsn temp; 175 176 proc http 177 method = "PUT" 178 url = "https://live.lts.gel.race.sas.com/referenceData/domains/&lkpURI/contents/&vrsnURI" 179 in = '{"status":"production"}' 180 out= prodVrsn 181 oauth_bearer = sas_services; 182 headers 183 "Accept"="application/json" 184 "Content-Type"="application/json;charset=utf-8" 185 "If-Match"=%tslit(&etag); 186 run; NOTE: 200 OK NOTE: PROCEDURE HTTP used (Total process time): real time 4.72 seconds cpu time 0.04 seconds 187 188 libname prodVrsn json; NOTE: JSON data is only read once. To read the JSON again, reassign the JSON LIBNAME. NOTE: Libref PRODVRSN was successfully assigned as follows: Engine: JSON Physical Name: /opt/sas/viya/config/var/tmp/compsrv/default/c4980a69-adae-4d03-af56-6e0706dbd771/SAS_work84D1000000A7_sas-launcher-14b63597-b bee-4180-b29f-f3a4253faf46-qrm4r/#LN00096 189 190 191 192 %studio_hide_wrapper; 200   Next Steps You can, of course, develop this process further by parameterizing the code and making the lookup table entries data driven. I didn't tackle anything like that here as I wanted the code as simple as possible for ease of understanding.   Find more articles from SAS Global Enablement and Learning here. ... View more

Global variables have existed in Intelligent Decisioning since the 5.5 release. Lookup tables have existed from the beginning. I mention lookup tables and global variables together because they are more or less the same thing. Both are implemented as hash objects in MAS and formats (or in-line conditional logic code) in CAS. For the most part, a global variable is just a one row lookup table.     Global Variable Example     Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   So, naturally people's first reaction to seeing global variables is the same as their first reaction to seeing lookup tables. They want to update them programmatically, without using the SID user interface. So let's do that here with REST... . Find the global variable URI First, we'll need to locate our global variable's universal resource identifier. We could do this programmatically but we'll just look it up in the Viya UI to get a little context. As, the image below shows, you can find the URI by editing the global variable and looking on the versions tab. Note that, for this exercise, we want the global variable URI, not the version ("revision") URI.   When we update a global variable, the "Displayed Version" will be updated.   GET the Global Variable ETag Next, we need to perform a GET request on the global variable to obtain its current ETag. We need the ETag when we perform the update in the next step. REST uses the ETag to help avoid update collisions.   Note that we use the global variable URI, /referenceData/globalVariables/2744cc6d-53bf-490a-8dc7-352dead40ace, in the GET request:   GET /referenceData/globalVariables/2744cc6d-53bf-490a-8dc7-352dead40ace/ HTTP/1.1 Host: yourViyaHost.comp.com:443 Accept: application/json Content-Type: application/json;charset=utf-8 Authorization: Bearer XXXXXXXXXXXXXXXXXXXXXXXXX Cookie: sas-ingress-nginx=6e50bb65648b4904ca91085f11bee901   The ETag is returned in the response header, shown below in Postman.       Update (PUT) the Global Variable With both the URI and the ETag, we can now update the global variable. The HTTP request is below. Note the following:   The request uses an HTTP PUT. The ETag is used in the If-Match request header element. The request body only requires a few fields from the full global variable schema. The request body changes the global variable value from 1.19 (shown above) to 1.2. PUT /referenceData/globalVariables/2744cc6d-53bf-490a-8dc7-352dead40ace/ HTTP/1.1 Host: yourViyaHost.comp.com:443 Accept: application/json Content-Type: application/json;charset=utf-8 If-Match: "ktorw3bn" Authorization: Bearer XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX Cookie: sas-ingress-nginx=6ff3a6db3117ea6c83b58f72ff5d473a Content-Length: 104 { "name": "eurusa", "description": "eurusa", "defaultValue": 1.2, "dataType": "decimal" }   And we then we can see that value changed in the user interface….     Next Steps . Want to update/add/delete global variables, lookup tables and, more without using the UI? Check out the Viya REST API at developer.sas.com. . So now that we've updated the global variable, we'll, of course, have to re-activate it. You can see the API doc for that here and we'll cover it in a future post as well. . Of course, we'll want to script this whole process so that we can easily run it when we want. We can do that in SAS with PROC HTTP as well as a host of other languages including Python. ... View more

Sometimes it feels like there's a CAS action for everything ("There's a CAS action for that!"). However, they're not always named or formed like you might expect. For example, to create a match code you might need to use the DATA Step CAS action, and, to load a CAS table into a database, you might need to use the SAVE action.   If you're a database guy, you might expect CAS to have an explicitly named "Truncate" action but it does not. Starting back in 2021.1.2, CAS supports table trunctation with the DeleteRows action. To trigger truncation you specify a WHERE clause with an (always) TRUE condition, i.e. WHERE = '1'. ("Delete Rows Where True." Since the WHERE clause always resolves to TRUE, every record gets deleted. Get it?) So truncating a CAS table looks like this: proc cas; table.deleterows / table={name="test" caslib="DM" where='1'}; run; How is this different than just deleting all of the table's rows? You could do that before with the DeleteRows action. The difference boils down to performance. Like any other data processing task, deleting rows from a table can be process intensive. CAS has to locate the records according to WHERE conditions and mark each as logically deleted. Truncation minimizes the impact on system resources by manipulating the table definition instead of the table data. Using CAS' internal table management mechanisms, truncation marks the table as empty and frees the storage space instead of processing the table records. Thus truncation can be orders of magnitude faster than deleting all of the rows.   How much faster is truncating versus explicitly deleting all a CAS table's records? Let's look...   In this example, we have an ~80 million row CAS table. First we'll delete all of the rows with a data value subsetting WHERE clause and then we'll truncate the table. As you can see from the screen shots, deleting all of the rows takes around 6 minutes while truncating the table takes around 6 seconds.   Data Value Driven Row Deletion Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   WHERE=1 Clause Triggering Truncation And, why do we care about table truncation? For near-real time CAS table updates of course, namely using the Truncate and Append method. ... View more

In my last article, I demonstrated basic parallel CAS action execution. In this post, we'll take it a step further and dynamically drive the parallel execution from a dynamic list, e.g. table read, directory listing, etc..   We'll basically be recreating this example from the SAS doc but we'll be adding a bit to it. For the SAS programmers, this is all very similar to data driven macro. The process will reflect the image below. A CAS Action will be applied in parallel to the values in an input list by looping.    Data Driven Parallel CAS Action Execution Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page. .   The runCASL Action To use some of the required language elements, the entire CASL code block must be stored as a text CASL variable and executed using the runCASL CAS action. Use the Source statement to mark the beginning of the parallel CASL code, and the endSource statement to mark the end of the code.   For SAS macro programmers, the CASL Source, endSource, and runCASL statements function like the %macro, %mend, and the actual macro call itself respectively, while creating the CASL code within the text variable is very similar to generating code using SAS macros.  runCASL Example   .   The CASL Dictionary Driver List Within the CASL code block, we'll iterate over a list that drives the parallel CAS actions. We'll call this the "driver list."   The SAS doc example uses a simple static list,   tables = ${iris cars}; Here tables is a CASL dictionary that contains two members, iris and cars. The list feeds some CASL logic that is applied to both values -- One time through for iris and another time through for cars, both in parallel of course.   CASL dictionaries can also be created dynamically using CAS Action Result variables. Below we see a dynamic list of source files in a CASLib DataSource. We'll actually use this list to feed parallel loadTable CAS actions later.    Dynamic List of Source Files in a CASlib   DO Loop Spawning Parallel CAS Actions A DO loop then iterates over the CAS actions submitting one for every item in the driver list. For the tables list in the SAS doc example, that looks like this with the dim() function giving the number of items in the list:   do i = 1 to dim(tables); References to the list items are done using simple array naming with the list name and the iterator, i, in brackets:   fname = tables[i] || '.sashdat'; . create_parallel_session The final piece of the puzzle is the create_parallel_session() function which allows for dynamic creation of CAS sessions. If you remember from the last article, we'll need a separate CAS session for each parallel CAS action. Assign the new session name to the create_parallel_session() function as shown below. Here the name, sc_sess[i] includes the iterator, i, so the spawned sessions will be named  sc_sess[1], sc_sess[2], ....   sc_sess[i] = create_parallel_session(); . Putting the Spawning Pieces Together Combining all of the components above, a complete data-driven, parallel CASLib DataSource Loader looks like this:     Data Driven Parallel CASLib Load   Data Driven Parallel CASLib Load[/caption] Note that the loop iterates over the items in the list which are files in the CASLib DataSource location in this case, running a load table action for each. The ASYNC and SESSION options on the tableLoad action ensure it will run asynchronously. For more information on this, see my last post. .   Getting Execution Status So our parallel CAS actions are now off and we hope they work but if we want to know for sure, we still have a bit of work to do. As we saw in the previous article, we'll need to use the wait_for_next_action casl function to get the status of each asynchronous action. Doing that in an automated, data-driven fashion requires more looping. Luckily though, the wait_for_next_action function does a lot of the work for us. When given a 0 parameter, it will wait for any asynchronous action and return the results of the first one that completes. Using this to our advantage, we can just loop on the function until it returns the results of all asynchronous actions.       Looping over the asynchronous action results[/caption]   The job variable receives the execution results/statistics of each asynchronous action which we print to the log:   Wait_for_Next_Action Printout   The Complete Code Fully complete, our code looks like the following. Hopefully this is useful to you.   proc cas; source pgm; table.fileinfo result=fI / caslib="public"; do i = 1 to dim(fI.fileInfo[,"name"]); cfile = fI.fileInfo[i,"name"]; ctable = scan(cfile,1,"."); sc_sess[i] = create_parallel_session(); table.dropTable / table=ctable caslib="public"; table.loadtable session=sc_sess[i] async=cfile / path=cfile caslib='public' casOut={name=ctable caslib='public' promote=True}; end; job = wait_for_next_action(0); do while(job); print job; job = wait_for_next_action(0); end; endsource; sccasl.runCasl / code=pgm; run;   Find more articles from SAS Global Enablement and Learning here. ... View more

Do you like running independent processes in parallel? I know I do. Say you need to load a 10 dimension table star schema. Why not load all 10 at the same time? Say you need to run 15 reports. Why not run them all at the same time? Say you need to summarize 5 tables as input to a complex DATA Step. Why not summarize them all at the same time? We can do this in base SAS with MP Connect; We can do it in DI Studio; And we can do it in CASL too!   We need a few puzzle pieces to make parallel CAS action execution a reality. Let's look at those here and build up to parallel execution....   Puzzle Piece 1: Multiple CAS Sessions The first thing we need for parallel CAS actions is multiple CAS sessions.   To run CAS actions in parallel, each must run in its own CAS session. If we want to run 8 CAS actions in parallel, we'll need 8 CAS sessions. 9 CAS actions? Then 9 sessions. 10 CAS actions? Then 10 sessions....   How do we create multiple CAS sessions? With multiple CAS statements of course. Below, we see an example creating two sessions, casSession1, casSession2 that are meant for two parallel CAS actions.     Starting two CAS Sessions Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Puzzle Piece 2: ASYNC Option The next thing we need is a mechanism to run CAS actions asynchronously. For example, when we issue a table.loadTable action, we need the program to move to the next step instead of waiting for that load to finish. Just submitting our CAS actions to different sessions will not, by itself, cause them to run asynchronously. We need to specify the ASYNC CAS Action option on any action we want to run asynchronously. Without the option, the program will wait for the CAS action to finish and not move on until it does.   Below we see some CASL code that calls two CAS actions asynchronously. Each action runs in its own session and each has the ASYNC option specified. Note that the ASYNC option requires a result name ("sess1" and "sess2" below). We'll see how this is utilized in the next section.     Two CAS Actions Coded to Run in Parallel   Puzzle Piece #3: Wait for It! The final thing we need is a mechanism to make the program wait for the asynchronous actions to complete and to return the results of the asynchronous CAS actions to the CASL program. For example, we'd need to know when our (parallel) table loads finished, and if they finished successfully, before we could build our STAR schema view on top of them.   The WAIT_FOR_NEXT_ACTION CASL function gives us this capability. It both waits for the referenced CAS action to finish and provides a mechanism to retrieve the CAS action status and other information.   The asynchronous CAS actions are referenced by the result name ("sess1" and "sess2") on the ASYNC option. The status and other information from the asynchronous CAS action execution is retrieved by referencing the name ("job1" and "job2") assigned to each of the WAIT_FOR_NEXT_ACTION calls.      Using the WAIT_FOR_NEXT_JOB function   Putting it all Together Below is the complete program whose pieces are shown above, including some data manipulation I did to increase the size of the input table so that the example would run longer making parallel execution easier to see.   cas casSession1 sessopts=(metrics=true); cas casSession2 sessopts=(metrics=true); caslib _all_ assign; data visual.mg20 (promote=yes); set visual.mega_corp visual.mega_corp visual.mega_corp visual.mega_corp visual.mega_corp visual.mega_corp visual.mega_corp visual.mega_corp visual.mega_corp visual.mega_corp visual.mega_corp visual.mega_corp visual.mega_corp visual.mega_corp visual.mega_corp visual.mega_corp visual.mega_corp visual.mega_corp visual.mega_corp visual.mega_corp; run; proc cas ; simple.groupByInfo session="casSession1" result=r status=s async="sess1"/ table={caslib="visual",name="mg20", groupBy={"dayofweek","facility","product","unit"}} casOut={caslib="visual",name="mc_gbinfo1",replace=true,replication=0} includeDuplicates=false groupbylimit=20000 details=true ; run ; simple.groupByInfo session="casSession2" result=r status=s async="sess2"/ table={caslib="visual",name="mg20", groupBy={"dayofweek","facility","product","unit"}} casOut={caslib="visual",name="mc_gbinfo2",replace=true,replication=0} includeDuplicates=false groupbylimit=20000 details=true ; run; job1=wait_for_next_action("sess1"); job2=wait_for_next_action("sess2"); print job1; print job2; print job1.session; print job1.status.severity; quit ; cas casSession1 terminate; cas casSession2 terminate; . Examining the log, we can see how to retrieve the execution information (e.g. Print Job1, Print Job1.status.serverity, etc.) as well as proof that the CAS actions ran in parallel by comparing the real time values. .      Log Highlights   More to Come In future posts, we'll dive into iteratively running parallel CAS actions as well as running more complex CASL programs with parallel execution. .> Find more articles from SAS Global Enablement and Learning here. ... View more

We've covered CASL Result variables and the CASL _Perf variable. So let's move on to the CASL _Status variable. Like the _Perf variable, the _Status variable is set/updated after each CAS Action execution, and, like the _Perf variable, the _Status variable is a CASL dictionary. It includes the following attributes:   severity reason status statusCode The severity tells us whether or not the CAS action was successful, 0 for success, 1 for warning, and 2 for error. While the other attributes add detail about the status. See here for the documentation. .   Simple _Status Example Looking at a simple example, we see we can print _Status just like we printed _Perf in this previous post. You can also give the _Status variable a different name using the Status= CAS action option ('status=s' in the example). This can be useful if you want to create a complex condition on the status of more than one CAS action.     A Simple Example of _Status Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.     Conditional Execution based on _Status Using CASL conditional programming, CAS Action Status information can help give CASL scripts fault-handling capabilities. For example, below we see a simple script that checks the table load status before running some analytics.     Simple Conditional CASL _Status Code   Want More CASL? For more on CASL programming, see the following links.    CASL: It’s like Base SAS with the MACRO Built-In CASL: CAS Action Result Variables CAS Action Computed Columns _Perf: CAS Action Performance Metrics "CAS Stored Process" with my Favorite Action Hero runCasl CASL Reference Find more articles from SAS Global Enablement and Learning here. ... View more

Are you a FULLSTIMER kind of coder? Maybe you like to analyze your SASTRACE thoroughly. Well then you'll probably be interested in the _Perf automatic CASL variable.   _Perf is a CASL Dictionary variable that contains performance metrics about the previously run CAS action. It gets automatically created/updated any time a CAS Action is run. As a dictionary variable it includes an array of key-value pairs including:   Elapsed Time CPU User Time CPU System Time Data Movement Time Total System Memory System Nodes Memory Memory, OS Memory, System These metrics help to better understand the CAS action performance on the CAS environment (e.g. CPU User Time) as well as the environment itself (e.g. System Nodes). It is important to remember that these metrics relate to the CAS environment and will not equate to the performance metrics issued by the SAS client that submits the CASL to CAS.   A Simple Example Here we compare the performance metrics of two similar simple.summary calls. Both run on the same input table but the second requests significantly more groupings. Looking at the printed _Perf metrics, we can see that the 2nd call requires considerably more CPU processing time than the first. It also requires more memory as well.     Simple Example using _Perf   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Going Further _Perf provides many of the same metrics that the METRICS=TRUE CAS session option provides. However, it can be applied at the CAS action level instead of the entire session, and, as a CASL variable, it can be manipulated programmatically for load testing scripts and other purposes. METRICS=TRUE provides additional metrics on data movement between the CAS worker nodes. While _Perf provides additional breakdowns in processing time and memory metrics. For additional information on data movement in CAS, see section 9 of this article. For a better understanding of user time vs system time and other cpu time considerations, see here.   ... View more