Open file formats introduced some years ago provide many useful advantages, such as lightning-fast compute performance and stellar compression. However, on top of operating on flat files, data practitioners have seen the need to add database-like capabilities. First came schema evolution, shortly followed by ACID-compliance and eventually snapshot management. These features improve manageability, such as modifiable table structures, guaranteed transactions and data history.   Various new open table standards emerged to support changing data in the lake. Iceberg and Delta Lake are most widely used as both of these table formats were designed to handle open file formats stored on blob storage. For example, Iceberg uses JSON and Avro files to store table metadata. This, of course, creates additional overhead of managing these metadata files.   The combination of open file formats and open table formats led to the concept of a lakehouse. A data lakehouse can be defined as a modern data architecture that combines the scalability and flexibility of data lakes with the transactional reliability and performance of data warehouses. Adding openness to lakehouse architecture - modern, flexible, and scalable arrangement to manage data using open standards and cloud-native technologies.   This approach has several benefits: • Scalability: Only limited by cloud resources spent. • Flexibility: Can support all data types and formats. • Cost-efficiency: Save costs by efficient data compression • Cloud Agnostic: Portable across all popular cloud platforms   In my opinion, the main drivers are cost-efficiency and flexibility. With open standards comes freedom and the ability to integrate with other tools and technologies also conformant to open standards, such as Parquet. This is why commercial vendors also support these open technologies, the data community wants the best of each world, instead of committing to a single vendor or technology across the entire platform.     As you can see from the picture above, there are many competing technologies in the field. Latest entrant to join the table format wars, is DuckLake, joining the battle and proudly promising to keep your ducks in a row! The recognized combatants are Iceberg, Delta Lake, and Hudi that have already established stable user bases. See the table below for comparison of common table format features:     Coming in with quack, DuckLake utilizes a database system to manage your metadata for the catalog running on any standard SQL database (e.g., PostgreSQL, MySQL, DuckDB, SQLite).   Having your metadata stored in a separate database provides: • Faster metadata lookups • Easier schema evolution and snapshot management • No need for external catalog services   This arrangement claims to make additional catalog services redundant and reduce operational overhead of maintaining a lakehouse. Having a centralized metadata store improves consistency and reliability across multiple tables.   While there are many advantages that a lakehouse can provide, neither the competition nor DuckLake currently support constraints, keys, or indexes. These are features found in traditional databases. Features commonly known to maintain data integrity, make the data rows identifiable and speed up database searches.   The architecture of DuckLake is straightforward. Key factor is the separation of the data layer from the metadata layer. Data Layer contains the data in Parquet file format. Metadata layer consists of the catalog database including the table schemas, snapshots, versioning, and transaction logs.     If you’re wondering if DuckLake is a table format or a data catalog, given that it contains both, they themselves define it as a data lakehouse format. This is due to the fact that it also contains a metadata catalog for storing the schema of the data, typically Parquet files. You can think of something similar to Delta Lake with Unity Catalog or even Iceberg coupled with Lakekeeper or Polaris for catalog management.   Is DuckLake picky on the infrastructure? The data files of DuckLake must be stored in Parquet. Using DuckDB files as storage is not supported at the moment. Data can be stored on any of the popular blob storages, like AWS S3, Azure Blob Storage, Google Cloud Storage or Cloudflare R2. However, the database that runs the catalog component simply needs to be a SQL database and can be installed practically anywhere. Attaching the catalog database to DuckLake and is very simple:   INSTALL ducklake; INSTALL postgres; -- Make sure that the database `ducklake_catalog` exists in PostgreSQL. ATTACH 'ducklake:postgres:dbname=ducklake_catalog host=your_postgres_host' AS my_ducklake (DATA_PATH 'data_files/'); USE my_ducklake; This code is an example from ducklake.select and uses PostgreSQL as the catalog database.   Why are DuckLake and its contenders important to SAS? You have probably heard that SAS recently released SAS/ACCESS to DuckDB for added performance when accessing open file formats, such as Parquet, ORC, and Avro. SAS promotes freedom of choice in storage platforms for optimal cost, capacity and performance. Data lakehouse platforms like DuckLake combined with open file formats like Parquet means our customers can reap the benefits of fast evolving open data architecture with the ability to integrate the best of data management and analytics from SAS.   To wrap it up, DuckLake is a cool and innovative data lakehouse platform, because it brings a whole new way to manage both metadata and data. Undeniably, DuckLake is lightweight and offers performance while preserving flexibility. To learn more about DuckLake, this is a great place to start. And if you prefer to ingest your information in video format, check out this one.   References and sources: Duck image created by MS Copilot on 3.9.2025, prompt “Draw an image of a happy yellow duck sitting in a pond” Table format comparison table created by MS Copilot on 4.9.2025, prompt “Compare Ducklake, Iceberg, Delta Lake and Hudi side by side” The Open Table Format War: Merely a Battle on the Path to Engineering a Truly Open Data Platform: https://www.onehouse.ai/blog/the-open-table-format-war-merely-a-battle-on-the-path-to-engineering-a-truly-open-data-platform DuckLake is an integrated data lake and catalog format: https://ducklake.select/ Understanding DuckLake: A Table Format with a Modern Architecture https://youtu.be/hrTjvvwhHEQ?si=mVImTo4KaDhDzwLL     ... View more

