Smart Partitioning: How to Filter and Append Large-Scale CAS Tables in One Step

Difficulty Level

Beginner

Published on : 23/03/2025

Expert Advice

Michael
Responsable de l'infrastructure Viya.

When using the WHERE parameter within the table.append action, the filtering logic is pushed down to the CAS nodes where the data resides; this means SAS only moves the relevant rows across the network to the target table, significantly reducing communication overhead compared to filtering the data after it has already been appended.

The CAS 'table.append' action is used to transfer rows from a source table (in-memory or promoted) to an existing target table in CAS. The 'WHERE' parameter in the 'source' option allows specifying a logical condition. Only rows from the source table that satisfy this condition will be added to the target table. Execution occurs in distributed memory on the CAS server, which is optimized for large data volumes. The target table must already exist.

Data Analysis

Type : CREATION_INTERNE

Examples use generated data (datalines) or SASHELP tables loaded into CAS.

1 Code Block

PROC CAS / DATA STEP Data

Explanation :
This example initializes two CAS tables, 'class_source' (from SASHELP.CLASS) and 'class_target' (empty). It then uses 'table.append' to add all rows from 'class_source' to 'class_target' without any filtering condition. Finally, it displays the content of the target table.

Copied!

1	/* Charger la table SASHELP.CLASS en CAS */
2	PROC CASUTIL;
3	load DATA=sashelp.class outcaslib="casuser" casout="class_source" replace;
4	RUN;
5
6	/* Créer une table cible vide avec la même structure en CAS */
7	DATA casuser.class_target;
8	SET casuser.class_source(obs=0);
9	RUN;
10
11	PROC CAS;
12	/* Ajouter toutes les lignes de class_source à class_target */
13	TABLE.append /
14	SOURCE={caslib='casuser', name='class_source'}
15	target={caslib='casuser', name='class_target'};
16	RUN;
17
18	/* Vérifier le contenu de la table cible */
19	PROC PRINT DATA=casuser.class_target;
20	RUN;

2 Code Block

PROC CAS / DATA STEP Data

Explanation :
This example loads the complete SASHELP.CLASS table into CAS as 'class_full'. It creates a 'class_filtered' table with some initial observations. Then, it uses 'table.append' with the 'where="Age >= 15"' parameter to add only the observations from 'class_full' where the age is 15 or older to the 'class_filtered' table. The final content of 'class_filtered' is displayed.

Copied!

1	/* Charger la table SASHELP.CLASS en CAS */
2	PROC CASUTIL;
3	load DATA=sashelp.class outcaslib="casuser" casout="class_full" replace;
4	RUN;
5
6	/* Créer une table cible avec quelques données initiales */
7	DATA casuser.class_filtered;
8	INPUT Name $ Sex $ Age Height Weight;
9	DATALINES;
10	Alfred M 14 69.0 112.5
11	Alice F 13 56.5 84.0
12	;
13	RUN;
14
15	PROC CAS;
16	/* Ajouter uniquement les étudiants dont l'âge est supérieur ou égal à 15 ans */
17	TABLE.append /
18	SOURCE={caslib='casuser', name='class_full', where="Age >= 15"}
19	target={caslib='casuser', name='class_filtered'};
20	RUN;
21
22	/* Vérifier le contenu de la table cible */
23	PROC PRINT DATA=casuser.class_filtered;
24	RUN;

3 Code Block

PROC CAS / DATA STEP Data

Explanation :
This example demonstrates an advanced case where data is first transformed into a new CAS table ('students_processed') by calculating an average score and status. Then, 'table.append' is used to add only the observations from 'students_processed' that satisfy a complex 'WHERE' condition to the 'high_achievers' table: the status must be 'Excellent' AND the gender must be 'F'. This illustrates how to combine data preparation steps with conditional appending.

Copied!

