SAS to Databricks: 7 SAS Behaviors That Quietly Change Your Numbers in PySpark

Every SAS-to-Databricks program reaches the same moment. The converted notebooks run cleanly, the jobs go green in Lakeflow Jobs, and the first reconciliation report arrives: 0.3% of rows differ, a regulatory total is off by a few thousand, one segment has too many customers. Nobody made a syntax error. The code was translated faithfully, line by line, into a language with different rules.

SQL dialects already have a well-worn path to Databricks. SAS doesn't, because a SAS program isn't SQL. It is DATA steps with an implicit row loop, a macro language that writes code at compile time, and decades of defaults that analysts rely on without knowing it. This guide covers the seven defaults that most often change results, what each looks like in SAS, and the PySpark that keeps the original answer.

One setup step supports several of the fixes below: carry a row sequence from ingest. SAS processes rows in dataset order, and plenty of logic depends on it without saying so. Spark promises no order. Add a _seq column when each SAS dataset is first landed in Delta, and every order-sensitive conversion has something deterministic to sort by.

1Missing values compare as the smallest number

In SAS, a numeric missing value (.) sorts before every number and compares as less than any of them, including large negatives. In Spark, any comparison with NULL returns NULL, and a when() treats NULL as false.

data flagged;
  set accounts;
  if balance < 0 then status = 'OVERDRAWN';
  else status = 'OK';
run;

F.when(F.col("balance") < 0, "OVERDRAWN").otherwise("OK")

F.when(F.col("balance").isNull() | (F.col("balance") < 0), "OVERDRAWN").otherwise("OK")

The same rule applies to every WHERE clause and every < or <= comparison. Sort order, by contrast, happens to agree: Spark's default ascending sort puts NULLs first, as PROC SORT does with missing values.

2a + b and SUM(a, b) are different

SAS gives you two additions. The + operator returns missing if any operand is missing. The SUM() function ignores missing values and returns missing only when every argument is missing. Analysts use SUM() precisely so that one blank field doesn't wipe out a total.

total_fees = sum(late_fee, service_fee, wire_fee);

fees = ["late_fee", "service_fee", "wire_fee"]
all_missing = F.coalesce(*[F.col(c) for c in fees]).isNull()
total = sum((F.coalesce(F.col(c), F.lit(0)) for c in fees), F.lit(0))

df = df.withColumn("total_fees", F.when(all_missing, F.lit(None)).otherwise(total))

In words: coalesce each argument to zero and add them, unless every argument is missing, in which case the result stays NULL. Translating SUM() to +, or coalescing everything to zero, each gets a different set of rows wrong. MEAN(), MIN(), MAX() and N() follow the same ignore-missing rule.

3Running totals depend on row order and treat missing as zero

RETAIN with FIRST./LAST. maps naturally to a window function. Two details decide whether it matches.

proc sort data=txns; by acct_id txn_date; run;

data running;
  set txns;
  by acct_id;
  if first.acct_id then balance = 0;
  balance + amount;          /* sum statement */
run;

from pyspark.sql import Window

w = (Window.partitionBy("acct_id")
           .orderBy("txn_date", "_seq")           # ties keep SAS input order
           .rowsBetween(Window.unboundedPreceding, Window.currentRow))

running = txns.withColumn("balance", F.coalesce(F.sum("amount").over(w), F.lit(0)))

4NODUPKEY keeps the first row, always

PROC SORT NODUPKEY keeps the first observation for each key, in the order rows arrived, because SAS sorts are stable by default. Spark's dropDuplicates() keeps some row per key, and which one can change from run to run.

proc sort data=claims out=first_claim nodupkey;
  by member_id;
run;

first_claim = claims.dropDuplicates(["member_id"])

w = Window.partitionBy("member_id").orderBy("_seq")
first_claim = (claims.withColumn("_rn", F.row_number().over(w))
                     .filter("_rn = 1").drop("_rn"))

This is one of the most common causes of failures that come and go: the converted job passes reconciliation on Monday and fails on Tuesday with no code change.

5MERGE is not a join

A DATA step MERGE ... BY looks like an outer join, and for one-to-one and one-to-many keys it mostly behaves like one. It differs in two ways that matter.

• Same-named columns overwrite. If both datasets have a region column, the value from the dataset listed last wins for matched rows, even if that value is missing. SQL keeps both columns, and a quick coalesce gets the missing case wrong.
• Many-to-many keys pair positionally. When both sides repeat a key, SAS pairs the first row with the first, the second with the second, and so on, and writes a NOTE to the log. A join produces every combination.

data combined;
  merge accounts(in=a) contacts(in=c);
  by acct_id;
  if a;
run;

shared = {"acct_id", "region"}
combined = (accounts.alias("a")
    .join(contacts.alias("c"), F.col("a.acct_id") == F.col("c.acct_id"), "left")
    .select(
        F.col("a.acct_id"),
        *[F.col(f"a.{x}") for x in accounts.columns if x not in shared],
        *[F.col(f"c.{x}") for x in contacts.columns if x not in shared],
        # matched rows take contacts' value, even when it is missing
        F.when(F.col("c.acct_id").isNotNull(), F.col("c.region"))
         .otherwise(F.col("a.region")).alias("region")))

Many-to-many merges should be caught, not silently converted. A conversion should check key uniqueness on both sides and flag the program for review when both repeat. Usually that exposes a data problem the SAS log had been reporting for years.

6Dates count from 1960

A SAS date is the number of days since 1 January 1960. A SAS datetime is the number of seconds since midnight on that day. When SAS datasets are landed without their formats, dates arrive as plain numbers, and a naive conversion to Unix time is off by exactly 3,653 days.

SAS_EPOCH_OFFSET_SECONDS = 315_619_200     # 1960-01-01 to 1970-01-01

df = (df
  .withColumn("open_date", F.date_add(F.lit("1960-01-01").cast("date"),
                                      F.col("open_date").cast("int")))
  .withColumn("last_login", F.timestamp_seconds(F.col("last_login")
                                                - SAS_EPOCH_OFFSET_SECONDS)))

Date functions need the same care. INTNX('month', d, 1) aligns to the start of the month by default, while add_months keeps the day of the month. INTCK counts interval boundaries crossed, not elapsed time. Each has a precise Spark equivalent, but none of them is the obvious one.

7Trailing blanks don't count

SAS character variables are fixed-length and padded with blanks, and SAS comparisons ignore trailing blanks: 'NY' = 'NY ' is true. In Spark those strings are different. This rarely shows up inside a single dataset. It shows up in joins, when one side is a SAS dataset and the other is a padded CHAR column or a fixed-width extract.

if state = 'NY' then region = 'NORTHEAST';

F.when(F.rtrim(F.col("state")) == "NY", "NORTHEAST")

# and on join keys
a.join(b, F.rtrim(a.cust_code) == F.rtrim(b.cust_code))

Proving it: parity before cutover

None of these behaviors shows up in a code review. They show up in data. That is why the last step of a SAS-to-Databricks migration is not "the job runs" but "the output matches".

Two practices keep this loop short. First, fix the pattern, not the program. When a difference traces back to NODUPKEY, the fix goes into the rule that converts every NODUPKEY, not into one notebook. Second, compare with tolerances you agree up front. Floating-point totals can differ in the last decimal place for legitimate reasons, and nobody should spend a week chasing 1e-12.

A pilot is the right place to learn this on your own code. With MigryX, a SAS-to-Databricks pilot typically covers about 10K lines of production SAS in 4 to 6 weeks. That includes Base, macros and PROC logic running on Databricks as PySpark, with row-level parity against the original outputs and lineage published to Unity Catalog.