Fedsql

Handling Date Comparisons in FedSQL

Simon 22 Aufrufe
Niveau de difficulté
Débutant
Veröffentlicht am :
Dans : Fedsql
Advanced Tutorial: Manipulating Native JSON in SAS Viya with FedSQL
22/12/2024 • 28
SAS Viya Tutorial: Pre-process Data in the Database Before CAS Loading
22/09/2024 • 19
SAS Viya Optimization: Mastering Implicit Pass-Through with FedSQL
22/12/2023 • 19
SAS and SQL: How to Replicate OVER PARTITION BY in SAS
23/07/2022 • 22
Resolving the "BASE driver" connection error with PROC FEDSQL
26/04/2022 • 16
Ganze Kategorie ansehen

The arrival of SAS© Viya™ and the CAS (Cloud Analytic Services) engine introduced the use of FedSQL, a standardized implementation of the SQL language designed for scalability. However, FedSQL is much stricter than the classic SAS© Base PROC SQL, especially regarding data types.

A common error occurs when filtering dates using numeric macro variables.

The Problem: Strong Typing vs. Weak Typing

In classic SAS© (Base SAS©), a date is nothing more than a number (the number of days since January 1, 1960). You can therefore write without any issue: WHERE my_date_column > 23040.

In FedSQL, types are strict. A column defined as DATE cannot be directly compared to an INTEGER. If you try to inject a raw numeric macro variable into your CAS query, you will get the following error:

ERROR: Operator is not unique: DATE > INTEGER

This means that the engine does not know how to compare these two incompatible data types without explicit conversion.

The Solution: The ANSI Format

FedSQL adheres to the ANSI SQL standard. To filter on a date, you must provide a literal value in the specific form: DATE 'YYYY-MM-DD'

The technical challenge is to transform your numeric macro variable (e.g., 23040) into a formatted character string (e.g., '2023-01-30'), while managing the constraints of the SAS© macro language (quotes, hyphens).

The Technical Recipe

To build this string dynamically, we need to combine three tools:

  1. PUTN: To convert the raw number into a date format with hyphens (yymmddd10.).

  2. %QSYSFUNC: To execute PUTN while masking the hyphens (-). Without the Q (Quoting), SAS© might interpret the hyphens in the date (2023**-01-**30) as minus signs (subtraction).

  3. %TSLIT: A macro provided by SAS© that solves the problem of quotes. In SQL, literals must be enclosed in single quotes ('value'), but SAS© macros do not resolve inside single quotes. %TSLIT (Time Share Literal) generates a string enclosed in single quotes while allowing the macro inside to be resolved.

The Corrected Code

Here is how to assemble these elements for a robust FedSQL query:

1/* Exemple : Macro variable contenant une date numérique SAS */
2%let Aujourdhui = 23040;
3 
4PROC FEDSQL;
5 select * from ma_librairie.ma_table_cas
6 /* La syntaxe magique : DATE suivi de la chaîne formatée */
7 where dateColumn >= date %tslit(%qsysfunc(putn(&Aujourdhui, yymmddd10.)));
8QUIT;
Let's look at what happens when the WHERE line is compiled:

putn(&Aujourdhui, yymmddd10.) transforms 23040 into 2023-01-30.

%qsysfunc protects the string 2023-01-30 so that it is treated as plain text.

%tslit wraps everything to produce '2023-01-30'.

The final code sent to the CAS server is: where dateColumn >= date '2023-01-30'

When working with FedSQL in CAS:

  • Forget about direct Date vs. Number comparisons.

  • Always use the ANSI literal DATE 'YYYY-MM-DD'.

  • Use the combo %TSLIT + %QSYSFUNC to inject your macro variables cleanly.

Zurück zur Artikelliste
Copyright © WeAreCAS 2014-2026
SAS und alle anderen Produkt- oder Dienstleistungsnamen von SAS Institute Inc. sind eingetragene Marken oder Marken von SAS Institute Inc. in den USA und anderen Ländern. ® zeigt die Registrierung in den USA an. WeAreCAS ist eine unabhängige Community-Site und nicht mit SAS Institute Inc. verbunden.