Long-time SAS© 9 users are increasingly looking to migrate to SAS© Viya™ to harness the power of the CAS (Cloud Analytic Services) engine. This distributed and in-memory engine can significantly speed up existing processes and analyze massive data volumes.
However, a key step in this modernization involves updating PROC SQL code to a CAS-native procedure: PROC FedSQL. While powerful, this transition can be confusing due to strict syntactic differences between the two languages.
This article explores the fundamental differences and provides a practical guide to adapting your code without errors.
1. Understanding the Architecture: Compute vs. CAS
Before modifying the code, it's crucial to understand where it runs:
SAS© Compute Server: This is the equivalent of the SAS© 9 Workspace Server. Your existing SAS© 9 code (and PROC SQL) works here without modification.
SAS© Cloud Analytic Services (CAS): The new high-performance engine. For a procedure to run here, the data must be loaded into memory and the code adapted.
2. PROC SQL vs. PROC FedSQL: The Major Differences
The main difference lies in the SQL standard used. While PROC SQL is permissive (SAS© extensions), PROC FedSQL is strict (ANSI-1999).
Table 1: Feature Comparison
| Characteristic | PROC SQL (SAS© 9 / Compute) | PROC FedSQL (SAS© Viya™ / CAS) |
| Execution Engine | SAS© Compute Server | CAS (Cloud Analytic Services) |
| SQL Standard | ANSI-1992 (Partial) + SAS© Extensions | ANSI-1999 (Strict) |
| Architecture | Single-threaded (except sort/index) | Parallel, Distributed, In-Memory |
| Data Types | Standard (Char, Numeric) | Extended (VARCHAR, INT64, DOUBLE, VARBINARY...) |
| Federated Queries | No (1 DB connection per query) | Yes (Multi-source joins possible) |
| Error Handling | Frequent stop on error | NOERRORSTOP option available to continue |
3. Which Procedure to Use and Where?
A common confusion concerns the location of data (Local Libraries vs. Caslibs). To run FedSQL on CAS, the sessref= option is mandatory.
Table 2: Decision Matrix
| Data Source (Input) | Data Target (Output) | Procedure to Use | Technical Note |
| Compute (e.g., Base SAS©, Work) | Compute | PROC SQL (or FedSQL without sessref) | Classic SAS© 9 approach. |
| Compute | CAS | PROC SQL | FedSQL on CAS cannot "see" local libnames (Compute). |
| CAS | Compute | PROC SQL | Data is downloaded from the CAS server to the client (slow for large volumes). |
| CAS | CAS | PROC FedSQL (with sessref) | The only method to benefit from distributed in-memory processing. |
4. Migration Guide: Pitfalls to Avoid
Here are the critical syntax differences that often block developers when converting code.
Row Management and Pagination
LIMIT instead of INOBS: The inobs option does not exist in FedSQL. Use the LIMIT clause at the end of your query.
OFFSET instead of MONOTONIC(): The undocumented monotonic() function is obsolete. To skip the first 5 rows and read the next 5, use: LIMIT 5 OFFSET 5.
String Strictness (The Most Important Point)
FedSQL is strict about the use of quotes:
Single quotes ('Text'): Only for character strings (values).
Double quotes ("MyVar"): Only for column or table names (especially if they contain spaces or special characters).
Data Types and Functions
Formats: The FORMAT= option in the SELECT is not supported. Use the PUT(col, format.) function. Be aware, this converts the result to a string (VARCHAR).
Explicit Typing: Use CAST(variable AS type) or the :: operator (e.g., date::char(10)) to force a data type.
Obsolete Functions: Replace RANUNI(seed) with RAND('UNIFORM', min, max).
SQL Syntax
No CALCULATED: You cannot reuse the name of a calculated column in the same query (in a WHERE or HAVING). You must repeat the calculation expression.
Mathematical Operators: SAS© mnemonics (EQ, GT, NE) are forbidden. Use standard symbols (=, >, <>).
Dataset Options: Options placed after the table name (e.g., table(where=(...))) do not work. Integrate everything into the SQL WHERE clause.
No ORDER BY in creation: During a CREATE TABLE, the ORDER BY option is ignored or generates an error, as distributed CAS tables have no intrinsic order.
5. Migration Cheat Sheet
This table summarizes the syntax changes to be made to your code.
Table 3: SAS© 9 vs. SAS© Viya™ Syntax
| Action / Concept | SAS© 9 Syntax (PROC SQL) | SAS© Viya™ Syntax (PROC FedSQL CAS) |
| Limit Results | INOBS=5 | LIMIT 5 |
| Pagination | WHERE MONOTONIC() > 5 | OFFSET 5 |
| Character Strings | "Text" or 'Text' | 'Text' (Single quotes mandatory) |
| Variable Names (special) | 'Nom Var'n (Name Literal) | "Name Var" (Double quotes) |
| Macro Variables | "&MyVar" | %TSLIT(&MyVar) |
| Comparisons | EQ, GT, NE | =, >, <> (Symbols) |
| Dataset Options | Table(where=(...)) | Standard SQL WHERE clause |
| Formatting | FORMAT=date9. | PUT(col, date9.) (Becomes VARCHAR) |
| Explicit Typing | N/A (Automatic) | CAST(x as INT64) or x::INT64 |
| Calculated Reference | CALCULATED keyword | Repeat the calculation expression |
| Sort on Creation | ORDER BY supported | ORDER BY not supported in CREATE TABLE |
Modernization to SAS© Viya™ and the CAS engine offers major performance gains. Although PROC FedSQL imposes stricter syntactic rigor than the classic PROC SQL, adopting these ANSI standards ensures more robust, portable, and optimized code for distributed processing. By following these conversion tables, you will avoid the majority of compilation errors during your migrations.
