SAS File Analyser

[#1]

What it does and why

This app was designed to help out in a migration process where a bunch of SAS files has to be migrated from SAS over to either pure SQL, to Alteryx workflows or hybrids. It does not perform any such migration automatically, but is meant to read and parse each file to help map out the difficulty to migrate and which resource to assign to each file / task.

• Reads SAS code files (.sas)

• Counts common SAS features that affect migration effort

• Assigns a difficulty band (low / medium / high)

• Recommends SQL, Alteryx-first, or mixed for each file

• Lists source tables, targets created, and file/DB I/O it touches

Outputs

• Per-file metrics (counts, difficulty, recommendation)

• Aggregate summary (files scanned, avg score, distribution)

How the flags guide the recommendation Core building blocks

• DATA_steps: General SAS transformation blocks.
  Signal: Many DATA steps. more bespoke logic. Often medium / hard in SQL, medium in Alteryx.

• PROC_SQL: SQL inside SAS.
  Signal: High counts usually easy in SQL, easy in Alteryx (In-DB).

• PROC_SORT / PROC_TRANSPOSE / PROC_SUMMARY_MEANS / PROC_FREQ: Sorts, pivots, aggregations.
  Signal: Straightforward in SQL (ORDER BY, PIVOT, GROUP BY) or Alteryx (Sort/Transpose/Crosstab/Summarize).

Formatting & custom code

• PROC_FORMAT / Custom_formats: Value labels/buckets.
  Signal: Replace with lookup tables or CASE expressions. Medium in SQL, medium in Alteryx.

• User_defined_functions (PROC FCMP): Custom functions.
  Signal: Often needs re-coding. Hard in SQL, medium–hard in Alteryx (Formula/Python/R).

Advanced/harder constructs

• PROC_IML / PROC_DS2 / Hash_objects / Arrays: Matrix language, DS2, in-memory hashes, array loops.
  Signal: Complex logic. Hard in SQL, hard in Alteryx unless redesigned or coded.

Macros & modularization

• Macros_defined / Macro_vars_used / Includes: macro definitions, variables, include.
  Signal: Parameterization and code generation. Medium–hard in SQL, medium in Alteryx (apps/macros/config).

Connectivity & SQL strategy

• LIBNAME_external – DB/file connections (ODBC/Oracle/Teradata/Snowflake/Excel/Access).
  Signal: Points to owners & places to rebuild connections. Easy in Alteryx (connectors), neutral in SQL.

• PassThrough_SQL: CONNECT TO … / EXECUTE (…) BY ….
  Signal: DB-native SQL already. Easy to keep in SQL (adjust dialect), easy in Alteryx.

Row-by-row patterns

• RETAIN / FIRST_LAST_BY / MERGE: State across rows, BY-group edges, DATA-step merges.
  Signal: Needs window functions or multi-row logic. Medium in SQL, medium in Alteryx.

Data quality & SAS-isms

• Data_quality_PROCs: STDIZE, RANK, UNIVARIATE, etc.
  Signal: Mostly aggregations/standardization. Medium in SQL, easy–medium in Alteryx.

• SAS_specific_functions / Hard_to_port_markers: INTNX/INTCK/MDY and PRX regex, CALL EXECUTE, FILENAME PIPE, DOSUBL, JSON/XML maps.
  Signal: Translation overhead or redesign. Medium–hard in SQL, medium–hard in Alteryx (sometimes code tools).

Interpreting the results

• Difficulty_band: quick effort signal per file. High = more redesign likely.

• Suggested_target:

  • Lots of PROC SQL + simple steps => SQL

  • Heavy macros/IML/DS2/hash/FCMP/custom formats => Alteryx-first (prototype, then move stable pieces to SQL)

  • Mixed => Hybrid (split: joins/aggregations to SQL, parameterization/UX in Alteryx)