# SAS to PySpark conversion: a practical guide > How SAS DATA steps, PROC SQL, PROC IMPORT, SORT and TRANSPOSE, macros, formats, EG projects and DI jobs map to PySpark: code pairs, pitfalls, migration steps. - Canonical page: https://getalchemist.io/sas-to-pyspark/ - Updated: 2026-11-03 - Author: [Dmitriy Alkhimov](https://www.linkedin.com/in/dmitriy-alkhimov-19217523b/), product lead at T1A - Site index: https://getalchemist.io/llms.txt How the constructs a SAS estate is built from map to PySpark: DATA steps, PROC SQL, procedures such as IMPORT, SORT and TRANSPOSE, macros, formats, Enterprise Guide projects and DI jobs. What breaks quietly, how to validate the result, and the order a migration runs in. The conversion works the same way whether the PySpark runs on Databricks, Snowflake or Microsoft Fabric. **Short answer.** Converting SAS to PySpark means rewriting each construct as its DataFrame or Spark SQL equivalent: DATA steps become DataFrame chains, PROC SQL becomes Spark SQL, macros become Python functions, formats become expressions or functions. The syntax maps cleanly. The work is in what SAS does implicitly: missing values, row order, BY-group processing, macros resolved at run time. Plan for those, and validate the output data against SAS, not only the code. ## 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. | SAS | PySpark | Watch for | | --- | --- | --- | | DATA step: `SET`, `WHERE`, `IF`/`THEN`, `KEEP` | `spark.table()`, `filter()`, `F.when()`, `select()` | Missing values in comparisons | | `PROC SQL` | `spark.sql()`, null-safe `<=>` joins | Missing keys in joins, date literals, remerged statistics | | `PROC IMPORT` | `spark.read.csv()` with options | Column types inferred from the data | | `PROC SORT`, `NODUPKEY` | `orderBy()`, `dropDuplicates()` | Which duplicate survives | | `PROC TRANSPOSE` | `groupBy().pivot()` | Column names built from data values | | `PROC APPEND` | `write.mode("append").saveAsTable()` | Columns must match the base table | | `%let`, `%macro`, `&var` | Python variables, functions and arguments | Code generated at run time | | `PROC FORMAT`, `PUT`, `INPUT` | SQL functions with `CASE`, `date_format()` | `BESTw.` and informat edge cases | | Enterprise Guide projects, DI jobs | PySpark modules or notebooks | Flow order and mappings that live outside the code | | `RETAIN`, `LAG`, `FIRST.` / `LAST.` | Window functions: `lag()`, `row_number()` | Spark rows have no order until you give one | | `MERGE` with `BY` | `join()` | Many-to-many merges do not behave like SQL joins | | `PROC MEANS` / `SUMMARY` | `groupBy().agg()` | `_TYPE_`, `_FREQ_` and `CLASS` combinations | | `PROC FREQ` | `groupBy().count()` and a window | Missing levels and percentages | ### How does a DATA step convert to PySpark? A DATA step processes one row at a time; PySpark works on whole columns. `SET` becomes `spark.table()`, `WHERE` becomes `filter()`, assignments become `withColumn()`, and `KEEP` or `DROP` become `select()` or `drop()`. `IF`/`THEN` chains map to `F.when()`. Steps that carry values between rows (`RETAIN`, `LAG`, `FIRST.`/`LAST.`) need window functions instead. `orders_clean.sas` (SAS): ```sas 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; ``` `orders_clean.py` (PySpark, Converter output: Alchemist's real output): ```python 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") ``` SAS reads a missing status as blank and ignores trailing blanks, so `rtrim(coalesce(STATUS, ''))` with the null-safe `<=>` keeps those rows, where a plain `!=` would drop them. A missing number is smaller than any number in SAS, hence the `coalesce` to `-inf` before each comparison: a missing amount lands in Small, as it does in SAS. ### How does PROC SQL convert to Spark SQL? Most PROC SQL runs in Spark SQL with small edits, so teams often keep it as SQL inside `spark.sql()` and rewrite only what differs. Three SAS habits need attention: how missing values behave in joins and comparisons, summary statistics remerged onto detail rows, and SAS functions and date literals that Spark spells differently, such as `'01JAN2026'd`. `region_sales.sas` (SAS): ```sas 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; ``` `region_sales.py` (PySpark, Converter output: Alchemist's real output): ```python 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") ``` SAS joins a missing key to another missing key; Spark never matches null to null, so the join uses the null-safe `<=>`. The date filter, through `cmp_timestamps_null_safe` from the Alchemist runtime library, and `HAVING` keep SAS's rule that a missing value is smaller than any other, and the SAS date literal becomes `DATE '2026-01-01'`. The `work` table becomes a temporary view that later steps query by name. ### How do PROC IMPORT, SORT, TRANSPOSE and APPEND convert? Each procedure becomes a DataFrame operation. PROC IMPORT becomes `spark.read`, PROC SORT becomes `orderBy()`, PROC TRANSPOSE becomes `groupBy().pivot()`, and PROC APPEND becomes a write in append mode. The summary procedures follow the same pattern: PROC MEANS becomes `groupBy().agg()`, PROC FREQ `groupBy().count()`. `orders_history.sas` (SAS): ```sas 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; ``` `orders_history.py` (PySpark, Converter + templates output: Alchemist's real output, partly generated from Alchemist templates): ```python 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") ``` `getnames=yes` becomes the `header` option, and the `&input` macro variable a Python f‑string. PROC TRANSPOSE with `ID` and `VAR` becomes a pivot: one row per region, one column per product, and no `_NAME_` column to drop. PROC APPEND writes to the base table in append mode, which later steps then read back. For PROC SORT, `dropDuplicates()` keeps an arbitrary row per key, while `NODUPKEY` keeps the first one in input order; when it matters which row survives, rank the rows with a window and an explicit order instead. Alchemist keeps sort steps by default, because it cannot always tell whether a later step needs the order; Spark drops all but the last sort, so the cost is small. Where you know the order is unused, a [node filter](https://docs.getalchemist.io/configuration/conversion/node_filter.html) leaves PROC SORT out of the output. For PROC MEANS and PROC FREQ, watch what SAS counts. `N=` counts non-missing values, which is `F.count()` on the column, not `count(*)`. PROC FREQ leaves missing levels out of its percentages unless the `MISSING` option is set, so filter the nulls before you compute them. PROC MEANS also writes `_TYPE_` and `_FREQ_`; add them only if a later step reads them. ### How do SAS macros and macro variables convert? Macro variables (`%let`, `&var`) become Python variables or function parameters, and `%macro` definitions become Python functions that build DataFrames. The pair below is real Converter output. Macro code that writes other code at run time, such as `%do` loops over table names or `CALL EXECUTE`, needs the most review, because that code exists only once SAS has generated it. `key_orders.sas` (SAS): ```sas %macro key_orders(min); data work.key_orders; set sales.orders; where amt > &min; keep id region amt; run; %mend; %key_orders(1000); ``` `key_orders.py` (PySpark, real Alchemist Converter output): ```python # 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") ``` A `%let cutoff = 1000;` at the top of a program becomes `cutoff = 1000` in Python, and each `&cutoff` becomes a reference to it. Macros that only parameterise a step convert cleanly. Macros that assemble table names, column lists or whole steps from strings are where a reviewer should read the output line by line. ### How do SAS formats and informats convert? A format controls how a value is displayed; an informat, how text is read. In PySpark, a display format becomes an expression such as `date_format()`, and a custom PROC FORMAT becomes a function: a Spark SQL function with a `CASE` expression that later steps call where SAS code calls `PUT`. Some formats, such as `BESTw.`, have no exact Spark equivalent. `report.sas` (SAS): ```sas 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; ``` `report.py` (PySpark, Converter + templates output: Alchemist's real output, partly generated from Alchemist templates): ```python 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") ``` PROC FORMAT becomes a temporary SQL function, so `put(region, $region_fmt.)` becomes `region_fmt(REGION)`, and the `OTHER=` range becomes the `ELSE` branch, which also catches blank and missing regions. `yymmn6.` prints a date as 202601, which is the `yyyyMM` pattern of `date_format()`. For `BESTw.`, Alchemist generates a regular cast, handled differently for values with and without decimals, and the [conversion notes](https://docs.getalchemist.io/usage/converting/nuance/sas_spark.html) document where the output differs from SAS, together with the `INPUT` and `$w.` informat caveats. If your estate keeps formats in a catalog, export it with the code: a format referenced but never defined is a common gap in an inventory. ### How do Enterprise Guide projects and DI jobs convert? An Enterprise Guide project or a DI Studio job is more than SAS code. The order of the flow, the settings of each task and the table mappings live in the project's metadata, and a conversion that reads only the generated code loses them. Alchemist converts EG projects (.egp) and DI jobs (.spk) from their metadata, not only their code. In the code: - Program nodes and the SAS they contain - Code that tasks generate at run time Only in the metadata: - The order the nodes run in, and which outputs feed which inputs - Task settings: query builder joins, filters, sort keys - Prompts and parameters the flow is run with - Table mappings and transformations in DI jobs This is why an inventory should export the projects themselves, not the code a developer copied out of them. DI jobs can also target [Prophecy](https://docs.getalchemist.io/configuration/dialects/prophecy/index.html), for teams that want visual pipelines after the move. ## 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](#data-step) and [PROC SQL](#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. `first_orders.sas` (SAS): ```sas 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; ``` `first_orders.py` (PySpark, illustrative: a hand-written equivalent, not Converter output): ```python 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") ) ``` Two orders on the same day: SAS keeps the one stored first, Spark keeps whichever `order_id` sorts first. Pick the tie-break that matches the business rule, and test it. ## 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. **Inventory and assess** (toolkit stage: Analysis, https://getalchemist.io/#stage-analysis). 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. 2. **Convert** (toolkit stage: Conversion, https://getalchemist.io/#stage-conversion). 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. 3. **Validate against SAS** (toolkit stage: Automation, https://getalchemist.io/#stage-automation). 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. 4. **Deploy and cut over** (toolkit stage: Automation, https://getalchemist.io/#stage-automation). Schedule the converted pipelines on [Databricks, Snowflake or Microsoft Fabric](https://getalchemist.io/sas-to-pyspark/#platforms), 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. ## 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. ### Databricks Runs on the Databricks Spark runtime, as notebooks or Python files scheduled as jobs. Map each SAS library to a Unity Catalog schema in the libref configuration. ### Snowflake Runs through Snowpark Connect for Spark, which executes the DataFrame code on Snowflake compute. Map each SAS library to a Snowflake schema in the libref configuration. ### Microsoft Fabric Runs on the Fabric Spark runtime, as notebooks or Spark job definitions in a workspace. Map each SAS library to a lakehouse schema in the libref configuration. ## 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. | Criterion | Manual rewrite | LLM-only | Deterministic (syntax tree) | Deterministic + LLM fallback | | --- | --- | --- | --- | --- | | How it works | Engineers rewrite each program by hand | A model generates code for each snippet or file | SAS is parsed into a syntax tree and rewritten with rules and templates | The same rules and templates; an optional LLM fallback, off by default, drafts only the constructs the rules do not cover | | Same input, same output | Depends on who writes it | Not guaranteed between runs | Yes, every run | Yes for rule-converted code; LLM-drafted parts can vary between runs | | Context across programs | What the engineer knows | What fits in the prompt | Lineage mapped from the parsed estate | Lineage mapped from the parsed estate | | Code without a rule | Handled with judgement | Converted plausibly; needs review | Left with a TODO comment for manual work | Drafted by the LLM fallback, checked with Ruff and Python, then reviewed | | Fits best | Small scope, or code being redesigned anyway | Prototypes and one-off programs | Hundreds of programs, EG projects or DI jobs | Large estates with a long tail of rare constructs | 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. - **Code Analyzer** (Analysis, https://getalchemist.io/#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. - **Code Converter** (Conversion, https://getalchemist.io/#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. - **E2E Migration Framework** (Automation, https://getalchemist.io/#e2e-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. - Start a free trial (Code Analyzer on any number of SAS assets, no time or usage limit): https://app.getalchemist.io/ - Try the live demo: https://live.getalchemist.io/ - Pricing: https://getalchemist.io/pricing/ (machine-readable: https://getalchemist.io/pricing.md) - Documentation: https://docs.getalchemist.io/ ## FAQ ### Can SAS code be converted to PySpark automatically? Yes, for the code data pipelines are made of: DATA steps, PROC SQL, common procedures and macro-driven workflows. A deterministic converter rewrites those by rule, and code without a rule is left with a TODO comment for manual work. SAS/STAT analytics and reporting layers are outside Alchemist's scope. Validate every converted table against SAS output before cutover. ### Does the converted PySpark run on Databricks, Snowflake and Microsoft Fabric? Yes. Alchemist writes PySpark as a .py module or a notebook. Databricks and Microsoft Fabric run it on their own Spark runtimes; Snowflake runs it through Snowpark Connect for Spark. The rules are the same for all three; what changes per platform is configuration and deployment, such as which schema each SAS library maps to. Delivered production migrations so far are on Databricks. ### Should SAS become Spark SQL or the PySpark DataFrame API? Both compile to the same Spark execution plan, so choose for the people who will maintain the code. PROC SQL often stays SQL inside spark.sql(); DATA step logic usually reads better as a DataFrame chain. Mixing both in one pipeline is normal. Alchemist's output format, code structure and formatting are configurable. ### How long does a SAS to PySpark migration take? Even with automation, an end-to-end migration takes at least 2–3 months, mostly organizational time in larger companies. Mid-size projects have taken 4–6 months to move the business process fully. The conversion step alone ran up to 20× faster with 10× less manual effort than LLM-only conversion; that figure covers conversion, not the whole migration. ### How do you validate converted SAS code? Run the SAS job and the converted PySpark on the same input data and compare the outputs: row counts, keys, column totals, then row-level differences. Differences usually trace back to missing values, row order, rounding or date handling. Decide case by case which result is correct, and keep the comparison as a regression test. ### Can an LLM convert SAS to PySpark? For a single program, often yes, and the result is a useful draft. Across an estate, the same code can convert differently on different runs, often without taking into account SAS quirks, and the model sees only its prompt, not the macros and upstream tables the code depends on. Alchemist converts deterministically. Its optional LLM fallback for unsupported constructs is off by default, its output is checked with Ruff and Python, and it can use a self-hosted endpoint. ### Does Alchemist migrate the data too? No. Alchemist converts SAS code, pipelines and process logic, not the data itself. Moving tables to Databricks, Snowflake or Microsoft Fabric is a separate step in your own data movement process. For the period when SAS and the new platform run side by side, the Full Kit includes a SAS–Cloud Coexistence Framework, on request. --- Code marked illustrative is a hand-written PySpark equivalent, not Converter output; code marked Converter output is Alchemist's real output, and Converter + templates output is real output partly generated from Alchemist templates.