SAS Viya to Excel: Automating Professional Reports with ODS EXCEL and CAS

Difficulty Level

Beginner

Published on : 14/06/2025

Expert Advice

Michael
Responsable de l'infrastructure Viya.

In a SAS Viya environment (as used in this script), the file path specified (/tmp/freqtable.xlsx) refers to the temporary directory of the Compute Server (the pod running the code), not your local PC's hard drive. To view this file, you must navigate to the Server Files in SAS Studio and download it, or use code to move it to a persistent location (like a SAS Content folder) if you want to keep it after the session ends.

ODS EXCEL is engine-agnostic: It captures CAS output just as easily as SAS 9 output.

The main objective of this script is to create a structured Excel file from SAS^© analysis results. The example is specifically designed for the SAS^© Viya^™ 4 environment and uses a Cloud Analytic Services (CAS) action. An example CAS table is created using a DATA step and loaded into CAS memory to ensure the script can be executed autonomously. The ODS EXCEL destination is configured with options for titles and the worksheet name, allowing for a clear and integrated presentation of the results in the Excel file.

Data Analysis

Type : CREATION_INTERNE

A CAS table named 'qualifyapps' is internally created using a DATA step with 'datalines' for example data, then loaded into CAS memory via PROC CASUTIL. This approach ensures that the example is autonomous and does not depend on pre-existing external data.

1 Code Block

DATA STEP / PROC CASUTIL / PROC CAS / ODS EXCEL Data

Explanation :
This example creates an Excel file (`freqtable.xlsx`) containing a frequency table. First, a small SAS table is defined with dummy data ('Credit_Qualification' and 'Count'), then loaded into CAS memory using `PROC CASUTIL`. Next, the `filename` statement is used to specify the path for the output Excel file. The `ods excel` statement is opened with options to name the worksheet and embed titles. The `proc cas` executes the `freqTab.freqTab` action on the `qualifyapps` CAS table to generate frequencies for `Credit_Qualification` using `Count` as the weight variable. Finally, `ods excel close` closes the ODS destination and finalizes the creation of the Excel file. This file can then be downloaded from SAS Studio. The script is designed to be fully autonomous and executable in a SAS Viya 4 environment.

Copied!

1	/* 1. Création d'une table CAS d'exemple pour la démonstration */
2	/* Ceci rend l'exemple autonome, comme exigé. */
3	DATA casuser.qualifyapps;
4	INPUT Credit_Qualification $ Count;
5	DATALINES;
6	Bonne 100
7	Mauvaise 50
8	Inconnue 20
9	;
10	RUN;
11
12	/* 2. Charger la table en mémoire CAS (si ce n'est pas déjà fait) */
13	PROC CASUTIL;
14	casauto restart;
15	load casdata="qualifyapps" incaslib="casuser" casout="qualifyapps" replace;
16	list tables;
17	QUIT;
18
19	/* 3. Définir le chemin de sortie pour le fichier Excel */
20	/* Le chemin /tmp est utilisé ici, assurez-vous qu'il est accessible en écriture */
21	filename outfile "/tmp/freqtable.xlsx";
22
23	/* 4. Ouvrir la destination ODS EXCEL et configurer les options */
24	/* - file: spécifie le nom du fichier de sortie. */
25	/* - sheet_label: définit le nom de l'onglet dans Excel. */
26	/* - embedded_titles/embed_titles_once: gère l'insertion des titres SAS.*/
27	ods excel file=outfile
28	options(sheet_label="CreditQualification"
29	embedded_titles="yes"
30	embed_titles_once="yes");
31
32	/* 5. Exécuter l'action CAS freqTab.freqTab pour générer le tableau de fréquences */
33	/* - table: spécifie la table CAS à analyser. */
34	/* - weight: la variable de pondération (nombre d'occurrences). */
35	/* - tabulate: la variable pour laquelle calculer les fréquences. */
36	PROC CAS;
37	ACTION freqTab.freqTab/
38	TABLE='qualifyapps',
39	weight='Count',
40	tabulate={'Credit_Qualification'};
41	RUN;
42
43	/* 6. Fermer la destination ODS EXCEL pour écrire le fichier */
44	ods excel close;
45
46	/* 7. Quitter la session SAS (optionnel, selon l'environnement d'exécution) */
47	QUIT;

Pro Tip

When generating large Excel reports from CAS data, using ODS reduces network traffic because only the results (the frequency table) travel from CAS to the client, not the raw source data.

This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.