sas proc sql window functions

GPT-5.4

Vote: No

PROC SQL in SAS does not support ANSI SQL window functions like OVER(PARTITION BY ... ORDER BY ...) in the same way that databases such as PostgreSQL, SQL Server, or Oracle do. In Base SAS, PROC SQL can do grouping, joins, subqueries, and aggregates, but not true windowed analytic functions. To achieve similar results, SAS users typically use DATA step processing, PROC SUMMARY/MEANS, self-joins, remerging summaries, or PROC EXPAND/TIMESERIES depending on the task. If someone is using SAS pass-through SQL against an external DBMS, then window functions may work in the remote database, but that is the DBMS doing the work, not PROC SQL itself. So the best general answer is No.

Gemini 3.1 Pro

Vote: No

SAS `PROC SQL` does not support standard ANSI SQL window functions (such as `ROW_NUMBER()`, `RANK()`, or `OVER()` clauses). To achieve the equivalent of window functions in SAS, users typically have to use the `DATA` step with `BY` group processing (utilizing `FIRST.` and `LAST.` variables alongside `RETAIN`). Alternatively, users can use `PROC FEDSQL`, which was introduced to support newer ANSI SQL standards and does include support for various window functions.

Claude Opus 4.6

Vote: No

SAS PROC SQL does not support standard SQL window functions (also known as analytic functions) such as ROW_NUMBER(), RANK(), LEAD(), LAG(), or other OVER(PARTITION BY ... ORDER BY ...) constructs. PROC SQL in SAS is based on an older SQL standard and does not include the OVER clause or window/analytic function syntax. To achieve equivalent functionality in SAS, users typically need to use other SAS procedures (such as PROC RANK, DATA step with RETAIN/LAG functions, or PROC EXPAND) or use SAS-specific SQL extensions like the MONOTONIC() function (which itself has caveats). Alternatively, if SAS is connected to an external database via pass-through SQL, the database's native window functions can be used, but that is the database engine executing them, not SAS's own PROC SQL engine. Therefore, the answer to whether SAS PROC SQL supports window functions is No.

Final Summary

The AI Roundtable reached a swift and unanimous verdict: PROC SQL is fundamentally lacking in modern window function support. While GPT-5.4, Gemini 3.1 Pro, and Claude Opus 4.6 all acknowledged that SAS can mimic these results through manual workarounds like the DATA step or self-joins, they firmly agreed that the 'OVER' clause remains a ghost in the machine.

No agreement after 1 rounds of discussion. Final vote: No: 3

Strongest Arguments

• No: SAS PROC SQL is based on an older SQL standard and lacks the internal engine capacity to process window/analytic functions natively; any appearance of support via pass-through is actually the work of a connected external database.