Migrating SAS workloads to Microsoft Fabric

What actually has to be migrated

A SAS estate is rarely just a folder of .sas programs. A typical one combines:

• SAS Enterprise Guide projects with process flows, built and modified by analysts over many years
• standalone SAS programs, often with %include chains and shared macro libraries
• PROC SQL, including pass-through SQL executed directly in source databases
• permanent datasets in libraries, and many more temporary WORK datasets created along the way
• macros and macro variables that generate code at runtime
• schedules, batch scripts and manual run instructions
• reports and exports consumed downstream, often as Excel files
• spreadsheets and other manual inputs that the process reads every run

An inventory of these artefacts tells you how big the estate is. It does not tell you how to migrate it. For that you need to know what each component reads, what it writes, in which order things run and which outputs anyone still uses. In other words, you need a dependency graph of the estate rather than a list of files.

Reconstruct the estate before converting it

An Enterprise Guide project looks like an opaque binary, but it is not one. An .egp file is a compressed archive containing the project’s XML definition and the code behind its nodes. With the right parsing, a large number of projects can be analysed programmatically rather than opened one by one.

From project structure and the associated code, it is possible to reconstruct:

• process flows and the order of the nodes within them
• input and output tables of each step, including WORK datasets
• joins, filters, unions and appends, aggregations and calculated columns
• duplicate handling and sort-dependent logic
• references to database tables and to external files such as Excel
• dependencies between datasets produced in one project and consumed in another

The result turns a visual SAS estate into something close to a machine-readable migration graph. A simple example shows why that matters.

Seen as a graph, this is one unit of work that has to move together. It also carries two risks a file list would never reveal: a manual spreadsheet that someone must keep supplying after migration, and an append step that makes the output depend on how often, and how many times, the flow has run.

Across a whole estate, the same graph shows which flows are isolated and can move early, which datasets are shared by many consumers and need to be migrated first, which clusters are tightly coupled and must move as one wave, and which outputs nobody reads any more. That is the basis for sequencing and estimating, and it is what our Source Harvesters are built to produce.

One limitation needs stating plainly. Parsing structure is not the same as understanding business meaning. Macros resolved at runtime, code built from macro variables and logic hidden in formats all need a human to confirm what the flow really does.

Choosing the Fabric target for each workload

Fabric offers several places for a SAS workload to land, and the right one depends on the workload, not on a one-to-one rule:

• Delta tables in OneLake, in a Lakehouse or a Warehouse, replace permanent SAS datasets. Most WORK datasets should not become tables at all: they become views, common table expressions or intermediate steps in a transformation.
• Warehouse and T-SQL suit set-based logic that is already mostly SQL, particularly where the team maintaining it is SQL-oriented.
• Notebooks with PySpark or Spark SQL suit heavier transformations, procedural logic that needs rewriting, and anything that benefits from reusable code.
• Data Factory pipelines take over orchestration, scheduling and data movement, replacing batch scripts and manual run instructions.
• Power BI semantic models replace SAS outputs that exist only to feed reports, often more cleanly than a table that reproduces a SAS export.

A common mistake is to turn every SAS program into one notebook. That reproduces the old structure, spreads ingestion, business rules, quality checks and output formatting across hundreds of notebooks, and makes the new platform as hard to maintain as the old one. A better result separates ingestion, reusable transformation layers, orchestration, quality checks and consumption.

Orchestration and capacity

Performance belongs in the migration design, not in the first weeks of production. Fabric capacity is shared, and a design that works for one notebook can behave very differently when dozens of workloads start together. Contention appears in Spark sessions, Delta commits, metadata operations and logging long before raw compute runs out. Orchestration therefore needs to be dependency-aware, with configurable parallelism, retries, idempotent steps and the ability to restart from a failed stage rather than rerunning everything. And it needs to be tested with representative data volumes and a realistic workload mix. A fast proof of concept on a small dataset says little about production.

What can be automated, and what cannot

