Migrating SAS to PySpark: DATA Steps, PROC SQL, and Macros at Scale

SAS has been the backbone of enterprise analytics for decades. Banks run risk models in SAS. Insurers calculate reserves in SAS. Government agencies process survey data in SAS. But the economics have shifted. SAS licensing costs continue to rise while the open-source ecosystem — led by Apache PySpark — delivers equivalent or superior capabilities at a fraction of the cost, with the added benefit of distributed processing across commodity hardware.

This article provides a practical mapping of SAS constructs to their PySpark equivalents: DATA steps to DataFrame operations, PROC SQL to Spark SQL, and SAS macros to Python functions. These are not theoretical comparisons — they are the patterns that migration teams encounter daily when converting production SAS programs to distributed PySpark pipelines.

The SAS-to-PySpark Conceptual Map

SAS and PySpark approach data processing from fundamentally different paradigms. SAS is row-oriented and procedural — a DATA step processes one row at a time, maintaining a Program Data Vector (PDV) that accumulates state. PySpark is column-oriented and declarative — you describe transformations on entire columns, and the Catalyst optimizer decides how to execute them across a distributed cluster.

Understanding this conceptual shift is the single most important factor in a successful migration. Translating SAS line-by-line into PySpark produces code that is slow, hard to maintain, and ignores Spark's strengths. The goal is to translate SAS intent into idiomatic PySpark.

SAS to Apache PySpark migration — automated end-to-end by MigryX

DATA Step to DataFrame Operations

The SAS DATA step is the workhorse of SAS programming. It reads data, applies conditional logic, creates new variables, and writes output datasets. The direct PySpark equivalent is a chain of DataFrame transformations using withColumn(), filter(), select(), and when().

SAS DATA Step with Conditional Logic

/* SAS: Create risk category from credit score */
data risk_scored;
  set customers;
  if credit_score >= 750 then risk_category = 'LOW';
  else if credit_score >= 650 then risk_category = 'MEDIUM';
  else risk_category = 'HIGH';

  annual_income_k = annual_income / 1000;

  if age < 18 then delete;
run;

PySpark Equivalent

from pyspark.sql import functions as F

risk_scored = (
    customers
    .filter(F.col("age") >= 18)
    .withColumn(
        "risk_category",
        F.when(F.col("credit_score") >= 750, "LOW")
         .when(F.col("credit_score") >= 650, "MEDIUM")
         .otherwise("HIGH")
    )
    .withColumn("annual_income_k", F.col("annual_income") / 1000)
)

Notice the structural differences. The SAS code processes row by row with imperative if/else logic. The PySpark code declares the transformation on entire columns. The filter() replaces the delete statement, and F.when() replaces the conditional assignment. PySpark executes this across all available cores and nodes simultaneously.

Retained Variables and Running Totals

SAS DATA steps with retain statements maintain state across rows — a pattern used for running totals, lag calculations, and sequential processing. PySpark handles these through window functions.

from pyspark.sql import Window

window_spec = Window.partitionBy("account_id").orderBy("transaction_date")

transactions_with_running = transactions.withColumn(
    "running_balance",
    F.sum("amount").over(window_spec)
).withColumn(
    "prev_amount",
    F.lag("amount", 1).over(window_spec)
)

PROC SQL to Spark SQL

SAS PROC SQL is often the easiest construct to migrate because Spark SQL supports standard ANSI SQL syntax. In many cases, the migration is nearly a direct copy with minor syntax adjustments.

SAS PROC SQL

proc sql;
  create table monthly_summary as
  select
    region,
    product_line,
    month(order_date) as order_month,
    sum(revenue) as total_revenue,
    count(distinct customer_id) as unique_customers,
    mean(revenue) as avg_order_value
  from orders
  where year(order_date) = 2025
  group by region, product_line, calculated order_month
  having total_revenue > 100000
  order by region, total_revenue desc;
quit;

PySpark Spark SQL Equivalent

# Register the DataFrame as a temporary view
orders.createOrReplaceTempView("orders")

monthly_summary = spark.sql("""
    SELECT
        region,
        product_line,
        MONTH(order_date) AS order_month,
        SUM(revenue) AS total_revenue,
        COUNT(DISTINCT customer_id) AS unique_customers,
        AVG(revenue) AS avg_order_value
    FROM orders
    WHERE YEAR(order_date) = 2025
    GROUP BY region, product_line, MONTH(order_date)
    HAVING SUM(revenue) > 100000
    ORDER BY region, total_revenue DESC
""")

