Watch this Ask the Expert session to learn how SAS Viya opens up a world of possibilities for advancing your analytics processes.  Watch the webinar You will learn how to: Smoothly transition traditional SAS code to SAS Viya by understanding potential issues. Explore the capabilities of the Cloud Analytic Services (CAS server) in SAS Viya to process data in a massively parallel processing environment for enhanced efficiency and performance. Use the latest CAS procedures designed to optimize your data processing tasks for distributed computing for improved scalability and speed.   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.   Q&A Can you send the link to the blogs series? Tips for moving to SAS Viya Getting Started with Python Integration to SAS® Viya® Can we get the name of the book you mentioned? Mark’s book is “Mastering the SAS DS2 Procedure: Advanced Data Wrangling Techniques – Second Edition”. Here is a link to a free excerpt.    Does Viya require a strong internet connection? This depends on what you mean by “strong”. If you mean “high bandwidth”, then not really. SAS Viya clients are browser-based, and don’t have very intense bandwidth requirements. If you mean “high reliability”, the answer is yes. Intermittent internet connections can interrupt the continuity of your work with any cloud-based application.   Is it possible to import files directly from my local C drive, instead of importing files to Viya first? You may have run into the same issue with Enterprise Guide running on a Windows computer and the SAS server running on a remote server somewhere. Before you can access a file from a SAS program, the server running that program needs to have access to the file location. Because a server usually can't see your C drive (unless you are running both Enterprise Guide and SAS on your laptop), you can't programmatically access files stored there. You can easily upload a file to the Viya SAS Compute Server and then process it, just as you would have done with Enterprise Guide. Your administrator can set up network folders that are accessible both in Windows systems and on the SAS Compute Server, making it a snap to work with files both in Windows and SAS. But unless the server has direct access to the location where your files are stored, you're going to have to load the file to the server first.   What is CAS? CAS stands for Cloud Analytics Services. It is a server in the SAS Viya environment that processes in-memory data in parallel. It's the high-powered, super-fast server for big data and complex analytics. The other server is the SAS Compute Server, which is equivalent to the SAS 9 workspace server.   If you use Viya Compute instead of CAS, will PROC SQL code still work? Absolutely. A key take-away from this webinar is the fact that the Compute Server is basically SAS Viya’s version of a traditional SAS 9 server. Your base SAS code, including any PROC SQL, will work just as you expect. That's the beauty of Viya. The SAS Compute Server runs your SAS 9 code, and you get access to all of those powerful new Viya technologies as a bonus!   Is it possible to use hash tables with SAS Viya? Absolutely. SAS9 DATA step programs using hash or has iterator objects should run just as expected in the SAS Viya Compute Server. If all of the tables referenced by the DATA step are CAS tables, the DATA step will run in CAS. You can also use hash objects in DS2 data programs and threads by leveraging the Hash and Hash Iterator packages. DS2 programs can execute on the Compute Server and in CAS.   Will Viya (CASL) reduce the need for writing" macros? I have a really interesting paper on this topic that I’m delivering at the Western Users of SAS Software (WUSS) conference in Sacremento, CA.  It’s titled “Beyond Macro: Data-Driven Programming in SAS Viya”. If you can, join me at WUSS to see it presented in person. If not, please keep an eye on the WUSS website for a link to the proceedings. In brief, all macro processing happens on the SAS Compute Server, so you can still do everything you ever did with macro just as in SAS 9. Even if you're submitting CASL code, everything is parsed by the word scanner and can trigger macro processing on the SAS Compute Server before being passed to CAS, so all your macro stuff can resolve normally. But then, when your code starts running in CAS, you have access to all those marvelous CASL variables. You can do some really complex data-driven processing that might have been impossible - or at least really difficult – in macro. The cool thing is, in SAS Viya, you get access to both and can use them in tandem to produce the results you need.   How does one determine whether the program runs on compute vs CAS? There are two ways of going about this. First, for a DATA step in particular, it'll say in the log that your code ran in CAS. If it doesn't say in the log that your code ran in CAS, then your DATA step ran on the SAS Compute Server. Notice that you get the answer either way, but one will probably be faster than the other. Now, for most other things, you explicitly tell it where to run. If you use the SESSREF= option on a PROC (Like PROC FedSQL) and specify the name of your CAS session, then the only place the code can run is in CAS. If it doesn’t run in CAS, it will fail and produce an error in the log. Without the SESSREF= option, it runs in the SAS Compute Server. So, you're explicitly telling it where to go. Of course, the CAS actions you specify in PROC CAS must run in CAS – they have no equivalent on the SAS Compute Server.   Could I conclude that FedSQL is just the replacement of Proc SQL to use or be run on CAS? While it is true FedSQL must be used for SQL queries in CAS, it is not just a replacement for PROC SQL. PROC SQL is tied explicitly to the SAS Compute Server executable process, so it has to run in the SAS Compute Server. FedSQL is a more vendor neutral SQL language, very much more ANSI standard, and it can run anywhere we can get SAS code to run. For instance, there are certain in- database products capable of running FedSQL in a database. And, of course, we can run FedSQL in CAS as well as on the SAS Compute Server. PROC SQL is single-threaded, but FedSQL is multi-threaded, which can make a difference in performance. But have no fear - PROC SQL will be around for a long time! It's very efficient and provides some very nice extensions in the SAS Compute Server. For additional info, the SAS Communities post titled "Difference between PROC SQL and PROC FedSQL" includes some helpful links to explore.    How do I auto save changes made to data sets in CAS lib for permanent storage? You can’t 'auto save', but you can use the SAVE statement in PROC CASUTIL to save changes made to in-memory tables to a permanent data source on-disk.   I am a SAS OR Programmer. How would this transition to Viya be like, especially in terms of performance? I haven't worked with SAS OR specifically, but the performance of the SAS/OR PROCS on the SAS Viya Compute Server should be equivalent to a similarly-provisioned SAS9 server. I don’t know if any of the SAS/OR PROCs are CAS-enabled – but you can check them out in the SAS/OR docs for SAS Viya. In the end, SAS Compute Server performance depends heavily on the hardware provisioning. To understand performance, you really have to know how the hardware for your systems is provisioned.   The main PROC I have used in SAS OR is the PROC OPTMODEL. Can I run PROC OPTMODEL in CAS?  There is a CAS action in the optimization action set that is equivalent to the Compute Server's PROC OPTMODEL. Here is a link to the documentation for optimization.runOptmodel.    I would like to create a business solution from the SAS OR code, in order to provide a tool for business users. How can Viya help me on that sense? Sounds like you would be interested in SAS Viya Jobs or a Custom Step in SAS Studio. Both allow you to create a friendly point-and-click user interface to allow business users to make selections that update and execute SAS code behind the scenes. A Viya Job could be executed in Visual Analytics, Microsoft applications (Word, Excel, Outlook, PPTX), or embedded in a web page. A Custom Step can be executed in SAS Studio. In this SAS Viya Quick Start Tutorials video series, the Creating Custom Steps with SAS Studio is a great place to start.   I am also an experienced Python programmer. How can SAS Viya be useful to me? Is there a comprehensive integration between SAS Viya and all Python packages? And how about performance of Python code execution in SAS Viya? Any benchmark? I suggest you start by watching the Use Python Code in SAS Studio and Use the Python SWAT Package on the SAS Viya Platform videos in the SAS Viya Quick Start Tutorials video series. You should also check out the blog series by Peter Styliadis that Mark mentioned "Getting Started with Python Integration to SAS® Viya®". I would also recommend a deep dive into the "Integrate SAS with Python" section of the SAS Developers site.   I have already seen many of these playlists but have not found a clear answer about performance comparison. What would you have to say about this performance comparison? Not sure if this is what you’re looking for, but here is a link to an extensive performance study that was conducted by an independent research firm comparing Viya and other AI/ML libraries: https://futurumgroup.com/sas-viya/   Is it possible to embed Python code inside SAS PROCs or would I have to work independently in Viya? You can use PROC PYTHON to submit Python code embedded in a SAS program. You can also call Python routines from all sorts of places in SAS. There are procs that are designed particularly to work with Python, and there are products like the SAS Intelligent Decisioning which can use Python models directly. You can also use Python programs to access CAS actions, do some preprocessing, and bring it back to Python for finishing up. For a particular PROC, review the SAS Online Documentation to see if it has an interface for Python.   Can Viya Compute and CASL be run in same program? Absolutely. You use PROC CAS to submit the portions containing CASL code.   Hi, how can we setup our external DB in SAS Viya? SAS Viya comes with a lot of SAS/ACCESS engines and CAS data connectors that allow you to establish direct access to various data sources. From the compute server, you can establish connections using the traditional SAS/ACCESS LIBNAME statement.  On the CAS side, you can establish connections to data sources programmatically using the CASLIB statement or the table.addCaslib action. For a no-code approach, you can use the point-and-click SAS Data Explorer application to add connections. To access Data Explorer, select the Applications menu in the upper left corner of Viya, and select Manage Data.   Can we run batch jobs in SAS Viya scheduled through Control M? I haven’t done a lot of scheduling in Viya so I don't speak from experience, but you can definitely schedule SAS Viya jobs to run in batch. I believe an open-source product called Airflow is what Viya uses for scheduling, so you may need to use a different scheduler.  To run a batch job from SAS Studio, just right-click a program and select Schedule as a job. You can also schedule jobs use Environment Manager.   Is there any SAS Viya tool which provides functionality similar to SAS DI in SAS 9? In SAS Viya, SAS Studio Flows is replacing DI Studio. Several Studio steps are available that correspond to DI Studio transformations.   Can we import python/R models into model manager for scoring? Yes, you can do that using SAS Model Studio and SAS Model Manager. That is not my area of expertise, but I know it is possible.   Is Viya able to run PMML files? SAS Viya supports PMML 4.2. See the documentation for more information:   Can we get Jupyter notebook within SAS Viya to use Python or R language? There is an on-demand Ask the Expert webinar about Jupyter Notebook. Check it out: Jupyter Notebook: Your Coding Canvas Q&A, Slides, and On-Demand Record... - SAS Support Communities.   Recommended Resources 9 for SAS9 – Top Tips for SAS 9 Programmers Moving to SAS Viya blog Webinar ZIP file (includes the PowerPoint PDF, SAS code and data files used to create this webinar) Accelerate Code in SAS Cloud Analytics Services | SAS Viya Quick Start Tutorial Develop Code with SAS Studio | SAS Viya Quick Start Tutorial What is a CAS-enabled procedure? Accelerating SAS® Code on the SAS® Viya® Platform Please see additional resources in the attached slide deck 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. Just hit SUBSCRIBE here:     ... View more