I have to admit, when I first heard about co-pilots, I was fast to fall in love with the idea of having a trusty AI assistant always at hand to help me in my daily tasks. Also, that instantly reminded me of the good old Windows 95 era paper clip assistant called Clippy in MS Office… but that’s just how old I am. You could also opt for Clippy’s other incarnations, anyone still remember The Genius, Power Pup the dog or Scribble the cat? Back in the day, people had mixed opinions about Clippy, but I think that was mainly because he had no memory and lacked a sense of timeliness and an understanding of your goals. Of course, Clippy always popped up on your screen at the worst possible moment. I think Clippy was disliked not because he tried his best to help, but rather because he wasn’t very good at it. But looking back in retrospect, and what we do with Copilots today, Clippy was very much ahead of his time.     Fast forwarding nearly 30 years, why do folks today willingly choose to employ Copilots, those devil’s little helpers, in their daily tasks? There are several reasons:   1) To boost productivity: Automation of repetitive tasks, like drafting messages, summarizing documents, or scheduling meetings. Quick access to information: Instantly retrieve data, or insights without searching manually. Generate content: Help with writing reports, creating presentations, or just to get your brains storming with new ideas.   2) To enhance decision-making: Data analysis: Interpret data, visualize trends, and highlight the important stuff. Scenario modeling: Explore what-if scenarios or create simulated outcomes. Summarize information: Compress complex documents into their core content.   3) To employ a personal assistant: Context-aware help: Better than Clippy, as a good Copilot understands your preferences, and goals and can elaborate on your previous questions. Learning and development: Recommend learning resources or explain unfamiliar concepts. Time management: Prioritize your tasks and create structure out of chaos.   I can agree with all this, as these are things that will make your life easier and will most likely get you faster to results with less repetitive work.   But what about developers, the heroes who create miracles in code? Don’t they need help too? What could a software developer gain by employing a co-pilot?   1) Coding on steroids Predictive code suggestions: Autocompletes lines or entire blocks of code based on context. Template-based code generation: Insertion of repetitive code pieces, such as API calls. Language not lost in translation: Code conversion from one language to another (e.g., Python to SAS).   2) Quality improvement Error detection: Flag potential logic errors or syntax issues early. Best practices: Suggests cleaner, more efficient code patterns. Debug help: Give suggestions on why the results are different from expected.    3) Make your code clear and shareable Documentation assistance: Add understandable explanatory comments to the code Explaining: Provide a verbose description of what a piece code actually does   I can respect all that. It makes perfect sense as most developers prefer to produce high quality code with minimal effort and time spent.   But what does a co-pilot mean to SAS developer? I was taught early in my career that SAS is a procedural language that has powerful expressions like SAS procedures (PROCs) and the all-capable data step. This power of expression means that you can get a lot done without typing too much code. Does it mean that a SAS developer will get less benefits than developers employing more ambiguous languages?   Not so. The upcoming SAS Viya Copilot is a new feature of SAS Viya that provides various capabilities to assist the developer across the entire analytics life cycle by enhancing SAS code development for faster results by providing LLM-based assistance. (Disclaimer: image depicted may or may not reflect the released product): SAS Viya Copilot leverages the power of generative AI and large language models (LLMs) to help you code smarter and easier. The obvious benefits come from coding assistance: Generation of new code from developer provided prompts Explaining the functionality of existing code in verbose text Adding comments to existing SAS code, thus making documentation a breeze All informed AI users put emphasis on the security, in the accidental case that a developer accidentally enters for example personally identifiable data to the prompt. There will be safeguards employed for protection, as the content is first passed through several content filters before it lands on the LLM.   Copilot is an exciting new technology, but I want to remind you that SAS works on providing solutions based on generative AI on multiple fronts: SAS Viya platform and the APIs it provides can be very valuable in creating and orchestration framework for your organization’s LLM assets. By incorporating prompt generation to purpose-built SAS Studio steps, you can provide users with a governed way to harness the power of LLMs. With the help of SAS Intelligent Decisioning, you can deploy customized decision workflows as APIs to serve a point-of-decision, such as recommending whether or not to enroll a new customer.   There are numerous examples of utilizing LLMs via SAS Studio custom steps and a few of them can also be found in the custom step repository that is freely available to all at: https://github.com/sassoftware/sas-studio-custom-steps If you’re new to the concept of SAS Studio custom step, it intuitively enables a developer to wrap SAS code into a re-usable, shareable package for all SAS users at your site. Think User written transformation, if you come from SAS Data Integration Studio background, but with improved usability and ease of sharing.   My personal favorite is the Custom Step that calls an LLM to create a new custom step. It is called LLM - Custom Step Generator with Azure OpenAI and it’s available here. There is also a very informative blog post by Bogdan Teleuca with step by step instructions and a video that explains the use case and usage. My head spins when thinking about the recursive possibilities of having tools that spawn new tools (that hopefully don’t spawn yet more tools). This demonstrates the limitless ways to utilize LLMs with only your creativity as constraint!   Co-pilots are thus useful for various tasks, but what about automation? While LLMs are very capable of parsing and processing user input, in most cases they can’t access your data, abide by your organization’s business rules and most importantly, they can’t act autonomously. They are limited to by the data they were trained upon and that is both a blessing and a curse. Wouldn’t it be blissful to have an autonomous agent that could react to changing requirements and execute tasks when needed? Cue in the concept of Agentic AI, the hottest buzzword in AI at the moment.   Agentic AI can be defined as AI systems that have the agency to act and perform complex tasks. LLMs are known to output answers that look and sound right but often are not based on facts. Have you ever tried asking an LLM to explain yourself as an individual? To become trustworthy companions, they need governance, control, and auditability. A concept of collaborative agents has been introduced. Agents that are autonomous to a certain degree, but work together with a human user, under a set of constraints and whose decisions and actions are governed and explainable.   To wrap things up, SAS Viya platform coupled with SAS Intelligent Decisioning makes it possible to design, build and deploy interfaces to LLMs in a governed, controlled manner. Main interest of this blog, obviously the SAS Viya Copilot, is underway in near future. Regarding our future vision, pre-built, intelligent agents for various industries and ecosystems are being visioned and developed.   It’s exciting times we live in, but if you’re like me and feeling nostalgic for good old Clippy, there is an unofficial substitute for Clippy for Windows called Paperclip by FireCube. You guessed right, it utilizes an LLM under the hood. It may not be all business-like Windows Copilot, but having an assistant with a twist of personality can never be overrated. ... View more

