SAS Tutorial: Applying SAS Date Formats with PROC FEDSQL

One of the most confusing obstacles when moving from PROC SQL (specific to SAS^©) to PROC FEDSQL (oriented towards ANSI SQL standard) concerns the management of display formats.

In PROC SQL or the DATA step, it is trivial to associate a format (e.g., date9.) with a variable during its creation. In FedSQL, the table creation syntax (CREATE TABLE) strictly follows the ANSI standard, which does not recognize the concept of a "SAS^© format". Consequently, a date column often remains a simple raw number (the number of days since 1960).

This article explains how to keep an underlying numeric variable (for calculations) while forcing its display in a date format.

The Problem: Storage vs. Display

You want to create a table where the date is stored as a number (to allow for future sorting and calculations) but is displayed as a readable date (16JUL2023).

What you would do in classic SAS^©: format my_date date9.;
What fails in FedSQL: FedSQL generally rejects the FORMAT= statement within a standard CREATE TABLE query, as it is not valid ANSI SQL.

Approach 1: The SAS^© Viya^™ / CAS Environment (The Recommended Method)

If you are working in SAS^© Viya^™ with CAS tables, the cleanest method is to separate the creation of the data from its decoration (metadata).

You first create the table with FedSQL, and then you use the PROC CASUTIL procedure to modify the table's attributes without reloading the data.

Step 1: Create the table (Raw Data)

1	PROC FEDSQL sessref=mysession;
2	create TABLE casuser.ma_table as
3	select
4	datepart(mon_timestamp) as ma_date_raw
5	from source_table;
6	QUIT;

At this stage, ma_date_raw is displayed as an integer (e.g., 23501).

Step 2: Apply the format (Metadata)

Use the alterTable action of CASUTIL. It is instantaneous because it only affects the file header.

1	PROC CASUTIL;
2	altertable
3	casdata="ma_table"
4	incaslib="casuser"
5	columns={
6	{name="ma_date_raw", FORMAT="date9.", label="Date de l'événement"}
7	};
8	QUIT;

This is the most robust method to ensure that reporting tools (like Visual Analytics) correctly interpret the column.

Approach 2: The SAS^© 9 Environment (The Classic Method)

If you are not on CAS but on a classic SAS^© server (Compute Server), you cannot use CASUTIL. You must use PROC DATASETS.

The principle remains the same: FedSQL creates the structure, and SAS^© Base comes in afterward to apply the SAS^©-specific polish.

1	/* 1. Création via FedSQL */
2	PROC FEDSQL;
3	create TABLE work.ma_table as
4	select datepart(timestamp_col) as ma_date
5	from SOURCE;
6	QUIT;
7
8	/* 2. Modification des métadonnées via PROC DATASETS */
9	PROC DATASETS lib=work nolist;
10	modify ma_table;
11	FORMAT ma_date date9.;
12	QUIT;
13	RUN;

Beware of the "Good Idea That Isn't": The PUT Function

It's tempting to solve the problem directly in the SQL by converting the data:

1	/* À ÉVITER si vous voulez garder du numérique */
2	SELECT PUT(ma_date, date9.) as ma_date_str ...

Why is this dangerous? The PUT function transforms your date into a character string (VARCHAR).

You lose the ability to sort chronologically (the sort will become alphabetical: "01FEB..." will come before "01JAN...").

You lose the ability to perform date calculations (adding days, calculating differences).

The Special Case of Implicit Functions

Note that in some contexts, FedSQL is "intelligent". If you use a function that explicitly returns a date type (like DATEPART on a timestamp), FedSQL can sometimes automatically assign a default format to the resulting column. However, for precise control (e.g., preferring DDMMYY10. to DATE9.), post-processing methods (CASUTIL or DATASETS) remain the only reliable guarantees.

Summary

Objective	Environment	Technical Solution
Keep it numeric + Display as date	SAS^© Viya^™ (CAS)	`PROC FEDSQL` (creation) + `PROC CASUTIL` (formatting)
Keep it numeric + Display as date	SAS^© 9 (Base)	`PROC FEDSQL` (creation) + `PROC DATASETS` (formatting)
Convert to text (Static Reporting)	All	Function `PUT()` in the SELECT (becomes VARCHAR)

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

SAS Tutorial: Applying SAS Date Formats with PROC FEDSQL

Niveau de difficulté

Publié le : 23/09/2021

The Problem: Storage vs. Display

Approach 1: The SAS^© Viya^™ / CAS Environment (The Recommended Method)

Step 1: Create the table (Raw Data)

Step 2: Apply the format (Metadata)

Approach 2: The SAS^© 9 Environment (The Classic Method)

Beware of the "Good Idea That Isn't": The PUT Function

The Special Case of Implicit Functions

Summary

Avertissement important

Niveau de difficulté

Publié le : 23/09/2021

The Problem: Storage vs. Display

Approach 1: The SAS© Viya™ / CAS Environment (The Recommended Method)

Step 1: Create the table (Raw Data)

Step 2: Apply the format (Metadata)

Approach 2: The SAS© 9 Environment (The Classic Method)

Beware of the "Good Idea That Isn't": The PUT Function

The Special Case of Implicit Functions

Summary

Avertissement important

Approach 1: The SAS^© Viya^™ / CAS Environment (The Recommended Method)

Approach 2: The SAS^© 9 Environment (The Classic Method)