SAS to PySpark conversion: a practical guide

How does SAS code map to PySpark?

Construct by construct, and most constructs have a direct equivalent. Row-by-row DATA step logic becomes column expressions on a DataFrame, SQL stays SQL, procedures become DataFrame reads, writes and aggregations, macros become Python. The table is the quick reference; the sections below show each mapping as SAS and PySpark side by side, with the behaviour the conversion has to keep.

data work.orders_clean;
  set raw.orders;
  where status ne 'CANCELLED';
  if amount >= 10000 then
    tier = 'Large';
  else if amount >= 1000 then
    tier = 'Mid';
  else tier = 'Small';
  net = amount - discount;
  keep order_id customer_id tier net;
run;

from pyspark.sql import functions as F

df_orders_clean = spark.table("RAW.ORDERS").filter(
    "NOT rtrim(coalesce(STATUS, '')) <=> 'CANCELLED'"
).withColumn(
    "TIER",
    F.when(
        F.expr(
            "coalesce(AMOUNT, cast('-inf' AS FLOAT)) >= 10000"
        ),
        F.expr("'Large'"),
    ).when(
        F.expr(
            "coalesce(AMOUNT, cast('-inf' AS FLOAT)) >= 1000"
        ),
        F.expr("'Mid'"),
    ).otherwise(F.expr("'Small'")),
).withColumn("NET", F.expr("AMOUNT - DISCOUNT")).select(
    "ORDER_ID",
    "CUSTOMER_ID",
    "TIER",
    "NET",
)

df_orders_clean.createOrReplaceTempView("ORDERS_CLEAN")

proc sql;
  create table work.region_sales as
  select r.region_name,
         sum(o.net) as total_net,
         sum(o.net) / count(*)
           as avg_net
  from sales.orders o
  inner join ref.regions r
    on o.region_id = r.region_id
  where o.order_date >= '01JAN2026'd
  group by r.region_name
  having sum(o.net) > 50000;
quit;

df_region_sales = spark.sql(
    """
SELECT
    R.REGION_NAME,
    sum(O.NET) AS TOTAL_NET,
    sum(O.NET) / count(*) AS AVG_NET
FROM
    SALES.ORDERS AS O
    INNER JOIN REF.REGIONS AS R
        ON O.REGION_ID <=> R.REGION_ID
WHERE cmp_timestamps_null_safe(
    O.ORDER_DATE, '>=', DATE '2026-01-01'
)
GROUP BY R.REGION_NAME
HAVING coalesce(sum(O.NET), cast('-inf' AS FLOAT)) > 50000
"""
)

df_region_sales.createOrReplaceTempView("REGION_SALES")

proc import datafile="&input./orders.csv"
            out=work.orders
            dbms=csv
            replace;
  getnames=yes;
run;

proc sort data=work.orders
          out=work.orders_sorted;
  by region_id;
run;

proc transpose data=work.orders_sorted
               out=work.orders_transposed
               (drop=_name_);
  by region_id;
  id product;
  var amount;
run;

proc append base=sales.orders_history
            data=work.orders_transposed;
run;

from pyspark.sql import functions as F

df_orders = (
    spark.read.option("header", "true")
    .option("inferSchema", "true")
    .option("sep", ",")
    .csv(f"{input}/orders.csv")
)

df_orders_sorted = spark.table("ORDERS").orderBy(
    F.asc("REGION_ID")
)
df_orders_sorted.createOrReplaceTempView("ORDERS_SORTED")

df_orders_transposed = (
    spark.table("ORDERS_SORTED")
    .groupBy("REGION_ID")
    .pivot("PRODUCT")
    .agg(F.first("AMOUNT"))
)
df_orders_transposed.createOrReplaceTempView("ORDERS_TRANSPOSED")

(
    spark.table("ORDERS_TRANSPOSED")
    .write.mode("append")
    .saveAsTable("SALES.ORDERS_HISTORY")
)

df_sales_orders_history = spark.table("SALES.ORDERS_HISTORY")

%macro key_orders(min);
  data work.key_orders;
    set sales.orders;
    where amt > &min;
    keep id region amt;
  run;
%mend;
%key_orders(1000);

# Converted by Alchemist
def key_orders(min):
	df_key_orders = (
		spark.table("SALES.ORDERS")
		.filter(f"AMT > {min}")
		.select(
		    "ID",
		    "REGION",
		    "AMT",
		)
	)
	df_key_orders.createOrReplaceTempView(
		"KEY_ORDERS"
	)

key_orders("1000")

proc format;
  value $region_fmt
    'N' = 'North'
    'S' = 'South'
    other = 'Other';
run;




data work.report;
  set sales.orders;
  region_label =
    put(region, $region_fmt.);


  order_month = put(order_date, yymmn6.);
run;

spark.sql("""
CREATE OR REPLACE TEMPORARY FUNCTION region_fmt(x STRING)
RETURNS STRING
RETURN CASE
    WHEN x = 'N' THEN 'North'
    WHEN x = 'S' THEN 'South'
    ELSE 'Other'
END
""")

df_report = (
    spark.table("SALES.ORDERS")
    .withColumn(
        "REGION_LABEL",
        F.expr("region_fmt(REGION)"),
    )
    .withColumn(
        "ORDER_MONTH",
        F.expr("date_format(ORDER_DATE, 'yyyyMM')"),
    )
)

df_report.createOrReplaceTempView("REPORT")

What makes a SAS migration hard?