Recently I learned that SAS is developing a new SAS/ACCESS engine for something called DuckDB. I wondered what’s so cool about it as SAS already provides all sorts of access engines and they all pretty much do the same thing: enable smooth access to external data sources. I started to dig deeper and heard that the two adjectives often used to describe DuckDB are performance and simplicity. Other commonly heard arguments are that it’s lightweight enough to run locally and easy to configure. DuckDB runs an In-Process Analytics Engine and thus requires no server to be installed. This means that you can use it directly inside SAS, Python, R, or even Jupyter notebooks without doing any additional configurations.   Why does it perform so well compared to many other DB engines? DuckDB uses something called vectorized execution on columnar storage. This makes it extremely fast for analytical queries, also on very large data. DuckDB can, for example, query Parquet, CSV, JSON, and Arrow files directly without producing additional copies of the data. The great thing about DuckDB is that you don’t first have to load data into a database as the engine can query the data where it lives.   Vectorized execution sounds complex, but it means that data is processed in chunks (vectors or batches) instead of row by row like traditional database engines do. Row-based engines (like PostgreSQL or SQLite) process data one row at a time — which is fine for small data, but cumbersome on very large datasets. A vectorized engine like DuckDB processes many values at once (e.g., 1,024 values per vector), using optimized memory access and processes data with a specific execution paradigm called SIMD (Single Instruction, Multiple Data). It’s super-efficient on modern multicore CPUs, utilizes cache efficiently, and achieves parallelism by performing the same operation on multiple data points concurrently, significantly speeding up the processing.   For the data analyst, DuckDB supports a rich SQL dialect with joins, common table expressions, window functions, aggregations, filtering, grouping, and regular expressions. This makes DuckDB ideal for data developers who prefer SQL over procedural code.   DuckDB is rapidly evolving with extensions being developed to enhance its integration capabilities even further. Of particular interest are the extensions to integrate with cloud-based object storages like S3 and ADLS and also the extension to support reading of Iceberg tables from local or cloud-backed catalogs. The Iceberg extension works with Parquet-backed Iceberg tables and utilizes DuckDB’s high-performance Parquet reader. This extension is further described in the Iceberg Extension pages of DuckDB documentation.   Now that’s enough about theory, let’s have a look at how SAS/ACCESS to DuckDB works. If you’re familiar with connecting SAS to external data, you know it’s done with a SAS libname statement. SAS has one on the roadmap and it will be called the SASIODUK libname engine.   Here’s an example of the usage for connecting DuckDB engine to a folder of Parquet files: libname duklib sasioduk file_type=parquet file_path="<your path to parquet files>"; After running it, SAS will report: NOTE: Libref DUKLIB was successfully assigned as follows: Engine: SASIODUK Physical Name: DuckDB SAS procedures work with SASIODUK just like they do with SAS datasets, for example PROC CONTENTS: proc contents data=duklib.'nocars.parquet'n; quit; Resulting in expected output:   But what’s impressive about DuckDB is the performance. Running a SQL query with multiple calculations, filters, aggregation and sort on a 27GB SAS dataset: PROC SQL; SELECT passenger_count, payment_type, count(*) AS num_trips, avg(trip_distance) AS avg_distance, avg(fare_amount) AS avg_fare, avg(tip_amount) AS avg_tip FROM saslib.yellow_tripdata_2011 WHERE passenger_count is not NULL AND passenger_count > 0 AND passenger_count < 5 AND trip_distance < 100 AND trip_distance > 0 GROUP BY passenger_count, payment_type ORDER BY payment_type, passenger_count; QUIT; On a SAS dataset, this query takes about 2 minutes to run: NOTE: PROCEDURE SQL used (Total process time): real time 1:56.27 cpu time 1:22.98 Next, run the same SQL query using DuckDB engine on a Parquet table: PROC SQL; SELECT passenger_count, payment_type, count(*) AS num_trips, avg(trip_distance) AS avg_distance, avg(fare_amount) AS avg_fare, avg(tip_amount) AS avg_tip FROM duklib.'2011/*.parquet'n WHERE passenger_count is not NULL AND passenger_count > 0 AND passenger_count < 5 AND trip_distance < 100 AND trip_distance > 0 GROUP BY passenger_count, payment_type ORDER BY payment_type, passenger_count; QUIT; Looking at the execution time for this code, it’s easy to spot the difference: NOTE: PROCEDURE SQL used (Total process time): real time 2.54 seconds cpu time 26.24 seconds The same query using DuckDB libname engine on same data, but stored as Parquet file, that takes about 2,7GB, runs in lightning fast 2,5 seconds! The graph below illustrates the execution time comparison.   I’ve been working with data for decades and still witnessing an almost 50x performance improvement with this new technology is almost beyond belief! If you’re a bit of a sceptic like me and cling on to your SAS datasets, I hear you... but still recommend having a look at this. And not just because of the stellar performance that DuckDB can bring but also the kick-ass compression that Parquet can offer thus saving you money in storage costs.   Now that we have proved that DuckDB can do awesome things with locally stored Parquet files, you probably wonder what to do with all the Parquet files in your S3 or ADLS. No problem, DuckDB’s got you covered with the S3/AWS extension and the ADLS/Azure extension. Using ADLS as an example we need to install the appropriate Azure and HTTPFS extensions in our extensions directory. The commands below can be wrapped inside our PROC SQL EXECUTE statement and referencing the previously created SASIODUK libname reference: libname duklib sasioduk; PROC SQL; connect using duklib; SET extension_directory = '/tmp/.extensions'; INSTALL httpfs; LOAD httpfs; INSTALL azure; LOAD azure; Then adding an Azure Managed Identity for Azure authentication: CREATE OR REPLACE SECRET secret2 ( TYPE azure, PROVIDER credential_chain, CHAIN managed_identity, ACCOUNT_NAME '<your account name here>' To prove it works, run a simple SQL select against a Parquet file in ADLS. Note that ABFSS in the URI stands for Azure blob file system secure. SELECT * FROM connection TO duklib ( SELECT * FROM 'abfss://<your directory here>/*.parquet' LIMIT 10 ); Resulting in log output (and of course the actual 10 rows from the Parquet file): NOTE: PROCEDURE SQL used (Total process time): real time 3.91 seconds cpu time 2.08 seconds I’m super impressed with the flexibility that DuckDB offers through its extensions and you can find the full list of core extensions here: DuckDB Core Extensions. I like the easy adding of extensions so much that I’ll add it to my ‘Why DuckDB’ list: 1) Performance, 2) Simplicity, and 3) Extendability.   In case I got you interested in utilizing DuckDB with SAS, you’ll be positively surprised with the recent SAS Viya roadmap developments. Until this release DuckDB users had to rely on JDBC to get SAS working with DuckDB. Mighty Duck is about to get some more air beneath its wings, since SAS/ACCESS to DuckDB aka SASIODUK is planned for 2025.07 stable release of SAS Viya (with the usual caveats about software release schedules).   ... View more