Automation, including language models, speeds up a SAS migration considerably. It is most valuable when it makes uncertainty visible rather than hiding it.

A useful conversion pipeline does not go straight from legacy code to generated code. It passes through several stages, each of which can stop and ask for a human decision:

1. SAS code
2. Parsed representation
3. Supported constructs
4. Unsupported or ambiguous constructs
5. Generated target code
6. Generated tests
7. Execution
8. Comparison with SAS output
9. Human review

Our Agentic Reverse Engineering accelerator works this way: every finding is traced to the source it came from, and ambiguous logic is surfaced for review rather than silently “fixed”.

Where SAS and Spark behave differently

Much SAS code translates to Spark SQL or PySpark without drama. The dangerous cases are those where the translation looks right and produces a different result. A few that recur in almost every SAS estate:

• Missing values. In SAS a missing numeric value sorts lower than any number and takes part in comparisons: where amount < 100 keeps missing amounts. In SQL, a comparison with NULL is unknown and the row is dropped.
• MERGE is not a join. A DATA step MERGE with a BY statement matches rows positionally within each BY group. For one-to-one and one-to-many data it resembles a join; for many-to-many data it produces a different result from any SQL join.
• Deduplication depends on order. PROC SORT NODUPKEY keeps the first record of each key in the current sort order. The Spark equivalent keeps an arbitrary record unless the ordering is made explicit.
• Trailing blanks. SAS pads character values and ignores trailing blanks when comparing them. Spark does not, so joins on padded keys can silently lose matches.
• Dates and formats. SAS dates are day counts from 1 January 1960, and much logic depends on formats applied at output or grouping time. Grouping by a formatted value, such as a month, is easy to lose in translation.
• PROC SQL remerging. SAS allows summary statistics next to detail columns and quietly remerges them. Standard SQL rejects the query or needs a window function.

None of these is exotic. Each one can pass a quick visual review, which is exactly why validation has to compare outputs rather than code.

Conversion is not validation

Suppose a SAS flow produces a customer-level dataset, and the new Fabric pipeline produces a dataset with exactly the same number of rows. That is encouraging, but it is not proof. The same count can hide customers missing on one side and duplicated on the other, attributes calculated differently, or history handled differently.

A credible comparison checks, at a minimum:

• business keys present in both outputs, and those present in only one
• duplicate keys on either side
• null distributions and distinct values in important columns
• column-level differences for matching keys
• aggregates by the dimensions the business reports on
• the downstream totals people actually use

Where the operational source is available, comparing source, SAS output and Fabric output together makes it possible to tell whether SAS or the new pipeline is the one that deviates. Sometimes the SAS logic turns out to be the one with the defect. We describe this approach in detail in how to prove a migration is correct.

Parallel running and cutover

For outputs with incremental or period-based logic, SAS and Fabric can run side by side for a defined period with their outputs compared after each run. This catches problems that appear only at month-end, on specific weekdays or after several incremental loads.

How long that period should be depends on the risk and the processing calendar, not on a fixed rule. What matters is agreeing the exit criteria before it starts, and switching consumers over in controlled waves that follow the dependency graph, so that a problem in one wave does not stall the rest.

When not to reproduce SAS exactly

SAS estates carry design choices that were sensible under constraints that no longer apply: chains of WORK datasets to keep memory in check, the same derivation copied into dozens of projects, outputs shaped for a specific Excel template, and nightly full recomputations where incremental processing is now straightforward.

A good migration preserves the business semantics, the control requirements and the behaviour that consumers rely on. It does not need to preserve every historical implementation decision. The question to ask of each workload is which behaviour must remain equivalent, not how to make Fabric look like SAS.

How we run a SAS to Fabric migration

1. Discovery of the SAS estate
2. Dependency reconstruction
3. Migration waves
4. Translation or redesign
5. Automated validation
6. Human review
7. Controlled cutover

Most engagements start with a migration assessment that establishes the estate, the dependency graph and the migration waves before any build effort is committed. Delivery then moves wave by wave, with validation running from the first migrated dataset.