Rarely the syntax. The work is in what SAS does without being asked: how it treats missing values, the row order a DATA step relies on, BY-group flags, macro code that exists only at run time, and dependencies nobody listed. Each converts cleanly alone and breaks quietly at scale, which is why validation matters more than conversion speed.

• Dependencies nobody listed

  Programs read each other's outputs through librefs, %include files and autoexec setup, and an EG project can depend on a table another team's job writes at night. Map the lineage before converting, so the work runs in dependency order and nothing is converted twice.

• Missing values

  In SAS, a missing number is smaller than any number, so if amount < 0 is true when amount is missing. In Spark, a comparison with null is null, and filter() drops the row. A missing character value is a blank, which equals ''; in Spark, null and '' are different values. The converted code keeps the SAS rules: see coalesce and the null-safe <=> in the DATA step and PROC SQL pairs.

• Row order

  A DATA step reads rows in the order they are stored, and code that looks at the previous row relies on it. A Spark DataFrame has no row order unless you give it one. Anything positional (LAG, RETAIN, first and last rows) needs an explicit orderBy in a window.

• BY-group processing

  FIRST.var and LAST.var flag the first and last row of each BY group. In PySpark they become row_number() over a window partitioned by the BY variables. SAS breaks ties by input order; Spark needs the tie-break written down, as in the pair below. The same holds for converted code: give it an explicit order on the non-BY columns, or its result can differ from run to run.

proc sort data=sales.orders
          out=work.orders_sorted;
  by customer_id order_date;
run;

data work.first_orders;
  set work.orders_sorted;
  by customer_id;
  if first.customer_id;
run;

from pyspark.sql import Window, functions as F

w = Window.partitionBy("customer_id").orderBy(
    "order_date", "order_id"  # explicit tie-break
)

first_orders = (
    spark.table("sales.orders")
    .withColumn("_rn", F.row_number().over(w))
    .filter(F.col("_rn") == 1)
    .drop("_rn")
)

What are the steps of a SAS to PySpark migration?

Four steps, in this order: inventory and assess the estate, convert, validate against SAS, then deploy and cut over. Assessment sets the scope and the order of work; conversion is the step tools shorten most; validation is where the time goes. Even with automation, an end‑to‑end migration takes at least 2–3 months, mostly organizational time in larger companies.

1. 01

   Inventory and assess

   Count programs, macros, EG projects and DI jobs; map which tables each one reads and writes; size the estate before anyone estimates it. The free Code Analyzer dashboard does the first pass on any number of SAS assets and reports a token count, the executable types and the workload mix.

   Toolkit stage: Analysis
2. 02

   Convert

   Convert in dependency order, and set the conventions once: which schema each libref maps to, whether the output is a .py module or a notebook, naming and formatting. In Alchemist these are configuration, not hand edits. With a deterministic converter, the same input gives the same output, so a changed template shows up as a reviewable diff instead of a fresh rewrite.

   Toolkit stage: Conversion
3. 03

   Validate against SAS

   Run the SAS job and the PySpark on the same input data and compare the outputs: row counts, keys, column totals, then row-level differences. Expect differences from missing values, row order, rounding and dates, and decide case by case which result is right.

   Toolkit stage: Automation
4. 04

   Deploy and cut over

   Schedule the converted pipelines on Databricks, Snowflake or Microsoft Fabric, run them next to SAS for a period, and cut over by business unit or data domain rather than all at once. Keep the comparison from the validation step as a regression test.

   Toolkit stage: Automation

The four steps follow the three stages of the Alchemist SAS‑to‑PySpark migration toolkit: Analysis, Conversion and Automation.

Where does the converted PySpark run?

On Databricks, Snowflake and Microsoft Fabric alike. Alchemist writes PySpark as a .py module or a notebook. Databricks and Fabric run it on their own Spark runtimes; Snowflake runs it through Snowpark Connect for Spark. The syntax tree and the rules are the same for all three; what changes per platform is configuration and deployment.

Manual, LLM-only or deterministic conversion?

Each approach can convert a single program. They differ at scale: whether two runs on the same code give the same result, whether macros and dependencies across programs are taken into account, and how much of the output someone must read line by line. Choose by the size of the estate and the review capacity you have.

In Alchemist projects, the conversion step ran up to 20× faster with 10× less manual effort than LLM-only conversion. That figure covers the conversion step only, not the whole migration. A detailed comparison of deterministic and LLM-only conversion is in preparation.

Where does Alchemist fit?

Alchemist is a SAS‑to‑cloud migration acceleration toolkit by T1A. The Code Analyzer measures and maps the estate, the Code Converter turns SAS into PySpark deterministically, and the Full Kit adds automation for validation and deployment. The output is PySpark that runs on Databricks, Snowflake and Microsoft Fabric; production migrations so far, since 2022, were delivered on Databricks.

Enlarge

Code Analyzer: the dependency graph of a SAS estate, the map the conversion order follows.

• Analysis

  Code Analyzer

  Parses programs, EG projects and DI jobs into a syntax tree: statement statistics, complexity, and data and macro lineage as a dependency graph. The free dashboard runs on any number of assets.

• Conversion

  Code Converter

  Pattern matching on the syntax tree, with templates you configure. Same input, same output. An optional LLM fallback for unsupported constructs, off by default, never the engine.

• Automation

  E2E Migration Framework

  Analysis, conversion, validation and deployment as one repeatable, agent‑orchestrated workflow, with conversion at scale (100+ assets). Full Kit, configured for your environment with T1A services.