All data has a story to tell, but without understanding the data, it can be difficult to uncover the essence of that story. Imagine having a lakeful of data that you only have very limited knowledge of and would like to learn more through data exploration.   If you have already been exposed to SAS Viya you know that SAS Viya platform offers a multitude of capabilities for data exploration and data governance.   The spearhead tool for data exploration is SAS Information Catalog. It is an intuitive tool for learning a lot more about your data in a quick glance. The Catalog plays a key role within the SAS Viya platform designed to help users discover, understand, and manage data assets more efficiently. It sports features such as automated data discovery, AI-powered metadata enrichment, centralized data catalog, data profiling and quality insights and provides integration with SAS and other data development tools.   While it’s crucial to gain both numerical and visual information about data, modern data users are accustomed to also getting a written, descriptive representation of data. SAS Information Catalog automatically formulates a textual description of the data and its main attributes in the summary presentation:     Fortunately, SAS Viya platform is rich with APIs that expose the platform functionality to authorized external calls. The APIs cover the Information Catalog and can be triggered by a variety of protocols and programming languages, like HTTP or Python, and including of course SAS’ own PROC HTTP.   Here’s an example of a PROC HTTP REST call to the search API of the Information Catalog and it uses the /catalog/search endpoint of the Catalog API. First, let’s define the file location for storing the REST API output: filename respc "&location/get_cat_src.json"; filename resphdrc "&location/get_cat_src.txt"; %let BASE_URI = %sysfunc(getoption(servicesbaseurl)); %put NOTE: &=BASE_URI; Then execute the actual PROC HTTP call with variable &SRC denoting the search term: proc http url="&BASE_URI/catalog/search?q=&SRC&index=catalog&limit=50" method='get' out=respc headerout=resphdrc headerout_overwrite verbose; headers 'Authorization'="Bearer &ACCESS_TOKEN" ; run; Finally, create a libname for the respc json file to access it like a regular SAS table: libname cat1 json "%sysfunc(pathname(respc))"; Once the json file has been parsed the result will look something according to this: Score denotes the match level of the result row with the given search term.   For further reference the Catalog APIs are documented here.   I believe that APIs are super powerful in making different tools speak to each other. But what if you weren’t limited to the exact query syntax that APIs require. Humans are ultimately more comfortable speaking in natural language, and many would appreciate a more flexible way to query data, conversationally, like we would do with each other. Bogdan Teleuca explains this in his blog Conversing with Data: Turning Queries into Conversations with SAS Viya, Azure OpenAI and LangChain.   Text-to-text interaction, powered by Large Language Models (LLMs), has shown tremendous capabilities in handling unstructured data. When it comes to structured data, LangChain enters the fray, making it a breeze to interact with data. The amalgamation of SAS Viya, Azure OpenAI, LangChain, and Python programming is opening new dimensions in data interaction. It's not just about querying anymore; it's about conversing, transforming the way we engage with our data.   But what does this have to do with data exploration and finding your data assets in a simple and reliable way? It is the application of the LLM models to one or several agents that make the AI magic happen. As it happens, Mr. Bogdan Teleuca has also written a blog on how to combine LLM agents to translate the natural language queries into API query syntax that SAS Viya and especially the Information Catalog can understand. In his blog post Using an LLM to Query Catalogued Assets Bogdan describes the process and also compares the internal search capability of the Information Catalog to the one of an intelligent LLM agent, specifically GPT-4 from Azure OpenAI.   Modern LLM’s provide help with that by examining the data and offering a conversational way to query the data. The key question is how to securely combine the power of an LLM into a data exploration tool. This linkage would need to be able to utilize the powerful cloud-based LLM models and still be secure, in the way not to divulge the data into public domain. For this we could employ intelligent agents to handle our data conversation. Now I’m just letting my vision fly, but to cover this scenario there could be three types of agents: Identification agent Retrieval agent Conversational agent   The identification agent first decides if the query is categorized as retrieval or conversational. Retrieval queries help to formulate the human language into syntax that is understood by the Information Catalog search API. They can be used to parametrize the data searches that normally take place in the traditional query input of SAS Information Catalog. An example would be:   “Find me all resources that have the word “CARS” in the table name AND contain at least 428 rows of data”   Conversational queries interact with the user to answer further questions on specific data. For example:   “(Ok, you found the data…) Now tell me can the Model column be used as a unique identifier and is it missing any values?” Looking forward to the near future this is something that SAS Data Management R&D is currently investigating. Having a data exploration tool that allows conversational data exploration in a reliable and secure manner provides value for users to understand and locate their data. Having this power at your fingertips would definitely help to uncover the beautiful story that is hidden in your data!     ... View more

