Pay close attention to Quoting Rules when updating character variables (as seen in your second code example). Because the value= parameter expects an expression string, you must "double-quote" string literals.
Incorrect: value='EnStock' (CAS interprets EnStock as a column name).
Correct: value="'EnStock'" (CAS interprets 'EnStock' as a string literal). If you are using SAS Macro variables, the outer quotes must be double quotes so the macro resolves, meaning you might end up with value="'" || "&StatusMacro" || "'" to be safe!
Think of table.update as your "SQL UPDATE" command for the CAS environment.
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.
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.
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.
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!
data mycas.stock_produits;
length NomProduit $30 Stock Initial $10;
input NomProduit Stock $;
datalines;
Ordinateur Portable 10
Smartphone 50
Souris 200
Clavier 0
Ecran 30
Tablette 5
;
run;
proc cas;
session casauto;
table.promote / caslib='mycas' name='stock_produits';
/* Tentative de mise à jour pour un produit non existant */
r = table.update {
table='produits',
where="NomProduit = 'ProduitInexistant'",
set={
{var='Prix', value='Prix + 100'}
}
};
print r;
/* Mise à jour conditionnelle avec vérification de stock */
r = table.update {
table='stock_produits',
where="input(Stock, ?? best.) <= 10",
set={
{var='Stock', value="'Faible'"}
}
};
print r;
table.fetch / table='stock_produits';
quit;
/* 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;
Pro Tip
Since table.update modifies the table in place, there is no "undo" button (no rollback). For critical production tables, always ensure you have a backup copy or use a temporary table to test your logic first.
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.
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.
This site uses technical and analytical cookies to improve your experience.
Read more.