There are several different ways to do lookups in SAS. In this post, I’ll look at techniques that can produce results similar to a left join of a big table with a small table, like might be done to enrich a transaction table with additional information. The techniques covered will include PROC SQL and FedSQL joins, DATA step and DS2 merges, hash objects, and custom formats. I'll execute code for each technique several times, using a small, medium, and large lookup table to enrich the data a very large existing table. I'll record the elapsed time required to produce the same result with the different methods. And finally, we'll look at combining all three lookups in a single program and assess the impact on overall program run time.         The Data   Table Rows Columns Size (MB) Comments COUNTRYCODES 17 2 0.3 Look up country names PRODUCTS 700 4 0.3 Look up item name and manufacturer CUSTOMERS 1,099,519 12 149.9 Look up customer names ORDERS 14,846,132 6 952.6 The big table to be enriched   Orders Sample   OrderID OrderDate CustomerID Country ProductID Quantity 023011_000100000043_0002 2023.01.01 000100000043 US 358 1 023011_000100000058_0014 2023.01.01 000100000058 US 632 2 023011_000100000105_0015 2023.01.01 000100000105 US 627 3   CountryCodes Sample   Country Country_Name AD Andorra AE United Arab Emirates AF Afghanistan   Products Sample   ProductID Item MadeIn Manufacturer 1 Toncot Beefy-T Emb T-Shirt US A Team Sports 2 Trekking Tent >GB Prime Sports Ltd 3 Indoor Handbold Special Shoes US 3Top Sports   Customers Sample   CustomerID Gender FirstName MI LastName StreetAddress City State ZipCode Country Phone Birthday 000100000001 F Katherine M Thomas 3005 Brookside Drive Guin AL 35563 US 205-412-9602 01/01/1927 000100000002 M Anthony E Feldmann 4026 Maple Lane Huntsville AL 35802 US 256-522-5585 01/01/1927 000100000003 M Sam C Tennyson 2715 Mulberry Avenue Donaldson AR 71941 US 501-384-1324 01/01/19     The Scenarios   We have a large data table (lookup.orders) that we want to enrich with information from another table.   Scenario 1   The lookup.orders table contains a two-letter country code in the Country column. We want to add a new column, CountryName, that contains the full name of the country. Table lookup.CountryCodes also contains a two-letter country code (Country) and the full country name (CountryName).   Scenario 2   The lookup.orders table contains a numeric ProductID column. We want to add a new column, Product, that contains name of the item ordered and the manufacturer’s name. Table lookup.Products also contains a numeric ProductID column, and columns containing the item name (Item) and manufacturer information (Manufacturer).   Scenario 3   The lookup.orders table contains a numeric CustomerID column. We want to add a new column, Name, that contains the customer’s full name. Table lookup.Customers also contains a numeric CustomerID column, and columns containing the customers fist (FirstName), middle initial (MI), and last(LastName) name information.   Final Scenario   I'll write code to add all three enrichments to lookup.orders in a single program and measure the overall elapsed time for completion.     The Techniques   Data Step   Merge Sample Code   proc sort data=lookup.orders out=orders; by country; run; data dsmerge_countries; merge orders (in=ordered) lookup.countrycodes; by country; if ordered; run;   Hash Object Sample Code   data dshash_countries; if _n_=1 then do; /* Get the lookup table variables into the PDV */ if 0 then set lookup.countrycodes; /* Initialize the hash */ declare hash codes(dataset:'lookup.countrycodes'); codes.definekey('Country'); codes.definedata('Country_Name'); codes.defineDone(); end; set lookup.orders; rc=codes.find(); drop rc; run;   Custom Formats Sample Code   proc format cntlin=char_formats; run; proc format cntlin=num_formats; run; data dsFmt_countries; set lookup.orders; length CountryName $85; CountryName=put(country,$country.); run;   PROC SQL Left Join   proc sql; create table sql_countries as select o.*, Country_Name from lookup.orders as o left join lookup.countrycodes as cc on o.country=cc.country ; quit;   PROC FedSQL Left Join     proc FedSQL; create table sql_countries as select o.*, Country_Name from lookup.orders as o left join lookup.countrycodes as cc on o.country=cc.country ; quit;     The Results    Technique vs. Lookup Table Size 17 Rows (SEC) 700 Rows (SEC) 1.1M Rows (SEC) Get All 3 Values (MM:SS) DATA Step Merge 12.01 19.45 19.85 01:02 DATA Step Hash Object 4.75 13.63 23.59 00:20 DATA Step Formats (3.27 sec to create) 3.85 4.47 7.38 00:20 PROC SQL LEFT JOIN 5.45 11.31 13.57 00:50 PROC FedSQL LEFT JOIN 10.07 14.50 13.05 00:24     Conclusions    If the formats are already available, then formats look like the best all-around option. If the formats need to be created, the additional overhead should be considered - and the DATA step hash object looks like an attractive choice. But remember, hash object performance seems to degrade as the size of the lookup table increases, and both hash objects and formats can be memory intensive. PROC SQL performs quite reliably, but with the largest data FedSQL seems to have the edge. So, as usual, there isn't any "one size fits all" solution. When performance is critical, there's no substitute for formal benchmarking. But knowing a bit about each of these techniques can guide you to a "good enough" choice for ad hoc coding.   Do you have a favorite lookup technique for your SAS programs? Did the information presented here give you cause to rethink your choices? Until next time, may the SAS be with you! Mark   Grab the ZIP file containing the code and data for this series from my GitHub at https://github.com/SASJedi/blogPackages/raw/main/data_manipulation_in_base_sas.zip   Links to prior posts in this series:   Part 1 – Append Part 2 – Sort Part 3 – Deduplicate     Find more articles from SAS Global Enablement and Learning here. ... View more