We at SAS have always been very confident about our capability to interact with any kind of data.  Be it flat files, databases or cloud storage, if there is will, there is always a way.   A few claims to set the stage… CSV has been and still is the de facto standard for data interchange between different systems as well as relational databases are still doing a fantastic job in dependable transactional processing. Now that we’ve established that, let’s look into what else is out there.   From the early days of SAS, we saw the need for a storage format that reliably stores structured data and is very fast for doing full table scans. For transactional processing, row by row access is perfect, but for analytics, the fastest way typically is to scan all the rows in one go. Therefore, sas7bdat format was introduced in the late 90s with the SAS System version 7. In short, it’s a binary encoded package that contains the data with some metadata included, such as column names, labels, SAS formats and SAS informats. One often overlooked benefit was that it was accessible between all supported OS platforms of the time.   While sas7bdat remains great for full table scans in SAS9 environment, in modern memory-rich computing environments it has a fallback. Data stored in sas7bat requires conversion when loading data to an in-memory processing engine such as SAS Viya’s Cloud Analytics Services (CAS). Like its predecessor engine called LASR, the CAS engine uses sashdat as native storage format. CAS-based sashdat tables differ from traditional sas7bdat in several ways, including scalability, multi-user access, and parallel processing. As it was designed from ground-up to support in-memory processing, it can be directly memory mapped and requires no conversion at load.   Both of these formats are reliable and well performing, but as the data volumes constantly grow, there has been need from market for yet improved efficiency in compression as cloud storage may be plentiful, but not free. Both of these SAS proprietary file formats offer compression and for sas7bdat, you can define compression easily by adding compress=yes to your libname statement:   libname mylib 'pathtofolder' compress=yes;   This enables compression for all tables created in the specified library. Back in the day SAS developers refrained from using compression because CPU power was scarce and expensive. Modern multi-core processors can handle it without skipping a beat and it doesn’t introduce much lag in dataset access times.   Similarly for sashdat, the compression can be specified with CASUTIL procedure when writing out a sashdat table with the compress=true option:   proc cas; table.save / caslib="casuser" name="mydata.sashdat" table={name="my_table", caslib="casuser"} compress=true quit;   While the storage efficiency can be improved with compression, further optimization is always a benefit when storing data. Also, I’m not hiding the fact that these are SAS proprietary storage formats and we live in the age of openness. Along with the introduction of cloud storage there has been a rise of new file formats that are open by definition. Any vendor can build access tools for them and many already have. With SAS Viya, also SAS has introduced support for open file formats, including Parquet and ORC. Accessing Parquet files with SAS Viya is simple with the Parquet libname engine:   libname myparq parquet "/export/home/users/user/parquet_folder";   After this, the parquet files are available for SAS procedures. To verify that that the file is actually in parquet format, we can use CONTENTS procedure. Pay attention to engine type and the compression algorithm being used:   proc contents data=myparq.class; run;     The libname engine for ORC works exactly the same. To create a library using the ORC engine:   libname myorc orc "/export/home/users/user/orc_folder";   And likewise for using SAS procedures on ORC data, again using procedure CONTENTS to check that file is in ORC format and it is compressed with ZLIB by default:     proc contents data=myorc.class; run;       One useful performance tip you can also find in the documentation is that in the ORC and Parquet engines, compression is on by default. In other SAS engines, compression is turned off by default. Turning off compression for these engines almost always reduces performance, especially with a cloud storage system.   To illustrate the compression capabilities between various file formats, I’m including the table below that compares SAS file formats with the most common open file formats:     This test was run on a randomized synthetic 1GB dataset and represents average compression rates for these storage formats. On live data it may show even better performance as real world tends to be repetitive.   Now you might be wondering that sas7bdat has already server SAS users se reliably for many years and you’ve never had any problems with respect to size or performance. Rest assured, sas7bdat is not going away, even if vendors like SAS are investing heavily to better support the open file formats.   It’s comforting to know that if your data volumes are growing and cloud storage costs are a pain in your cloud budget, there are reliable remedies to the problem. I know that all data is not created equal, some data is used all the time while others are mainly stored for safekeeping. You may have heard the buzzword data tiering and that just might be the topic for my next blog. Stay tuned!     ... View more