1	/* Créer une table source avec des données brutes en CAS */
2	DATA casuser.students_raw;
3	INPUT StudentID Name $ Gender $ Score1 Score2;
4	DATALINES;
5	101 Jean M 85 92
6	102 Marie F 78 88
7	103 Pierre M 91 75
8	104 Sophie F 65 95
9	105 Paul M 80 81
10	;
11	RUN;
12
13	/* Créer une table cible avec des données initiales */
14	DATA casuser.high_achievers;
15	INPUT StudentID Name $ Gender $ AverageScore STATUS$;
16	DATALINES;
17	201 Marc M 90 Initial
18	;
19	RUN;
20
21	PROC CAS;
22	/* Calculer le score moyen et déterminer un statut avant d'ajouter */
23	DATA casuser.students_processed;
24	SET casuser.students_raw;
25	AverageScore = (Score1 + Score2) / 2;
26	IF AverageScore >= 85 THEN STATUS = 'Excellent';
27	ELSE STATUS = 'Good';
28	RUN;
29
30	/* Ajouter uniquement les étudiants 'Excellent' et de sexe féminin */
31	TABLE.append /
32	SOURCE={caslib='casuser', name='students_processed', where="Status = 'Excellent' AND Gender = 'F'"}
33	target={caslib='casuser', name='high_achievers'};
34	RUN;
35
36	/* Vérifier le contenu de la table cible */
37	PROC PRINT DATA=casuser.high_achievers;
38	RUN;

4 Code Block

PROC CAS / DATA STEP Data

Explanation :
This example creates a temporary SAS table 'temp_large' with 1000 observations, then loads and 'promotes' it as 'large_data_source' in CAS. It also creates an empty promoted target table 'filtered_promoted'. It then uses 'table.append' to add to 'filtered_promoted' only the rows where 'Value < 10'. The Viya/CAS aspect is highlighted by the use of promoted tables which are persistent beyond the current session, and a final cleanup block to drop all CAS tables created during the examples.

Copied!

1	/* Créer une table source temporaire avec un grand nombre d'observations */
2	DATA _null_;
3	DO i = 1 to 1000;
4	ID = i;
5	Value = mod(i, 100);
6	OUTPUT;
7	END;
8	RUN;
9
10	PROC CASUTIL;
11	load DATA=work.temp_large outcaslib="casuser" casout="large_data_source" promote replace;
12	RUN;
13
14	/* Créer une table cible promotée vide */
15	PROC CASUTIL;
16	load DATA=work.temp_large(obs=0) outcaslib="casuser" casout="filtered_promoted" promote replace;
17	RUN;
18
19	PROC CAS;
20	/* Ajouter des lignes à la table promotée 'filtered_promoted' avec une condition WHERE */
21	/* L'utilisation de 'promote' assure la persistance de la table au-delà de la session */
22	TABLE.append /
23	SOURCE={caslib='casuser', name='large_data_source', where="Value < 10"}
24	target={caslib='casuser', name='filtered_promoted'};
25	RUN;
26
27	/* Vérifier le contenu de la table cible promotée */
28	PROC PRINT DATA=casuser.filtered_promoted;
29	RUN;
30
31	/* Nettoyage : supprimer les tables CAS après utilisation */
32	PROC CAS;
33	TABLE.dropTable / caslib='casuser', name='large_data_source';
34	TABLE.dropTable / caslib='casuser', name='filtered_promoted';
35	TABLE.dropTable / caslib='casuser', name='students_raw';
36	TABLE.dropTable / caslib='casuser', name='high_achievers';
37	TABLE.dropTable / caslib='casuser', name='class_source';
38	TABLE.dropTable / caslib='casuser', name='class_target';
39	TABLE.dropTable / caslib='casuser', name='class_full';
40	TABLE.dropTable / caslib='casuser', name='class_filtered';
41	TABLE.dropTable / caslib='casuser', name='students_processed';
42	RUN;

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.

Difficulty Level

Published on : 23/03/2025

Expert Advice

Data Analysis

1 Code Block

2 Code Block

3 Code Block

4 Code Block

Related Documentation