Updating Rows in a CAS Table

The `table.update` action from the 'table' action set is used to modify data in an in-memory CAS table. It is particularly useful for transactional or bulk update operations. Updates can be performed on the entire table or on a subset of rows specified by a WHERE clause. New column values can be literal constants, expressions based on other columns in the table, or a combination of both. It is important to note that literal character values (such as 'Sport Sedan' or '31,000') are automatically converted if the target column has a compatible numeric type.

Data Analysis

Type : CREATION_INTERNE

Examples use generated data (datalines) or SASHELP, then uploaded to CAS for manipulation.

1 Code Block

PROC CAS / DATA step Data

Explanation :
This example initializes a `produits` table in CAS memory. Then, it uses the `table.update` action to increase the 'Prix' of all products by 5%. The `table.fetch` action displays the updated results.

Copied!

1	DATA mycas.produits;
2	LENGTH NomProduit $30 Categorie $20 Statut $10;
3	INPUT NomProduit Categorie Prix Statut $;
4	DATALINES;
5	Ordinateur Portable Electronique 1200 EnStock
6	Smartphone Electronique 800 EnStock
7	Souris Accessoire 25 EnStock
8	Clavier Accessoire 75 Rupture
9	Ecran Electronique 300 EnStock
10	Tablette Electronique 500 EnStock
11	;
12	RUN;
13
14	PROC CAS;
15	SESSION casauto;
16	TABLE.promote / caslib='mycas' name='produits';
17	TABLE.update /
18	TABLE='produits',
19	SET={
20	{var='Prix', value='Prix * 1.05'}
21	};
22	TABLE.fetch / TABLE='produits';
23	QUIT;

2 Code Block

PROC CAS

Explanation :
This example updates products where 'Statut' is 'Rupture'. For these products, 'Prix' is reduced by 10% and 'Statut' is changed to 'EnStock'. This demonstrates a conditional update on multiple columns.

Copied!

1	PROC CAS;
2	SESSION casauto;
3
4	TABLE.update /
5	TABLE='produits',
6	where="Statut = 'Rupture'",
7	SET={
8	{var='Prix', value='Prix * 0.90'},
9	{var='Statut', value="'EnStock'"}
10	};
11	TABLE.fetch / TABLE='produits';
12	QUIT;

3 Code Block

PROC CAS / %LET

Explanation :
This example uses macro variables to make the update more dynamic. Products in a specific category (here 'Electronique') have their price increased by 15% and their 'NomProduit' prefixed with 'MAJ-'. This illustrates the use of complex expressions and macro variables.

Copied!

1	%let CATEGORIE_CIBLE = 'Electronique';
2	%let POURCENTAGE_AUGMENTATION = 1.15;
3
4	PROC CAS;
5	SESSION casauto;
6
7	TABLE.update /
8	TABLE='produits',
9	where="Categorie = &CATEGORIE_CIBLE.",
10	SET={
11	{var='Prix', value="Prix * &POURCENTAGE_AUGMENTATION."},
12	{var='NomProduit', value="'MAJ-' \|\| NomProduit"}
13	};
14	TABLE.fetch / TABLE='produits' / to=100; /* Fetch toutes les lignes pour vérification */
15	QUIT;

4 Code Block

PROC CAS Data

Explanation :
This scenario shows how the `table.update` action can be used for validations. The first block attempts to update a non-existent product, demonstrating return handling. The second block updates the status of products with stock less than or equal to 10, changing the numeric 'Stock' value to a character string 'Faible'. This illustrates the use of the `input` function for type conversion in the `where` clause.

Copied!

1	DATA mycas.stock_produits;
2	LENGTH NomProduit $30 Stock Initial $10;
3	INPUT NomProduit Stock $;
4	DATALINES;
5	Ordinateur Portable 10
6	Smartphone 50
7	Souris 200
8	Clavier 0
9	Ecran 30
10	Tablette 5
11	;
12	RUN;
13
14	PROC CAS;
15	SESSION casauto;
16	TABLE.promote / caslib='mycas' name='stock_produits';
17
18	/* Tentative de mise à jour pour un produit non existant */
19	r = TABLE.update {
20	TABLE='produits',
21	where="NomProduit = 'ProduitInexistant'",
22	SET={
23	{var='Prix', value='Prix + 100'}
24	}
25	};
26	PRINT r;
27
28	/* Mise à jour conditionnelle avec vérification de stock */
29	r = TABLE.update {
30	TABLE='stock_produits',
31	where="input(Stock, ?? best.) <= 10",
32	SET={
33	{var='Stock', value="'Faible'"}
34	}
35	};
36	PRINT r;
37	TABLE.fetch / TABLE='stock_produits';
38	QUIT;

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.

Retour à la liste

Expert Advice

Stéphanie

Spécialiste Machine Learning et IA.

« If you need to update a table based on values found in another table, consider using a CAS Data Step with a modify or merge statement, as table.update is designed for self-contained logic and constant-based expressions. »