Migrating SAS workloads to Databricks

Who owns the transformations after SAS?

In many organisations, SAS Enterprise Guide is where analysts built and changed their own process flows. Databricks is a very capable platform, but it is oriented towards notebooks, code, version control and jobs. If the people who maintained the SAS flows are not going to write PySpark, the migration has to decide explicitly where their work goes.

In practice we see three patterns, often in combination:

• Engineering-owned pipelines. Core data preparation moves to Spark code maintained by a data engineering team, with analysts consuming curated tables.
• SQL-centric transformation. Logic that analysts need to understand and change is implemented in SQL, for example with Databricks SQL or dbt, which keeps it readable for a wider group.
• A governed self-service layer. Where analysts must keep building their own preparations, they get a defined place and set of tools to do so on top of curated data, rather than rebuilding a shadow estate.

Databricks does not solve this change by itself. Deciding it early prevents a technically successful migration that its former users cannot work with.

Understand the SAS estate first

The discovery work is the same as for any SAS migration: reconstructing process flows, inputs, outputs, WORK datasets, external files and dependencies from Enterprise Guide projects and programs, and turning them into a dependency graph that drives migration waves. We describe that in detail for SAS to Microsoft Fabric; it applies equally here.

Mapping SAS workloads to Delta, Spark and jobs

• Permanent SAS libraries become schemas in Unity Catalog, with datasets as Delta tables.
• WORK datasets mostly disappear, becoming temporary views, common table expressions or DataFrames within a job, unless they are reused and worth materialising.
• PROC SQL largely maps to Spark SQL, with attention to SAS-specific features such as remerged summary statistics, the CALCULATED keyword, and interval functions such as INTCK and INTNX.
• DATA step logic maps to PySpark or SQL window functions, but often needs rethinking, as described below.
• Macros become parameterised functions, job parameters or configuration. Code that generates code needs particular care.
• Schedules become Databricks jobs with explicit task dependencies.

As on any platform, one SAS program should not automatically become one notebook. Ingestion, reusable transformations, quality checks and consumption belong in separate, testable components.

Procedural SAS logic in a distributed engine

The DATA step processes one row at a time in a defined order. Spark processes partitions of data in parallel with no inherent row order. Logic that relies on order therefore has to be made explicit:

• RETAIN and running totals become window functions with an explicit partition and ordering.
• FIRST. and LAST. by-group processing becomes row numbering or window functions over the same keys and a defined sort.
• LAG and LEAD exist in Spark, but SAS LAG in conditional code behaves as a queue rather than “the previous row”, so a direct translation can be wrong.
• Loops that write several output rows per input row become explodes, joins against generated ranges or unions.
• Hash objects used as in-memory lookups become joins, often broadcast joins.

Where the ordering is not fully specified in the original code, the SAS result may depend on physical row order. The migration has to decide what the correct order is, and that is a business question as much as a technical one. The semantic differences between SAS and Spark, such as missing values, MERGE and trailing blanks, are covered on our SAS to Fabric page.

Performance is a separate question from correctness

Code that ran efficiently in SAS can perform badly when translated literally to Spark. Sorting a dataset before every step, processing many small tables sequentially, or bringing data to the driver to loop over it are patterns that work in SAS and fight a distributed engine. Common issues after a direct translation include large shuffles from unnecessary sorts and wide joins, skewed keys that leave one task doing most of the work, many small files in Delta tables, caching that consumes memory without benefit, and Python user-defined functions where built-in functions exist.

That is why semantic equivalence and performance equivalence are two separate validation questions. A job can produce exactly the right result and still be unusable at production volume. Both need to be tested with representative data volumes, concurrency and workload mix, not only on a development sample.

Unity Catalog, lineage and governance

Unity Catalog records lineage for workloads that run through it, down to column level, and brings permissions, auditing and discovery under one model. That is a real improvement on most SAS estates. It does not, however, know anything about the legacy estate: the mapping between each SAS output and its Databricks replacement has to be kept by the migration itself, and it is what makes validation and impact analysis possible during the transition.

Access rules embedded in SAS libraries, metadata server permissions or even program logic need to be translated into catalog permissions, row filters and column masks, and tested with real user profiles.

Validating SAS against Databricks

Every migrated output is compared with the SAS output on business keys, column values, aggregates and downstream totals, ideally with the source system included to show which side deviates. Differences are classified rather than simply failed, and high-risk outputs can run in parallel with SAS for a period before cutover. The method is described in how to prove a migration is correct.