From SAS 9 to SAS Viya: The Complete Migration Guide from PROC SQL to PROC FedSQL

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.

Note :
CAS syntax example:

1	PROC FEDSQL sessref=masession;
2	create TABLE casuser.resultat {options replace=true} as
3	select * from casuser.SOURCE;
4	QUIT;

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).

Macro Problem: Since single quotes prevent macro resolution ('&MyVar' does not work), use the %TSLIT(&MyVar) macro function. It resolves the variable and adds the necessary single quotes.

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.
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.

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.

Avertissement important

Les codes et exemples fournis sur WeAreCAS.eu sont à but pédagogique. Il est impératif de ne pas les copier-coller aveuglément sur vos environnements de production. La meilleure approche consiste à comprendre la logique avant de l'appliquer. Nous vous recommandons vivement de tester ces scripts dans un environnement de test (Sandbox/Dev). WeAreCAS décline toute responsabilité quant aux éventuels impacts ou pertes de données sur vos systèmes.

Retour à la liste des articles