CAS

How to retrieve the memory size of all tables in a CASLIB?

Simon 24/08/2024 7 views

Working with SAS^© Viya^™ and the CAS (Cloud Analytic Services) engine often involves monitoring the memory footprint of your data. A common question frequently arises: how to obtain storage details (size, memory usage) for all tables in a library at once?

If you have already tried using wildcards like % in the table.tabledetails action, you have probably encountered an error. This article explains why and offers a robust CASL solution to consolidate this information into a single report.

Note :
The standard action to get the size of a table is table.tabledetails. However, it is designed to analyze a specific table at a time. The following code will fail because the name option does not accept generic patterns:

1	/* Ce code ne fonctionnera pas */
2	PROC CAS;
3	TABLE.tabledetails / name="%" caslib="MaLib";
4	QUIT;

To overcome this limitation, a programmatic approach is needed:

List all available tables.

Loop over this list.

Execute tabledetails for each.

Consolidate the results.

Note :
The most effective solution uses CAS scripting language (CASL). Here is the complete procedure, inspired by a solution validated by the SAS^© community.

1. Data Preparation (Optional)
To test the code, we first load some sample tables (Cars, Class, Baseball) into the casuser library.

1	cas mycas;
2	LIBNAME casuser cas caslib=casuser;
3
4	PROC CASUTIL;
5	load DATA=sashelp.cars outcaslib="casuser" casOut="cars" replace;
6	load DATA=sashelp.class outcaslib="casuser" casOut="class" replace;
7	load DATA=sashelp.baseball outcaslib="casuser" casOut="baseball" replace;
8	RUN;

The Consolidation Script

The script below performs the operation in a single PROC CAS step. It iterates over each table, retrieves its details, and uses a trick with datastep.runcode to append the results to a final table named allResults.

1	PROC CAS;
2	/* 1. Récupérer la liste de toutes les tables de la librairie */
3	TABLE.tableinfo RESULT=rc / caslib="casuser";
4
5	/* Variable pour gérer la syntaxe d'ajout (append) après la première itération */
6	fragment=';';
7
8	/* 2. Boucler sur chaque table trouvée */
9	DO TABLE over rc["TableInfo"][,"Name"];
10
11	/* Récupérer les détails de la table courante */
12	TABLE.tabledetails RESULT=RESULT / name=TABLE caslib="casuser";
13
14	/* Sauvegarder le résultat temporaire dans une table CAS */
15	saveresult RESULT caslib="casuser" casout=TABLE;
16
17	/* 3. Générer et exécuter du code Data Step dynamiquement */
18	/* Cela permet d'ajouter le nom de la table source et de consolider les données */
19	code = "data casuser.allResults" \|\| fragment \|\|
20	" set casuser." \|\| TABLE \|\| ";" \|\|
21	" length Data varchar(100);" \|\|
22	" Data='" \|\| table \|\| "';" \|\| /* Ajoute une colonne avec le nom de la table */
23	" run;";
24
25	datastep.runcode / code=code;
26
27	/* Changer le fragment pour activer l'option append pour les tours suivants */
28	fragment='(append=yes);';
29	END;
30	QUIT;

table.tableinfo : This action creates a result table (stored in the rc variable) containing the list of all tables present in the library.

The DO loop : It iterates over the "Name" column of the previously retrieved list.

saveresult : Transforms the in-memory result of the tabledetails action into a real temporary physical CAS table.

Dynamic code construction (code=...) : This is the core of the trick. A character string containing DATA step code is built.

On the first pass, the allResults table is created.

On subsequent passes, append=yes is used to avoid overwriting previous data.

datastep.runcode : Executes the dynamically generated SAS^© code.

After executing this script, you will get a single table named casuser.allResults.

This table will contain detailed metadata (compressed size, block size, index, etc.) for all your tables, with an additional column Data indicating the name of the source table. You can then use this table to create monitoring reports or dashboards on the usage of your CAS space.

Important Disclaimer

The codes and examples provided on WeAreCAS.eu are for educational purposes. It is imperative not to blindly copy-paste them into your production environments. The best approach is to understand the logic before applying it. We strongly recommend testing these scripts in a test environment (Sandbox/Dev). WeAreCAS accepts no responsibility for any impact or data loss on your systems.

Back to article list

Dans : CAS

Promoting CAS Tables: Understanding Scope and Avoiding the "Table not found" Error

24/05/2024 • 7

SAS Viya: How to Properly Manage the Creation and Deletion of CASLIBs

26/03/2024 • 10

Passing Dynamic Parameters to FedSQL in a Custom Action

24/05/2023 • 16

CAS Storage Optimization: Mastering the DVR Format When Loading Data

24/05/2023 • 7

CAS Storage Optimization: Mastering the DVR Format When Loading Data

24/05/2023 • 7

View full category

References & Docs

Cloud Analytic Services table.tabledetails SAS Viya REPORT CASL CASUSER datastep.runcode PROC CAS DATA Step tableInfo

SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration. WeAreCAS is an independent community site and is not affiliated with SAS Institute Inc.