SQL Server to Oracle Migration with SAS: Bypassing the 32-Character Limit

When using SAS^© as an ETL to copy tables from one database to another (for example, from SQL Server to Oracle), an irritating technical limitation is often encountered: SAS^© truncates variable names to 32 characters.

Modern databases support much longer column names (128 characters). A column named Identity_Source_System_Modified_On in SQL Server will be truncated to Identity_Source_System_Modified_ when processed by SAS^©, which can cause naming conflicts or loss of meaning.

Here are two strategies to solve this problem: the modern method (if you have the right tools) and the manual method (if you are using a standard version of SAS^©).

Method 1: The "Clean" Solution (PROC FEDSQL)

If your SAS^© environment is configured with PROC FEDSQL (often available with SAS^©/ACCESS or SAS^© Viya^™), this is the ideal solution.

Unlike the classic SAS^© engine, which forces everything through the SAS^© dataset format (32-character limit), PROC FEDSQL acts as a universal translator. It reads the metadata from the source and applies it to the target without being constrained by the internal limitations of SAS^© datasets.

The Code

1	/* Définition des connexions via LIBNAME */
2	LIBNAME sqlsrv sqlserver user=&user pass=&pass ...;
3	LIBNAME dest oracle user=&user pass=&pass ...;
4
5	PROC FEDSQL;
6	/* Création directe de la table cible à partir de la source */
7	create TABLE Dest.USR as
8	select * from sqlsrv.User_Table;
9	QUIT;

Why does it work? FedSQL complies with the ANSI SQL standard. It maps data types and column names directly from the SQL Server engine to the Oracle engine. If both databases support 128-character names, FedSQL will preserve these names in their entirety.

Prerequisites:

You must have a license for PROC FEDSQL.
The SAS^©/ACCESS engines used must support this functionality.

Method 2: The "Workaround" Solution (PROC SQL Pass-Through)

If you don't have FedSQL or if it fails to initialize, you must use Base SAS^©. The trick relies on an important detail: while SAS^© truncates the variable name, it often keeps the full original name in the variable's label during ODBC/OLEDB import.

The strategy is a three-step process:

Copy the table (names will be truncated).
Identify the truncated columns by comparing the name and the label.
Dynamically generate an ALTER TABLE SQL command to rename the columns in Oracle afterward.

Step 1: Copy the table (with truncation)

1	PROC SQL;
2	/* Transfert des données via SAS */
3	/* Les noms > 32 chars sont tronqués ici */
4	create TABLE dest.usr as
5	select * from connection to odbc (select * from dbo.usr);
6	QUIT;

Step 2: Identify Truncated Names

We will read one row from the source into a temporary table (WORK.ONE) to inspect the SAS^© metadata.

1	PROC SQL;
2	/* Récupérer une seule ligne pour avoir les métadonnées */
3	create TABLE work.one as
4	select * from connection to odbc (select top 1 * from dbo.usr);
5	QUIT;

Step 3: Generate and Execute the Renaming Script

We use the system table DICTIONARY.COLUMNS to find cases where the NAME (truncated) is different from the LABEL (full).

1	PROC SQL noprint;
2	/* Variable macro pour stocker les commandes ALTER */
3	%let renames=;
4
5	/* Génération dynamique du script SQL Oracle */
6	/* Syntaxe : ALTER TABLE table RENAME COLUMN old TO new */
7	select 'RENAME COLUMN ' \|\| trim(name) \|\| ' TO ' \|\| trim(label)
8	into :renames separated BY ', '
9	from dictionary.columns
10	where LIBNAME = 'WORK'
11	and memname = 'ONE'
12	and name ne label; /* Condition clé : le nom diffère du label */
13	QUIT;
14
15	/* Exécution du renommage directement dans Oracle */
16	PROC SQL;
17	connect using dest; /* Utilise la connexion Oracle définie dans le libname DEST */
18	execute (
19	ALTER TABLE USR &renames
20	) BY dest;
21	QUIT;

Important Note on Oracle Syntax:

The exact syntax of the ALTER TABLE command may vary depending on your Oracle version.
Ensure that your target Oracle database version supports long names (Oracle 12c R2 and later support 128 characters; earlier versions are limited to 30 characters). If your Oracle is old (v11g), even this method will fail, and you will have to shorten your names manually.

Summary

Method	Advantages	Disadvantages
PROC FEDSQL	Simple, robust, no complex code. Natively preserves long names.	Requires a specific license and a compatible configuration.
PROC SQL + ALTER	Works with standard Base SAS^©.	More complex to code. Requires `ALTER` permissions on the target database. Depends on the Oracle version.

Wichtiger Haftungsausschluss

Die auf WeAreCAS.eu bereitgestellten Codes und Beispiele dienen Lehrzwecken. Es ist zwingend erforderlich, sie nicht blind in Ihre Produktionsumgebungen zu kopieren. Der beste Ansatz besteht darin, die Logik zu verstehen, bevor sie angewendet wird. Wir empfehlen dringend, diese Skripte in einer Testumgebung (Sandbox/Dev) zu testen. WeAreCAS übernimmt keine Verantwortung für mögliche Auswirkungen oder Datenverluste auf Ihren Systemen.

Zurück zur Artikelliste