When multiple SAS Studio developers are responsible for maintaining different components of a set of interdependent SAS Studio flows and SAS programs how can you manage updates so that the developers can modify and test changes without affecting the ongoing use of these flows and SAS programs by end-users in a production environment? An answer is SAS Studio Git integration and I'll show you here an 8 step recipe for how to do this.   As we jump in to see how this works, let me outline a very simple scenario as context for this work. Suppose this SAS Studio flow shown below is maintained by a user I'll call Alex. It joins two source tables to create a target table called OrderAnalysis.     Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Suppose also there is a related program, shown below, for which a user called Fiona is responsible. It uses the OrderAnalysis table produced by Alex's flow as source table to generates summary reports.     The current flow and program are in production as a project we will call ETL and Reporting and the project is managed by Sarah. Any updates by either Alex or Fiona to the individual components they are responsible must not affect their dependencies in the current production version. Any changes to the flow or the program need to be tested to verify that no dependencies have been compromised. It is Sarah's responsibility to push changes to the production project once the tests have been verified.   Using SAS Studio's Git integration Alex and Fiona can independently update development versions of each of their content pieces and conduct tests to confirm that no project dependencies have been disrupted, all without impacting the production project. Sarah can then push updates to the production project with a simple push of a button. Here I will step through how Sarah, Alex, and Fiona can collaborate to accomplish this.   Step 1: Sarah creates a GitLab project   To begin, Sarah will create an account at gitlab.com, if she does not already have one. It's easy and free. GitLab will be used for this scenario, but SAS Studio works with other Git servers including GitHub, BitBucket and others in the same way. Once logged in to her GitLab account, she can click the New project button on her account home page and select Create a blank project. Let's call this project Production. She can leave defaults for the rest of the options and click Create project.     The project called Production with a single main branch is added on the GitLab host with nothing in it but a readme file. Sarah can now clone this project to a local SAS Studio Git repository, copy the existing ETL and Reporting production content to the local repository and then push the content to the remote repository. To prepare for that Sarah will gather two pieces of information. She will need the URL for the Production repository stored on the Git server and she will need to create and copy access tokens to allow access to the repository. To find the URL, (1) click Code in the Production project and (2) copy the value in the Clone with HTTPS field.     Note: For this post, HTTPS is used for cloning the repository to SAS Studio. SSH is also supported.   Step 2: Sarah creates two Access Tokens for the project   Next, Sarah will create two access tokens. The first will be used by Sarah herself and defined to allow her full access to push updated to the main branch of the Production repository. The second access token will be used by Alex and Fiona and will be defined for read access to the main branch and full access to a other branches of the Production repository. Alex and Fiona will be able to add new features in a feature branch and only once tested and approved, Sarah will then be able to push those updates from the feature branch to the main branch.   In the left side menu in GitLab she selects (1) Settings and then(2)  Access Tokens.     In the Project Token Access screen (not shown), she clicks the Add new token button. She'll chose Ownertoken as the name, an expiration date sometime in the future, and Owner will be selected for the role. This will be the access token Sarah will use to connect from SAS Studio and the Owner role will allow her to push changes in the SAS Studio content to the main branch of the repository. She will also select api under Selected Scopes.     Next she click Create project access token at the bottom of the screen. At this point it is necessary to click the copy icon to copy the access token and save it for later use in SAS Studio. It will not be accessible for copying once you leave this screen.     Sarah clicks Add new token again a second time, this time creating a second token that she names DeveloperToken, assigns an expiry date in the future, a role of Developer, and api as the selected scope. She copies the access token as before and save to the text file. Alex and Fiano will access the project with the Developer role and thus be able to create feature branches and push changes to those branches, but they won't be able to push changes themselves to the main branch.   NOTE: For each user that will access the repository on the Git server Sarah will also need to add them, or a group they are part of, to the project membership. For details click here.   Step 3: Sarah clones the GitLab project to a SAS Studio folder and adds SAS Studio content to the project   Back in SAS Studio, Sarah creates an empty folder and clone the Gitlab Production repository to that empty folder. Git repositories can only be cloned to empty folders. Once the folder is cloned a a local repository, Sarah can copy the project content to the repository folder. Let's call the folder called aa_Production.     Sarah will (1) click the Git Repositories section of the navigation pane and then (2) click Clone Repository.     She copies the Repository URL that was copied earlier in step 1 above into the Repository field and for Server location clicks the folder icon and navigates to the aa_Production folder. In the Profile Create new is selected for the Profile     Once Create new is selected a second window opens to define a profile. Sarah will select the HTTPS tab as the method type RepoOwner for the profile name. The username and email corresponds to a GitLab.com user account and she enters her own. Users will require a Gitlab account to access the repository.     When Sarah clicks OK, the repository shows in the Git Repositories section.     In the Explorer window, the aa_Production folder is displayed with a repository icon. You may need to refresh a higher level folder above aa_Production to see the icon change.     Sarah can now copy the contents of aa_Finalized into the aa_Production repository folder, return to the Repositories section of the navigate pane, double click the aa_Production repository to open it up and see in the Commit tab the ETLFlow and the OrderAnalysis SAS program that have been staged in the local repository. To push these changes to the remote repository, Sarah can (1) mark all the files as staged, (2) add a commit message, (3) click Commit, then (4) Push the changes to the main branch of the remote repository.     The project at gitlab.com now shows the newly pushed content.     Step 4: Sarah creates a development branch in the Git project   Sarah will now add a development branch to the repository called featuredev for Alex and Fiona to use. Their updates will be stored separate from the main production branch. To do this, Sarah can navigate to the Git Repositories section in SAS Studio, open the aa_Production repository, click the History tab, right-click on one of the commit messages, and select Create new branch.     In a subsequent screen (not shown) she types featuredev as the branch name. The new branch is added and by default is set as the current branch. Any changes to repository content will be made in the branch that is current.     Note: At any time, you can click Current branch and then select a different branch to make it the current branch:     Before handing over the development work in the repository to Alex and Fiona, Sarah will update the code in the project that defines the library location for the target data produced in the project because any changes to the target table created by the ETL flow during development must not impact the target table used in the production application. So, Sarah will add a Server folder in SAS Studio called aa_TargetDev and she will add some macro code to the SAS Program step in the flow and to the OrderAnalysis program in place of the original LIBNAME statement for the target library.     This program will point the target library to the aa_TargetDev folder when the user logged into SAS studio is Alex, Fiona, or Sarah. Otherwise the aa_TargetProd production folder is used for the target library. (NOTE: Alex, Fiona, and Sarah will also now each need a separate login account for SAS Studio if they need to use the production project content).    To commit and push the featuredev branch and it's updated content to the remote repository, Sarah will click the Commit tab in the aa_Production repository, and, as described earlier she will  (1) mark all the files as staged, (2) add a commit message, (3) click Commit, then (4) Push the changes to the featuredev branch of the remote repository.     Even if Sarah did not have a need to update the target library code, it is necessary for Sarah to push the newly created featuredev branch to the remote repository. Otherwise the featuredev branch will not exist in the remote repository and Alex and Fiona will not be able to access it when they connect to the aa_Production repository.   Sarah's work is done for now.   Step 5: Alex and Fiona clone the GitLab project to SAS Studio and checkout the featuredev branch   Alex and Fiona can now each login to SAS Studio, each create a folder called (Alex could call his aa_DevAlex and Fiona could call hers aa_Fiona), and each of them use the same method Sarah did to clone the aa_Production repository, except they will clone them to their own unique folder, use their own gitlab account name and email in their Git Profile, and they will both use the DeveloperToken access token for the password (they can get that token from Sarah).   To checkout the featuredev branch, they will then each access the Git Repositories section in SAS Studio from the Navigate pane and open their Git repository. In the screen capture below for Alex, he has opened his aa_DevAlex repository. Initially main will be the active branch. To active the featuredev branch, Alex will (1) click the History tab, right click on the commit message that shows the origin/featuredev branch, and then (3) select Checkout branch --> origin/featuredev.     Step 6: Alex and Fiona develop independently in their own local clones of the featuredev repository and push changes to the remote featuredev    Alex and Fiona are free to develop and test each of their project components in their own separate local repository clones of the original content. When they have completed updates they can use the Commit tab as describe earlier to mark files as staged, commit them and push them. The push will go to the featuredev branch in the remote repository on the Git server. The Git server will thus contain all changes pushed by both Alex and Fiona.   Note: If both developers make changes to the same file the Git server will detect conflicts when the second user attempts to push. The conflict will need to be resolved before the push can proceed.   Step 7: Sarah pulls completed changes in the featuredev branch to her local repository and tests    Once all updates by Alex and Fiona are ready to review Sarah can update the featuredev branch in her local repository by opening her aa_Production repository, making sure that the featuredev branch is the current branch and clicking on Pull featuredev.     The updated content is now pulled into the SAS Studio environment and Sarah can proceed to execute the flow and the SAS program and verify that their are no errors and results are as expected.   Step 8: Sarah pushes changes to the main branch   Sarah is ready to push the updates to the main branch which stores the production content. To do this in SAS Studio she (1) clicks on Current branch and (2) selects main to make the main branch the current repository.     Next, she will (1) click the History tab, (2) right-click the most recent commit message for the featuredev branch, and (3) select Merge into main --> featuredev.     This merges the updates into the main branch of the local repository. This updates the production content in SAS Studio for all users of the project. The main branch of the remote repository also needs to be updated to be in sync with the current production content in SAS Viya. Sarah can accomplish that by clicking Push main.     Note: As alternative method for step 8, Sarah can login to her account at gitlab.com and use the tools in the gitlab.com project interface to push the featuredev branch in the project on the Git server to the main branch. Subsequently, Sarah would return to SAS Studio and click on Pull main to pull the changes from the remote main branch to the local main branch.   Additional resources   Youtube: Git and SAS: A Match Made in (SAS) Studio SAS Documentation: Understanding Git Integration in SAS Studio SAS Communities Library: SAS Explore 2023 – SAS Studio Git Integration Hands-On Session     Find more articles from SAS Global Enablement and Learning here. ... View more

  SAS Studio flows are really starting to come on strong with support for a variety of Data Management operations with the recent addition of SAS Studio Engineer steps. This blog explains a design for a no-code SAS Studio flow that fully supports automation to update records in dimension tables and in related fact tables while implementing Slowly Changing Dimensions (SCD) for preservation of historical dimension records. Background information on slowly changing dimensions and the Implement SCD step in SAS Studio The Implement SCD allows you to save historical records for a given business entity in a dimensional table. Saving current and historical records in dimensional tables is referred to as slowly changing dimensions. As an example, in a customer dimension table you may want to update a customer's address but maintain a record for the customer's old address. This could be useful if you have a related CUSTOMERORDERS fact table and you want to relate older orders to the old customer address and new orders to the new address.   To store both historical and current records for a dimensional entity (such as customers) you can no longer use the unique identifier for each customer (let's call the identifier CUSTOMER_ID) as the primary key in the dimension table related to foreign keys in fact tables. The CUSTOMER_ID will remain in the table to identify unique customers but you also need to generate and store a unique surrogate key that is unique for each record in the slowly changing dimension table. You will also need to include that unique surrogate key as the foreign key in the records of related fact tables.   In her post earlier this year MK Queen showed how to use the Implement SCD step in a SAS Studio flow in order to create and maintain such a dimensional table structure. Please refer to her blog for background details on using the Implement SCD step. Here I will extend that discussion to present a design for a SAS Studio flow that: uses the Implement SCD step includes additional steps to maintain the primary- key/foreign-key relationship between an SCD dimensional table and a related fact table as both dimensional records and fact records are added over time. Overview of the flow design This is a view of the flow design with numbers superimposed to identify each item in the flow:   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Before going into the details let's briefly overview the flow design. The numbers below correspond to the numbered items in the flow diagram displayed above: CustomerchangesM1 is a comma separated text files containing either new customers for month1 or existing customers with changes effective this month. These record will be used to update a slowly changing customer dimension table called CUSTOMERDIMSCD. The Import step imports the data in CustomerchangesM1 to a target table. The target table for the import step is an Oracle table called CUSTOMERCHANGES. If the Oracle table already exists, it will be replaced with the data from the customerchange1 text file. Oracle tables are used in this flow because the Implement SCD step supports source and target tables from Oracle as well as a few other databases (and does not support SAS datasets). Using Oracle tables in each step maximizes in-database across all steps in the flow. The Implement SCD step uses the CUSTOMERCHANGES Oracle table to update an Oracle table called CUSTOMERDIMSCD – a customer dimension table that retains customer history. The target table is represented by the circular part of the Implement SCD step icon and does not show up as a separate object in the flow. The CUSTOMERDIMSCD table contains the customer_ID business key for each customer and also a generated surrogate key called CUSTOMER_HISTORYID as a unique primary key value. Orders1 is a comma separated text files containing orders received during month 1 for customers identified with the CUSTOMER_ID business key. These records need to be appended to an OrderFact table. This Import step imports the data in orders1 to a target table. The target table for this import step is an Oracle table called RECENTORDERS. A Query step uses a join to match each order record in the RECENTORDERS table with the current record for the matching CUSTOMER_ID in the CUSTOMERDIMSCD table and adds the CUSTOMER_HISTORYID key for that matching record to the RECENTORDER records. RECENTWITHHISTID is the target table produced by the join. It contains each order from RECENTORDERS plus the foreign key CUSTOMER_HISTORYID values that match the corresponding primary key in CUSTOMERDIMSCD. A Merge Table step is used to append the new recent order records from RECENTWITHHISTID to a target order fact table called CUSTORDERSCD. This flow is designed so that in each new month (or any time period), as new customerchanges and/or new orders are available as source data in item 1 and/or item 5, the flow can be re-executed to update the customer dimension table (CUSTOMERDIMSCD) and the order fact table (CUSTORDERSCD). Also, if the flow is unintentionally executed for the same source records, no duplicate records will be unintentionally added to the dimension or fact tables. Apart from the source files (items 1 and 5 in the flow) all other tables in the flow are Oracle tables for two reasons: the Implement SCD step and the Merge Table step require that both source and target tables must be stored in a supported database and Oracle is one such supported database when the source and target tables are in the same database, the Implement SCD step, the Merge Table step, and the Join step are all execute fully in-database without any data being returned to SAS and without any data being processed by SAS. This minimized network transport of data, speeds the time of execution, and maximizes overall efficiency of the process. The design presented here includes a single dimension table but the design could be repeated for additional dimensional tables in star-schema relational models. Details on the flow design Let's look at some of the details for each numbered item and follow the process that occurs as the flow is executed for a sample set of incoming source records and existing records in the CUSTOMERDIMSCD table. A very small number of records are used in these examples to facilitate illustrating the process, although the method discussed supports any volume of data. Item 1 The customerchangesM1.csv file contains the following 2 records representing customer updates for the current month (month1):     These are updates that must be applied in the CUSTOMERDIMSCD table that currently contains the following records:     The first record in customerchangesM1.csv is an update for CUSTOMERID=1 in the CUSTOMERDIMSCD table and the second record in customerchangesM1.csv is a new customer to be appended to the CUSTOMERDIMSCD table. Item 2 The Import step accesses the customerchangesM1.csv file and parses it into fields using the comma to delimit records and maps them to columns in the target table. Item 3 The target table for the Import is defined as an Oracle table by selecting ORALIB, which is a SAS library defined to connect to the Oracle database. The table name is CUSTOMERCHANGES. If this table already exists, it will be replaced with the data from the current source for the import task.    Item 4 The Implement SCD step used the CUSTOMERDIMCHANGES Oracle table produced by the Import step as a source table and the CUSTOMERDIMSCD table is defined I the ImplementSCD properties as the target table:     The Implement SCD step is configured to maintain historical records as the CUSTOMERDIMSCD table is updated. I will describe this briefly here and for more detail see the post by MK Queen.   CUSTOMERID is defined as the business key to match source and target columns for updates:     Three columns in are identified for historical change tracking:     The columns in the CUSTOMERDIMSCD target table used for change tracking are identified:     The surrogate key that identifies unique records in CUSTOMERDIMSCD is identified:     When the Implement SCD step is executed, the CUSTOMERDIMSCD target table is updated:     The prior current record for CUSTOMERID=1 is no longer the current record (CURRENT_FLAG=0) and a new current record has been appended (CURRENT_FLAG=1). A record has also been added for CUSTOMER_ID=5. This customer did not exist in the table before the update. Item 5 OrdersM1 is a comma delimited text file contains the following 2 records representing orders in the current month (month1):     The CUSTOMERID business key matches the CUSTOMERID in the CUSTOMERDIMSCD. These are new orders that need to be added to the ORDERFACTSCD table and when we add them we need to include the CUSTOMER_HISTORYID primary key to relate them to the matching current record for the same customer in the CUSTOMERDIMSCD table. Item 6 The Import step accesses the ordersM1.csv file and parses it into fields using the comma to delimit records and maps them to columns in the target table. Item 7 The target table for the Import is defined as an Oracle table by selecting ORALIB, which is a SAS library defined to connect to the Oracle database. The table name is RECENTORDERS. If this table already exists, it will be replaced with the data from the current source for the import task.    Item 8 The join step does a right join of CUSTOMERDIMSCD with the RECENTORDERS table using CUSTOMER_ID as the join key:     To make sure that the RECENTORDERS record match with only current customer records in CUSTOMERDIMSCD (and not historical records) a filter is added to the join to include only records from CUSTOMERDIMSCD where CURRENT_FLAG=1:     All the columns from RECENTORDERS are selected as well as the CUSTOMER_HISTORYID from the CUSTOMERORDERSCD table:     Item 9 The target table for the join is saved in Oracle by specifying the Oracle library (ORALIB). The table is called RECENTWITHHISTID:     When the join is executed for the current CUSTOMERDIMSCD and RECENTORDERS tables the target RECENTWITHHISTID table contains these records:     Item 10 Records in the RECENTWITHHIST table can now be added to the CUSTOMORDSCD Oracle table using the Merge Table step. More details for the Merge Table step can be found in the video Data Integration with the Merge Table Step posted by Grace Barnhill. In brief, the Merge step using an SQL Merge method to update rows in a target table based on a source table using key column matching. Rows from the source can be used to update matching target rows and/or rows in the source that do not match rows in the target can be appended to the target.   The target table for the Merge Table step will be an Oracle table in the ORALIB library called CUSTORDERSCD:     We will match source record with target records on the Key column ORDERID:     We will only Insert rows that DO NOT MATCH and we will add all column values from the source to the target:     We do not update any rows when a matching ORDERID is found. We do not need to update rows because we only receive new orders from the source:     Configured this way, the Merge Tables step assures that no duplicate orders will be added to the CUSTORDERSCD table, even if we execute the flow more than once for the same source data for new orders. Automation: re-executing the flow when new source data is available The source files (items 1 and 5 in the flow diagram) are File objects. In the properties for those items you can change the file that they access by clicking the folder icon to the right of the File location field:     You can also update the same source files external to the flow and re-run the flow without changing the source files used in the flow. Such an approach would allow you to schedule the SAS Studio flows to execute automatically as source data is updated. For documentation on scheduling SAS Studio flows see Scheduling a Program, Task, Query, or Flow as a Job and Using the Scheduled Jobs Window.   In this flow design you can change one or both file sources independently and re-execute the flow because the Implement SCD step will only update existing CUSTOMERDIMSCD records when a change in a change-tracked column occurs and the Merge Step will only append new orders to the CUSTORDERSCD table. If the flow is inadvertently executed for any source data that has already been processed in an earlier run of the flow, those records will not affect the CUSTOMERDIMSCD or CUSTORDERSCD tables.   ... View more

To process data in CAS, data from CASLIB data sources are first copied into a memory resident CAS tables (see CAS Fundamentals: Data Lifecycle). When the size of the data source is large it is best load only the data that you require for CAS processing to limit I/O, network transport of data, and memory resources.   The LOAD statement in PROC CASUTIL and the LoadTable CAS action provide WHERE= and VAR= options to limit rows and columns to load and these are surely great options to use to your advantage.   But that's not what I'm talking about here. I'm talking about how you can use FEDSQL to execute a database query on a CASLIB that uses database tables as it's data source. IF (and only if) your CASLIB is a database CASLIB, you can execute a database query (perhaps a summary query, perhaps a join, any valid query) directly on the data source BEFORE it is loaded into CAS and only the result of that query is loaded to CAS.  An earlier post by Nicolas Roberts  covered the implicit pass-through method and I'll say some more about that here. In addition I'll show you a more recently supported explicit pass-through method.  I will show examples of these techniques using a CASLIB that connects to a postgres database, but these methods works for many other database CASLIBs as well.   Both implicit and explicit pass-through with CAS are accomplished with PROC FEDSQL. Implicit pass-through involves writing a query using FEDSQL syntax. The CASLIB data connector engine translates this on your behalf into a native database query, sends that query to the database for execution, and returns the result to CAS where further processing is done, if necessary, to complete the query request. With explicit pass-through, you write a database  query using the native syntax of the database and embed that query within your FEDSQL code. The native query is submitted directly to the database for execution and the results are returned to CAS.   FEDSQL Implicit SQL pass-through example   In the example below, after starting a CAS session, the CASLIB called caspgr connects to a Postgres database. In PROC FEDSQL the SESSREF= option will direct the procedure to execute in CAS, rather than the SAS compute server. The CREATE table statement will create a new in-memory CAS table called revenuesummary and it will be populated with the results of the SELECT query. That query names the "orders_large" table from the Postgres caspgr CASLIB. The syntax used is not native to Postgres and is written entirely in FEDSQL syntax.   cas mysession; CASLIB caspgr datasource=(srctype="postgres" database=DCON server="server.demo.sas.com" port=30432 user=student password='Metadata0' ) libref=caspgr; proc fedsql sessref=mysession; create table caspgr.revenuesummary{options replace=true} as select order_type, sum(total_sale) as revenue from caspgr."orders_large" where order_date < date'2015-01-01' group by order_type; quit;   If a caspgr.orders_large table does not exist as a CAS table in CAS memory, the select query will be converted as much as possible into a native Postgres query, sent to Postgres to execute, and the results will be returned to populate the CAS table caspgr.revenuesummary. Thus,  filtered and summarized query result is all that is returned to CAS. (Notice that when naming the table "orders_large" on the FROM clause, the name must be quoted when naming you are accessing from the caslib data source. This is necessary for Postgres tables, but not for other types of database caslibs.)   Let's check the SAS log to compare performance for the implicit pass-through method (method A)  versus pre-loading the orders_large table into memory and running the same query on the in-memory table (method B). The orders_large Postgres table contains 2,000,000 rows and 31 columns and the code was run in a small-scale development environment with a single CAS node.   Method A: Implicit SQL Pass-Through     84 proc fedsql sessref=mysession ; 85 create table caspgr.revenuesummary{options replace=true} as 86 select order_type, sum(total_sale) as revenue 87 from caspgr."large_orders" 88 where order_date < date'2015-01-01' 89 group by order_type; NOTE: The SQL statement was fully offloaded to the underlying data source via full pass-through 90 quit; NOTE: PROCEDURE FEDSQL used (Total process time): real time 0.32 seconds cpu time 0.02 seconds   Method B: Load Database Table to CAS and Query   92 proc casutil; NOTE: The UUID '399c10ee-2aed-ae4e-89e2-c5fbd28d203a' is connected using session MYSESSION. 93 load casdata="large_orders" inCASLIB="caspgr" outCASLIB="caspgr" casout="large_orders" replace; NOTE: Performing serial LoadTable action using SAS Data Connector to PostgreSQL. NOTE: Cloud Analytic Services made the external data from large_orders available as table LARGE_ORDERS in CASLIB caspgr. NOTE: The Cloud Analytic Services server processed the request in 38.5743 seconds. 94 run; 95 quit; NOTE: PROCEDURE CASUTIL used (Total process time): real time 38.59 seconds cpu time 0.08 seconds 96 proc fedsql sessref=mysession ; 97 create table caspgr.revenuesummary{options replace=true} as 98 select order_type, sum(total_sale) as revenue 99 from caspgr."large_orders" 100 where order_date < date'2015-01-01' 101 group by order_type; NOTE: Table REVENUESUMMARY was created in CASLIB CASPGR with 3 rows returned. 102 quit; NOTE: PROCEDURE FEDSQL used (Total process time): real time 1.06 seconds cpu time 0.02 seconds   A note following the first PROC FEDSQL (method A) indicates that the "SQL statement was fully offloaded to the underlying data source via full pass-through" and the entire process to execute that query and load the revenuesummary table completes in 0.32 seconds of real time. In contrast, in method 2, when a PROC CASUTIL LOAD statement is used to first load the entire orders_large source table, that process requires  38.59 seconds. The final PROC FEDSQL step re-creates the revenuesummary table by executing the same FEDSQL query on the pre-loaded orders_large CAS table, which takes 1.06 seconds.   By querying the data source with implicit pass-through you can execute processes more quickly, take advantage of the database capacity to process data natively, reduce the volume of data you must transport across the network, and reduce the memory required in CAS to create the CAS table.   Sometimes the implicit pass-through method only converts some of the query elements for in-database execution. For instance, it may convert the WHERE clause, but not perform the summarization. Sometimes it does no more than simply generate a database query to return ALL the data from the data source and all the rest is done in CAS. In the query shown in the log below, the original query (see method A above) is modified to use a KURTOSIS function in place of the  SUM function. KURTOSIS isn't  converted into an equivalent summary function in Postgres and the log no longer shows the message that the SQL statement was fully offloaded to the database. In this case, only the WHERE clause is offloaded to the database but many more rows were returned to CAS and CAS then completes the summarization to calculate the KURTOSIS. The real time to process the second query was thus longer at 2.31 seconds versus 0.32 seconds when the SUM function was used.   87 proc fedsql sessref=mysession ; 88 create table caspgr.revenuekurtosis{options replace=true} as 89 select order_type, kurtosis(total_sale) as kurtosis 90 from caspgr."large_orders" 91 where order_date < date'2015-01-01' 92 group by order_type; NOTE: Table REVENUEKURTOSIS was created in CASLIB CASPGR with 3 rows returned. 93 quit; NOTE: PROCEDURE FEDSQL used (Total process time): real time 2.81 seconds cpu time 0.03 seconds   During code development, if you need to evaluate the extent of database pushdown of implicit queries you can use drivertrace options on the CASLIB statement.   FEDSQL Explicit SQL pass-through example   An alternative approach to query database tables for loading data to CAS is to submit native database queries directly with explicit SQL pass-through. This ensures that the query you write in the native language will execute entirely in-database and you can take advantage of existing native code and all available functionality of the database. For the example below native Postgres SQL that leverages the databases capability to manipulate JSON data is used with explicit SQL pass-through to parse a JSON column to extract and summarize revenue from customer order information. Only the summary result is returned to CAS making it available for further analysis within CAS.   The Postgres table contains an integer column called orderid and a column of the JSON data type called orderinfo. Orderinfo stores information about customer orders.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Let's focus first on how I construct a native Postgres query to extract the information I want to bring to CAS. The query below uses JSON operators to extract the values for the customer name, customer state, product, and qty object keys within the orderinfo JSON column of the jsonorders table.   select orderinfo -> 'customer' ->>'name' as customername, orderinfo -> 'customer' ->>'state' as customerstate, orderinfo -> 'items' ->> 'product' as product, orderinfo -> 'items' ->> 'qty' as quantity from jsonorders   This result yields:     We will use the first query above as sub-select for an outer query to summarize the quantity ordered by customerstate. Here is the complete native postgres query:   select customerstate, min(quantity) as minquantity, max(quantity) as maxquantity, sum(quantity) as sumquantity from (select orderinfo -> 'customer' ->>'state' as customerstate, orderinfo -> 'items' ->> 'product' as product, cast(orderinfo -> 'items' ->> 'qty' as integer) as quantity from jsonorders) t group by customerstate   Which yields the final summary result:     With the native postgres query in hand we can now include this in FedSQL pass-through code so that it is sent to execute in Postgres and so that the summary result is returned to CAS:   cas mysession; CASLIB caspgr datasource=(srctype="postgres" database=DCON server="server.demo.sas.com" port=30432 user=student password='Metadata0' ) libref=caspgr; proc fedsql sessref=mysession; create table caspgr.StateSalesSummary as select customerstate, minquantity, maxquantity, sumquantity from connection to caspgr (select customerstate, min(quantity) as minquantity, max(quantity) as maxquantity, sum(quantity) as sumquantity from (select orderinfo -> 'customer' ->>'state' as customerstate, orderinfo -> 'items' ->> 'product' as product, cast(orderinfo -> 'items' ->> 'qty' as integer) as quantity from jsonorders) t group by customerstate; quit;   Let's examine this step by step:    The CAS statement starts a CAS session named mysession.  A CASLIB statement defines a CASLIB called caspgr that connects to the postgres database.  A PROC FEDSQL statement with the SESSREF option starts PROC FEDSQL in the CAS session.  A CREATE TABLE statement creates a CAS table called caspgr.StateSalesSummary.  A SELECT clause in the create table statement will select data to populate the table. The FROM clause for the SELECT, indicates to read data FROM CONNECTION TO CASPGR. CASPGR is the CASLIB connection to Postgres defined in step 2.  The FROM CONNECTION TO CASPGR is followed by our native Postgres query enclosed in a set of parentheses. This native query will be executed by Postgres and the results will be returned to CAS and read by the SELECT clause from step 5 above the results will be stored in the CAS table caspgr.StateSalesSummary.   Any valid native database query can be used in this way for most database caslibs.   Both implicit and explicit pass-through capabilities of FEDSQL allow us to take advantage of database processing of data before returning results to CAS. Implicit pas-through allows us to write FedSQL syntax without the requirement to conform to the specific syntax of the database. With implict pass-through, logging methods available to evaluate how much processing was passed to the database but it is possible that some or most of the FedSQL syntax is not translated to the database and more data will be returned to CAS for processing. With explicit pass-through methods, since you write the native database code yourself, you have full knowledge and control over what executes in database and what is returned to CAS. It also allows you to leverage database methods that may be difficult to reproduce within SAS.   Further Reading   SAS Documentation: FEDSQL Implicit and Explicit Pass-Through Facilities, SAS Documentation: FedSQL for CAS Reference     Find more articles from SAS Global Enablement and Learning here. ... View more

Watch this Ask the Expert session to learn about SAS Viya architecture and programming methods for data access and management.                                    Watch the webinar This webinar is perfect for experienced SAS programmers interested in learning more about Viya and Viya programming including: How your SAS Compute Server session and your SAS Cloud Analytics Server (CAS) session interact when you submit SAS programs  Programming methods to access and process data sources effectively. Data movement and data processing in Viya.  The questions from the Q&A segment held at the end of the webinar are listed below and the slides from the webinar are attached along with the demo code I showed during the presentation.   Can you read Oracle tables or other database tables in SAS Viya like we can in SAS 9? Yes. You can use SAS/ACCESS LIBNAME statements and SQL passthrough that execute on the SAS Compute Server in SAS Viya to connect to databases and they work identically as they do in SAS9. Additionally, you can use CASLIBs to establish a CAS server connection to databases. CASLIBs are the best way to move data from database tables into CAS memory as CAS tables and to save CAS tables on disk as database tables. Here is a link on documentation for using CASLIBs with databases. CAS Data Connector Documentation   Why is there both PROC SQL and PROC FEDSQL? What is the difference? PROC FEDSQL was created more recently than PROC SQL. The FEDSQL syntax has fewer SAS specific elements, making it more likely that your SQL code can be translated into native SQL code and pushed down into the database for execution when using database tables rather than pulling data from database tables back to SAS for processing. PROC SQL can also do this in many cases, but it depends more on how you write the SQL queries. FEDSQL also supports more of the ANSII datatypes including high precision numeric and varchar data types. PROC SQL will convert such datatypes to the SAS numeric or character data types for processing. Further comparison of PROC FEDSQL and PROC SQL can be found in a recent Ask the Expert talk available on demand.   Can I use SAS datasets from SAS 9 in SAS Viya? Yes.   Can I use Enterprise Guide to develop in Viya? Yes. If you have a SAS9 maintenance release 5 (M5) or later, you can use Enterprise Guide rather than SAS Studio. When you do that then the SAS9 session that Enterprise Guide uses replaces the SAS Compute Server role in SAS Viya.   Running in CAS requires memory. What happens when you have hundreds of users and you run out of memory? Or am I misunderstanding? You are correct. You need to think about scalability for CAS and figure out how much infrastructure you will need to support the data volume and number of users. The cloud offers ease of scaling up and down. Also keep in mind you do not need to retain CAS tables in memory when they are not being used. They can be loaded from the disk data sources as needed and unloaded from memory when not in use. New in-memory tables you create can be saved to disk and then unloaded from memory as well.   Can you run batch SAS jobs on the compute server? Yes. Here is a link to the documentation for the Batch Plugin for SAS Viya cli and for SAS Job Execution Web Application     Can you fire up the old "display manager" from the compute server? No, but you CAN start the display manager (also known as the SAS Windowing environment) and use its SAS9 session in place of the SAS Compute Server to interact with CAS. This is so if you have SAS9 maintenance release 5 (M5) or later.   If CASLIB is an in-memory process, what happens if the server node goes down? Will all my work be gone? Yes, if you don’t save your in-memory tables to disk. Every CASLIB has a disk data source location when you can permanently store and an in-memory location where the procedures are accessing the data.   To run an end user session, do we always need to do via the compute server, or can we also create a CAS session directly? SAS Studio is the application or interface we provide in SAS Viya for SAS programmers. Users of other SAS products including SAS Visual Analytics, SAS Model Studio, SAS Data Prep use their own client interface to interact with CAS directly. We also provide technology that allow Python, R, Java, and Lua programmers to interact with CAS directly from their native programming interfaces.   For the load data = option, is the data regular SAS data set or a new format SAS data? It is a regular SAS dataset. It can be any data read via a SAS library, but it if is a base SAS library then it is a regular SAS dataset. The file format for SAS datasets is the same in SAS Viya as it is in SAS9.   Is there an option to only load 1 copy of a dataset into CAS and not load multiple copies into memory? When you load data into CAS it is distributed across all the CAS worker nodes, but it does not create a copy of the entire dataset in each node. It splits the data up into chunks and puts different chunks on each node. It still processes that distributed table as a single table. All the CAS nodes work together as a single system to process the data in parallel on each node. As you load the data it is possible to limit the number of nodes that it writes the data to, but by default it will distribute the data across all the CAS worker nodes.   Are there any options or session operations that we would want to set or turn off when connecting to a CAS Viya session? When you start a CAS session with a CAS statement there are many options that you can set for the session. These include the session encoding (latin1 versus Unicode for instance), language localization options, options for how long to allow the session to run before it times out and more. These options are documented here: CAS session options   Do we need to specifically write in our code to distribute the data into CAS servers? OR, is it done automatically and how do we know up to how much extent the data has been distributed? It’s done automatically. Whenever you load data into CAS, it will distribute it across all the worker nodes. You have options to limit that if you don’t want to use all the worker nodes.   Is load file like PROC Import? Yes, they are similar. Load file= supports the same files types as PROC IMPORT. And you can, in fact, use PROC IMPORT to load data into CAS. When you name the output file you are creating with PROC IMPORT, you can point to a CASLIB.   Can we get copies of the sample code? Yes. It’s attached to this post.   What will be the best way to run SQL from a DB to load into CAS? Typically, our SQLs are several lines long. The best way to do this is to define a CASLIB to connect to the database. We supply what call SAS Data Connectors that allow you to do this. It requires a SAS/ACCESS license for the specific database or databases you need to connect to. Documentation on how to define a CASLIB using these Data Connector is here. Once you have defined the CASLIB, you can use PROC FEDSQL to write a query that reads from your database CASLIB to create an in-memory table. PROC FEDSQL, like PROC SQL supports both implicit and explicit SQL pass-through that will execute in-database and load your database data source into CAS memory. Or, if you simply want to load the entire database table into CAS, you can use PROC CASUTIL and a load CASDATA= statement.   Can the CAS library be a substitute for Work and all Work datasets exist in-memory?  Perhaps with the USER= option? The SAS WORK library datasets are not in-memory tables. They are stored on disk like all datasets in SAS libraries. They are loaded into memory from disk in each DATA or PROC step in which you use them. I would not recommend storing your SAS Compute server WORK tables in CAS memory as a general practice. The reason you want to put tables in CAS is for processing large volumes of data that otherwise are too large to process on the compute server, because you want to join that data with an existing CAS table, or because users of other SAS Viya applications like SAS Visual Analytics, which use the CAS server, need to access that data for their work.   What's the RAM requirement for each "worker" machine? Say I have 5 datasets in 100GB.  Another user may be working on another project with a few large datasets. There are many considerations that go into sizing such requirements. For the most recent release of SAS Viya, here is some documentation you can start with as you go down the path of determining your system requirements: System Requirements. From that documentation note the following statement:  These guidelines do not attempt to account for all ordering scenarios, but instead are intended to illustrate typical software orders. SAS strongly recommends that you consult with a sizing expert to obtain an official hardware recommendation that is based on your requirements. To request sizing expertise, contact your SAS account representative. If you need assistance in determining your SAS account representative, send an email to contactcenter@sas.com.   What is the purpose of Promoting? Given it is already loaded in CAS memory? Promoting makes it available for you to use in another session. It also makes it available to other users on the shared CAS server. You can always re-load the data in each session, but it may be more convenient and quicker to not have to. Also, data administrators within the organization may be managing the data that is made available in memory for other users who may not have the knowledge or permissions to load CAS tables. These may include users of other SAS Viya applications like SAS Visual Analytics.   While promoting a table, what if the same table name exists across the sessions, will it replace the existing table while promoting or will it throw an error? You will get an error if you try to promote an existing global table. You must first drop the existing table from memory explicitly. There is a DROP statement in PROC CASUTIL to do this.   How should one work if data is located in a relational database and this data changes regularly? What are best practices for updating CAS Library for data in a relational database? I would recommend defining a CASLIB to connect to the database. Defining such CASLIBs is found here. Then use PROC CASUTIL and a LOAD CASDATA= to load that table. Load it as an unpromoted session table so that you will need to load it in each session that you use it. Every time it is loaded, you will be loading the current copy of that table from the database. If you are maintaining a long-term CAS session, perhaps for many users, you may need to schedule a daily running script to drop the in-memory table and reload it from the database into the session.   How do you ingest live data into CAS? Is this a streaming data source? You may be able to use SAS Event Stream Processing software. SAS Event Stream Processing can read from a large variety of common streaming sources and emit output events to an in-memory CAS table. SAS Event Stream Processing allows you to process the event stream data in a variety before emitting output events, which can be important if the raw event streams are too large to store. Instead you can filter, summarize, capture certain patterns in the event or perform other operations to pre-process the event streams before sending them to a CAS table.   What is the difference between Server Controller and Session Controller? The distinction between Server and Session in the diagrams used in this presentation is that users launch individual sessions. The servers (both Server Controller and Server Worker) represent the computing hardware, installed software, and general services that support those sessions.   Is there a way of keeping your own version of a table in memory for you to come back to another day if you have closed your CAS session or does it have to be promoted to a global area that everyone to see for it to persist in memory? The way to do this is to use the CASUSER library. Each individual user has their own global library called CASUSER that only they have access to. So, any table you put in CASUSER is only visible to you even when you promote it.   Can one user open more than one session at the same time? Yes. When you use the CAS statement you give your session a label. You could create another CAS session with a different label.   For the use case of LOAD CASDATA; do some easy feature engineering (e.g. Var1/Var2*100) -; SAVE CASDATA:  is PROC FEDSQL the best way to do that?  Or are there any commands/options directly within PROC CASUTIL for creating new variables/columns and executing that entire scenario all in one step (i.e. within a single PROC CASUTIL)? PROC CASUTIL is a utility procedure focused on managing CAS tables and the disk datasets and files for those tables. Loading, unloading, saving and the like at the table level. You would not use it to compute columns or to query data. PROC FEDSQL is a good way to do that, but you can also execute the DATA step and PROC DS2 in CAS, once tables are loaded into memory. There are many other CAS procedures for data management and analysis as well. Here is a link to an overview that describes the functionality of PROC CASUTIL in somewhat more detail: CASUTIL overview.     Recommended Resources: Programming for SAS Viya training course SAS Viya Learn & Support page SAS Hackathon   Want more tips? Be sure to subscribe to the Ask the Expert board to receive follow up Q&A, slides and recordings from other SAS Ask the Expert webinars.   ... View more

. Hi SAS Viya Community, I’m presenting a live “Ask the Expert” webinar on January 14 th , 11 AM – Noon ET entitled SAS ®  Viya ®  Architecture and Data Movement: A SAS ®  Programmers Overview. I will discuss SAS Viya architecture and the benefits of in-memory data and scalable distributed computing. This webinar is perfect for experienced SAS programmers interested in learning more about Viya and Viya programming. I’ll hold a live Q&A session at the end of the webinar. You will learn: How to use SAS DATA steps and procedures in Viya. Programming methods to manage data access. Data movement and data processing in Viya.  Click here to register for the webinar.   Want more tips? Be sure to subscribe to the Ask the Expert board to receive follow up Q&A, slides and recordings from this and other SAS Ask the Expert webinars.   Can't join the live event? You can view this and other Ask the Expert sessions on-demand here.  ... View more

. Hi SAS Programming Community, I’m presenting a live “Ask the Expert” webinar on January 14 th , 11 AM – Noon ET entitled SAS ® Viya ® Architecture and Data Movement: A SAS ® Programmers Overview. I will discuss SAS Viya architecture and the benefits of in-memory data and scalable distributed computing. This webinar is perfect for experienced SAS programmers interested in learning more about Viya and Viya programming. I’ll hold a live Q&A session at the end of the webinar. You will learn: How to use SAS DATA steps and procedures in Viya. Programming methods to manage data access. Data movement and data processing in Viya.  Click here to register for the webinar.   Want more tips? Be sure to subscribe to the Ask the Expert board to receive follow up Q&A, slides and recordings from this and other SAS Ask the Expert webinars.   Can't join the live event? You can view this and other Ask the Expert sessions on-demand here.  ... View more

If you are interested in exploring a variety of new programming and application interface choices in SAS Viya, check out this one hour  Ask the Expert recording     I am attaching here the presentation material, data used in the demonstrations, and the code used in the programming examples. And below are answers to some questions posted during the live presentation.   Q: Does the latest SWAT interface support the Scikit-Learn machine learning library in Python? As far as I know, it was a limitation previously, so wondering if the latest SWAT version has support for it.    A: The scikit-learn procedures will not execute in the distributed SAS Viya environment. In general we are using SWAT to access SAS machine learning algorithms and routines. These models can be compared in Python to the results of scikit-learn models, but the scikit-learn models will be executed using native Python code on the local Python environment.   Q: is the random forest procedure available in SAS/STAT?   A: Yes, PROC FOREST in SAS/STAT will produce random forests if executed in SAS 9.4 (If you execute PROC FOREST on SAS Viya it will create the same random forest model we talked about using CAS actions)   ... View more

Did you miss the live presentation of the Ask the Expert session on SAS® Viya® Architecture and Data Access, Movement, and Processing: A SAS Programmer's Overview. No problem. You can still click here to access and view the on demand recording. A PDF document for the presentation and the demonstration script is also available for download. In this post I am attaching the demo code used and a Q&A for questions received but not answered in the recording.     Here is a Q and A for some outstanding questions that were not provided during the live session: Q: Does SAS Web Report Studio integrate with SAS Viya. A: It does not. However, SAS Visual Analytics provides the equivalent functionality in the SAS Viya environment.   Q: Could you give some examples of what programmers cannot accomplish on SAS Grid but on SAS Viya? Do they share the similar architecture? A: SAS Grid allows you to distribute SAS jobs in an automated way to any number of available SAS servers that are available. This allows you to effectively use any available SAS Server resources to execute SAS programs as soon as a server is available. However, each SAS job executes only on the single SAS server machine that the job is sent to. In SAS Viya, on the other hand, every single process executes in parallel across multiple machine simultaneously where the data itself is distributed across all those machines and the process executes in parallel across all those machines. This allows you to execute each individual process rapidly on large volumes of data.           ... View more