The key differences: SAS uses calculated to reference computed columns in the same query; Spark SQL requires repeating the expression or using a subquery. SAS uses mean(); standard SQL uses AVG(). These are minor syntactic differences, not conceptual shifts.

MigryX precision parser — Deep AST-level analysis ensures every construct is understood before conversion begins

SAS Macros to Python Functions

SAS macros are one of the most misunderstood constructs in migration projects. SAS macros are a text substitution system — they generate SAS code as strings, which is then compiled and executed. Python functions, by contrast, are first-class programming constructs that accept arguments, return values, and compose naturally.

SAS Macro

%macro process_region(region=, year=);
  data work.&region._summary;
    set raw.transactions;
    where region = "&region" and year(txn_date) = &year;
  run;

  proc means data=work.&region._summary noprint;
    var amount;
    output out=work.&region._stats mean=avg_amount sum=total_amount n=txn_count;
  run;
%mend;

%process_region(region=WEST, year=2025);
%process_region(region=EAST, year=2025);

PySpark Python Function

def process_region(transactions_df, region, year):
    """Process transactions for a specific region and year."""
    filtered = transactions_df.filter(
        (F.col("region") == region) &
        (F.year("txn_date") == year)
    )

    stats = filtered.agg(
        F.avg("amount").alias("avg_amount"),
        F.sum("amount").alias("total_amount"),
        F.count("amount").alias("txn_count")
    )

    return stats

# Process multiple regions
west_stats = process_region(transactions, "WEST", 2025)
east_stats = process_region(transactions, "EAST", 2025)

# Or process all regions at once — no macro loop needed
all_stats = (
    transactions
    .filter(F.year("txn_date") == 2025)
    .groupBy("region")
    .agg(
        F.avg("amount").alias("avg_amount"),
        F.sum("amount").alias("total_amount"),
        F.count("amount").alias("txn_count")
    )
)

The Python version is more powerful in every dimension. It accepts DataFrames as arguments, returns DataFrames, can be unit tested, and can be composed into larger pipelines. The final example shows the real insight: what required a macro loop in SAS is simply a groupBy() in PySpark, processing all regions in a single distributed operation.

Scaling from Single-Node to Distributed

SAS runs on a single server. Even SAS Grid computing requires specialized infrastructure and licensing. PySpark, by contrast, scales horizontally by adding commodity nodes. A pipeline that processes 10 GB on a developer's laptop uses the same code to process 10 TB on a 50-node cluster — no code changes required.

The key architectural decisions for scaling PySpark pipelines include:

• Partitioning strategy — partition data by commonly filtered columns (date, region) to enable partition pruning and reduce shuffle.
• File format — replace SAS datasets (.sas7bdat) with Parquet or Delta Lake for columnar compression, predicate pushdown, and schema evolution.
• Broadcast joins — use F.broadcast() for small lookup tables to avoid expensive shuffle joins.
• Caching — cache intermediate DataFrames that are reused across multiple downstream operations with df.cache() or df.persist().

# Optimized PySpark pipeline with partitioning and broadcast
lookup_df = spark.read.parquet("s3a://ref-data/product_lookup/")

result = (
    spark.read.parquet("s3a://data-lake/transactions/")
    .filter(F.col("transaction_date") >= "2025-01-01")
    .join(F.broadcast(lookup_df), "product_id", "left")
    .groupBy("product_category", F.month("transaction_date").alias("month"))
    .agg(F.sum("amount").alias("total_amount"))
)

# Write partitioned output
result.write.partitionBy("month").mode("overwrite").parquet("s3a://output/monthly_summary/")

Migrating from SAS to PySpark is not just a technology swap — it is a shift from proprietary, single-node analytics to an open-source, distributed platform that scales with your data. The constructs map cleanly, the code becomes more maintainable, and the infrastructure costs drop dramatically. For organizations with hundreds or thousands of SAS programs, automated migration with MigryX accelerates the timeline from years to months.

Why MigryX Delivers Superior Migration Results

The challenges described throughout this article are exactly what MigryX was built to solve. Here is how MigryX transforms this process:

• Production-ready output: MigryX generates code that passes code review and runs in production — not prototype-quality output that needs weeks of cleanup.
• Platform optimization: Converted code leverages target platform-specific features for maximum performance and cost efficiency.
• 25+ source technologies: Whether migrating from SAS, Informatica, DataStage, SSIS, or any of 25+ legacy technologies, MigryX handles it.
• Automated documentation: Every conversion decision is documented with before/after code mappings and transformation rationale.

MigryX combines precision AST parsing with Merlin AI to deliver 99% accurate, production-ready migration — turning what used to be a multi-year manual effort into a streamlined, validated process. See it in action.