I was recently involved in a customer meeting, doing a data governance speak. I was having fun and had a good story with practical use cases and an eye-catching demo. After my set was finished, a colleague started his presentation on augmenting data preparation with AI. I was a bit skeptical, as I felt it’s still just hot air and a lot of promises. Little did I know, as I had not realized how far we have come with GenAI as coder’s aid.   In his demo my colleague was using the SAS Viya Copilot Extension for Visual Studio Code and started his demo by telling GenAI what kind of data manipulation he would like to accomplish. It was a simple data preparation task - to split a source table into separate result tables as output based of the content of a specific column. GenAI replied with a first version of the code, and he let it run in Visual Studio Code. Code ran with no errors and output was as expected. However, the next two things were the part that literally blew my mind!   He asked GenAI to insert comments to the code. We checked them together and they were as good as anyone of us typically writes. I felt that was very cool for all of us who are lazy to write comments. But the coolest thing was when he asked AI to modify the code to into re-usable format. GenAI went on to create a SAS macro that takes the table name and names of the columns to be processed as macro inputs. And the result was a working SAS macro. We also tested that with necessary variables and results were good!   This was the part that convinced me that AI can be especially useful in shaping the rough version of the code and letting the developer to fine tune it with either more detailed requirements for the GenAI or editing manually. These capabilities in my opinion earn the moniker ‘Copilot’ as that is exactly what is does. One thing has not changed though. While Copilot provides good advice, comments and a solution to given problem, the developer still needs to know which problem is being solved to define the requirements for the code. In this transportation analogy, the developer would be the pilot who directs the GenAI by formulating the requirements and estimates the validity of the results. It’s good to remember that the developer in pilot role is ultimately responsible also for the copilot’s possible logic mistakes. As in any software development process, human review of the results adds necessary governance to the process.   Many organizations have already defined or are in the process of defining their policy regarding the application of GenAI and use of results obtained from the use of it. Use of GenAI often raises questions related to intellectual property risks so having a clear policy makes it more straightforward for the developer to start using these tools in good confidence. Obviously training plays a key role in the adoption of secure GenAI practices. In modern integrated development environments, it’s all too easy to drop private information into the editor.   If you’re already developing in SAS Viya, then you can give it a try with the SAS Viya Copilot Extension for Visual Studio Code. It’s flexible tool to enhance productivity and time to results as you program in SAS. SAS Viya Copilot uses GenAI to add comments to a block of SAS code, write an explanation of a block of SAS code, and generate SAS code based on a block of comments in a SAS program. All of these available directly from the developer-friendly menus, as in the image below (source: https://github.com/sassoftware/sas-copilot) Looking at these examples it’s very easy to get hyped with all the possibilities that GenAI can offer. That happened to me, and if you’re feeling the same way, have a look yourself. A good place to start is the blog 3 ways you can use a copilot in SAS® which explains how SAS Viya Copilot can speed-up your SAS coding right now. ... View more

Preparing to write this Juletip I went back to the basics of Xmas. Where is Santa Claus from? Of course, Lapland in the north of Finland. He even has his own Santa Claus village in Rovaniemi.   Then I was pondering, come Christmas Eve, Santa will hop on his sleigh and even though it might be a flying sled, he would still need some snow to get it safely off the ground. So, I started to research, will there be enough snow in Rovaniemi on Christmas Eve. For this experiment I used SAS Studio on SAS Viya 2024.10 release. Also, because I’m dealing with snow, I just had to use Snowflake cloud database to store my finished analytical base table data! In this example, I’m doing some simple data management in SAS Studio, to prove the point that anyone can do data prep and analytics on freely available open data. To answer the research question, I visited the download observations site of the Finnish Meteorological Institute: https://en.ilmatieteenlaitos.fi/download-observations They offer superb selection of open data and offer many parameters to customize what you are downloading.  I also noticed they provide open APIs as well, a word about those in the conclusion.   I then proceeded to download the weather data for Rovaniemi Airport observation station, as that is the station closest to Santa’s Village. I chose the data from years 2013-2023. This I received in consumer friendly xlsx format, it was 4018 rows of data.     My tool of choice was SAS Studio and its flow designer. First step was to import the xlsx into a SAS table called SNOW_RAW:   I used the Import data step which is capable in importing CSV and XLS/X files. Import ran successfully but looking at the imported columns,  I unfortunately noticed a data quality problem. The most interesting column that tells the depth of snow used -1 almost consistently to indicate no snow at all – except for year 2014 where they had on some rows used the ‘-‘ (dash) character. Also, I found the convention of using -1 to indicate no snow a bit problematic for analysis, as I’d rather have zero to symbolize no snow. Fortunately there is a tool to investigate the string patterns in data columns – SAS Information Catalog! So I analyzed the table there and found out the following:   There are indeed 7 rows that use the dash ‘-‘ to symbolize no snow, so that was the culprit! Having a visual look and the actual data proved my suspicions. This quite well proves that discovering data quality problems with SAS Information Catalog is easy for any data user.   This caused SAS to interpret the column as character instead of numeric and that is a problem when doing analyses on the data. Thus, I wanted to remedy this and started to work on improving the quality of data for the snow depth column. First I use the Recode Values step to clean out the -1 and ‘-‘ values in the SNOW column. It’s super simple to use, just state the old value and new value that will be used as replacement. As you can see in the below pic, I’m recoding both -1 and dash values to zero. Also you can define whether you want to create a new column or change the existing one. I’m creating a new column called SNOW_RECODED. As my snow column is still in character format, next I want to convert that to a new numeric column called SNOW_CLEAN. For that I use the Calculate Columns step. Of course, the versatile Query step would work just as well, but calculate columns has a few perks that make it easy to use. For example, it provides a ready-made tool for converting from char to numeric: The created code can be seen in the expression builder and it’s very straightforward as expected. It uses the SAS input function and requires a SAS informat as parameter. To create a DATE column, I can simply combine the Day, Month and Year columns with SAS cats function to concatenate them together. Then I use the SAS input function to read them in as dates using the good old DDMMYY10. informat. input ((cats('Day'n,'-','Month'n,'-','Year'n)),DDMMYY10.) SAS date formats are always a bit cryptic, but they are well documented. See here for DDMMYYx. Now that I’m happy with my data I can proceed to loading the data into Snowflake and create some basic analyses to see if Santa will find snow on Xmas eve. Notice the ‘S’ symbol on data to indicate that the data has now been stored in Snowflake. A use case for this could be that the next consumer of data down the data chain needs their data stored in Snowflake, but also showcases the seamless integration between SAS and Snowflake. Also it has the word SNOW, so it’s appropriate for my theme!   Using Characterize Data step I can see that the mean for snow depth for the last 11 years on Xmas eve has been about 36cm, so according to near historical data, Santa’s sled should get a safe start. Then I use the Bar Chart step to find out corroborating evidence for a swift sleighride for Santa, as I’m filtering the graph for Month=12 and Day=24 to find out historical snow depths for Xmas eve. Application of the Series plot step is why I needed to create the continuous DATE variable as it shows the whole time period in one graph. Looking at the all year round snow depths, this also predicts smooth sledding for Santa. This concludes the example, and I hope it shows that data management can be simple also with real data. Also, it worth to notice that SAS Studio has grown wings and is now a powerful tool for many types of data manipulation and analysis. SAS Studio is being developed at amazing pace with new features released monthly with each release and a great place to find out about the latest developments is here: What’s New in SAS Studio Next step to take this further would be to utilize the Finnish Meteorological Instute’s open APIs for fetching data and they also provide ready-made Python, R and JavaScript libraries for this. How about creating a SAS Studio Custom Step for extracting the weather data through one of these APIs? Perhaps something for the next exercise… until then, happy holidays! ... View more

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

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