SAS to PySpark Migration: DATA Steps, PROC SQL, and Macros

SAS migrations to Databricks are some of the trickiest, because SAS mixes three different programming models — the DATA step, PROC SQL, and the macro language — and each maps to Spark differently. A clean conversion respects those differences instead of forcing everything into SQL.

PROC SQL → Spark SQL (the easy part)

PROC SQL is close to standard SQL and converts fairly directly to Spark SQL or DataFrame .sql(). Watch a few SAS-isms: CALCULATED (referencing a computed column in the same query), automatic remerging of summary stats with detail rows, and SAS's permissive type coercion. These can change results if translated literally.

The DATA step is not SQL

The DATA step processes one row at a time with retained variables, BY-group processing, and implicit output. This is the part people get wrong by shoehorning it into SQL.

• **`RETAIN` / running totals** → window functions (`sum() over`, `lag`).
• **`BY`-group `FIRST.`/`LAST.`** → `row_number()` / `rank()` over a partition; not a `GROUP BY`.
• **`MERGE` (DATA step merge)** → a `.join()`, but SAS merge semantics (especially many-to-many) differ from SQL joins — verify the row count carefully, because SAS and SQL disagree on how duplicates on both sides combine.
• **Implicit output / `OUTPUT` statement** → explicit `union` of filtered DataFrames.
• **`SET` with multiple datasets** → `unionByName`.

The SAS-merge-vs-SQL-join difference is the single biggest correctness trap in SAS conversions.

Macros → parameterization, not string soup

SAS macros (%MACRO, &var, %DO loops) generate SAS code at compile time. Don't try to reproduce macro text substitution in Python — instead, express the *intent* as PySpark functions with parameters and Python loops. A macro that generates 12 monthly queries becomes a parameterized function called in a loop.

Formats and informats

SAS FORMAT/INFORMAT and its date handling (SAS dates are days since 1960-01-01) need explicit conversion. A SAS numeric date column becomes a Spark date via an offset calculation, not a direct cast — miss this and every date is off by decades.

PROC steps

Common PROCs map to Spark: PROC MEANS/SUMMARY → groupBy().agg(), PROC SORT → orderBy (often removable), PROC TRANSPOSE → pivot, PROC FREQ → groupBy().count(). Statistical PROCs (PROC REG, etc.) map to MLlib or are re-scoped entirely.

Validate — SAS especially

Because SAS's DATA-step and merge semantics differ from SQL's, converted PySpark can be perfectly valid and still return different rows. The SAS-date offset and many-to-many merge issues in particular change values, not just plans. Run the SAS logic and the converted code against the same sample and diff the output.

That per-artifact verification is exactly the layer [JarvisX](/) adds to the conversion.