The `table.append` action is used to combine two CAS tables by adding the content of the source table to the target table. It is particularly useful for incrementing datasets without having to reload the entire target table. Source and target tables must have compatible structures for the operation to succeed, although CAS can handle some differences in column types or lengths. If the tables are not fully compatible, warnings or errors may occur. The action executes within the context of a CAS session and can be invoked via PROC CAS in SAS©, or directly via CASL, Lua, Python, or R APIs.
Data Analysis
Type : CREATION_INTERNE
The examples use generated data (DATA step with direct assignment) to create temporary CAS tables.
1 Code Block
DATA STEP / PROC CAS Data
Explanation :
This example shows the simplest use of `table.append`. Two tables, `t1` (source) and `t2` (target), are created with identical structures. The `table.append` action is then called to add all rows from `t1` to the end of `t2`. Finally, `proc print` is used to verify the content of table `t2` after the append operation.
This example shows the simplest use of `table.append`. Two tables, `t1` (source) and `t2` (target), are created with identical structures. The `table.append` action is then called to add all rows from `t1` to the end of `t2`. Finally, `proc print` is used to verify the content of table `t2` after the append operation.
Copied!
| 1 | /* Préparation de l'environnement CAS */ |
| 2 | options cashost="cloud.example.com" casport=5570; |
| 3 | cas casauto; |
| 4 | LIBNAME mycas cas caslib="CASUSER(your-username)"; |
| 5 | |
| 6 | /* Création de la table cible (t2) */ |
| 7 | DATA mycas.t2; |
| 8 | LENGTH id 8 nom $10 ville $10; |
| 9 | id = 1; |
| 10 | nom = 'Alice'; |
| 11 | ville = 'Paris'; |
| 12 | OUTPUT; |
| 13 | id = 2; |
| 14 | nom = 'Bob'; |
| 15 | ville = 'Lyon'; |
| 16 | OUTPUT; |
| 17 | RUN; |
| 18 | |
| 19 | /* Création de la table source (t1) */ |
| 20 | DATA mycas.t1; |
| 21 | LENGTH id 8 nom $10 ville $10; |
| 22 | id = 3; |
| 23 | nom = 'Charlie'; |
| 24 | ville = 'Marseille'; |
| 25 | OUTPUT; |
| 26 | RUN; |
| 27 | |
| 28 | /* Ajout de la table t1 à la table t2 */ |
| 29 | PROC CAS; |
| 30 | TABLE.append / |
| 31 | SOURCE={caslib='CASUSER(your-username)',name='t1'} |
| 32 | target={caslib='CASUSER(your-username)',name='t2'}; |
| 33 | RUN; |
| 34 | |
| 35 | /* Affichage de la table t2 mise à jour */ |
| 36 | PROC PRINT DATA=mycas.t2; RUN; |
2 Code Block
DATA STEP / PROC CAS Data
Explanation :
This example uses the `table.append` action with tables of slightly different structures. The source table `nouveaux_clients` contains a `pays` column that does not exist in the target table `clients`. CAS will handle this incompatibility by ignoring the `pays` column during the append. The `promote=TRUE` option on the target table ensures that the updated table is visible to all users with appropriate permissions in the CAS session. The `noAutoPromote=FALSE` option is included to ensure that if 'promote' is used, it overrides automatic promotion if it is disabled by default.
This example uses the `table.append` action with tables of slightly different structures. The source table `nouveaux_clients` contains a `pays` column that does not exist in the target table `clients`. CAS will handle this incompatibility by ignoring the `pays` column during the append. The `promote=TRUE` option on the target table ensures that the updated table is visible to all users with appropriate permissions in the CAS session. The `noAutoPromote=FALSE` option is included to ensure that if 'promote' is used, it overrides automatic promotion if it is disabled by default.
Copied!
| 1 | /* Préparation de l'environnement CAS */ |
| 2 | options cashost="cloud.example.com" casport=5570; |
| 3 | cas casauto; |
| 4 | LIBNAME mycas cas caslib="CASUSER(your-username)"; |
| 5 | |
| 6 | /* Création de la table cible (clients) */ |
| 7 | DATA mycas.clients; |
| 8 | LENGTH id 8 nom $10 age 8; |
| 9 | id = 1; |
| 10 | nom = 'Alice'; |
| 11 | age = 30; |
| 12 | OUTPUT; |
| 13 | RUN; |
| 14 | |
| 15 | /* Création de la table source (nouveaux_clients) avec une colonne supplémentaire */ |
| 16 | DATA mycas.nouveaux_clients; |
| 17 | LENGTH id 8 nom $10 age 8 pays $10; |
| 18 | id = 3; |
| 19 | nom = 'Charlie'; |
| 20 | age = 25; |
| 21 | pays = 'France'; |
| 22 | OUTPUT; |
| 23 | RUN; |
| 24 | |
| 25 | /* Ajout avec l'option 'casout' et 'promote' pour rendre la table visible et 'noAutoPromote=FALSE' */ |
| 26 | PROC CAS; |
| 27 | TABLE.append / |
| 28 | SOURCE={caslib='CASUSER(your-username)',name='nouveaux_clients'} |
| 29 | target={caslib='CASUSER(your-username)',name='clients', promote=TRUE, noAutoPromote=FALSE}; |
| 30 | RUN; |
| 31 | |
| 32 | /* Affichage de la table clients mise à jour */ |
| 33 | PROC PRINT DATA=mycas.clients; RUN; |
3 Code Block
DATA STEP / PROC CAS Data
Explanation :
This advanced example demonstrates how to rename columns from the source table on the fly before the append operation. The `commandes_recentes` table has different column names than `commandes_historiques`. The `vars` option in the `source` parameter is used to specify the column mapping (`rename='new_name'`). This ensures that the source data is correctly aligned with the target structure.
This advanced example demonstrates how to rename columns from the source table on the fly before the append operation. The `commandes_recentes` table has different column names than `commandes_historiques`. The `vars` option in the `source` parameter is used to specify the column mapping (`rename='new_name'`). This ensures that the source data is correctly aligned with the target structure.
Copied!
| 1 | /* Préparation de l'environnement CAS */ |
| 2 | options cashost="cloud.example.com" casport=5570; |
| 3 | cas casauto; |
| 4 | LIBNAME mycas cas caslib="CASUSER(your-username)"; |
| 5 | |
| 6 | /* Création de la table cible (commandes_historiques) */ |
| 7 | DATA mycas.commandes_historiques; |
| 8 | LENGTH id_commande 8 article $15 quantite 8 date_achat $10; |
| 9 | id_commande = 101; |
| 10 | article = 'Livre A'; |
| 11 | quantite = 2; |
| 12 | date_achat = '2023-01-15'; |
| 13 | OUTPUT; |
| 14 | RUN; |
| 15 | |
| 16 | /* Création d'une table source (commandes_recentes) avec des colonnes à renommer/filtrer */ |
| 17 | DATA mycas.commandes_recentes; |
| 18 | LENGTH order_id 8 product_name $15 qty 8 purchase_date $10; |
| 19 | order_id = 102; |
| 20 | product_name = 'Stylo B'; |
| 21 | qty = 5; |
| 22 | purchase_date = '2023-02-20'; |
| 23 | OUTPUT; |
| 24 | order_id = 103; |
| 25 | product_name = 'Cahier C'; |
| 26 | qty = 1; |
| 27 | purchase_date = '2023-03-01'; |
| 28 | OUTPUT; |
| 29 | RUN; |
| 30 | |
| 31 | /* Ajout après renommage des colonnes dans la source pour correspondre à la cible */ |
| 32 | PROC CAS; |
| 33 | TABLE.append / |
| 34 | SOURCE={caslib='CASUSER(your-username)', name='commandes_recentes', |
| 35 | vars={{name='order_id', rename='id_commande'}, |
| 36 | {name='product_name', rename='article'}, |
| 37 | {name='qty', rename='quantite'}, |
| 38 | {name='purchase_date', rename='date_achat'}} |
| 39 | } |
| 40 | target={caslib='CASUSER(your-username)', name='commandes_historiques'}; |
| 41 | RUN; |
| 42 | |
| 43 | /* Affichage de la table commandes_historiques mise à jour */ |
| 44 | PROC PRINT DATA=mycas.commandes_historiques; RUN; |
4 Code Block
DATA STEP / PROC CAS Data
Explanation :
This example explores handling incompatible data types during append. Initially, the source table `nouveaux_produits` has the `prix` column as a character string, while the target table `produits` has `prix` as numeric. A direct attempt to append with `addCols=TRUE` (which by default handles missing columns) could fail or result in implicit type conversion with data loss if the types are too different. The solution shown is to create an intermediate `DATA` step (`nouveaux_produits_clean`) to explicitly convert the `prix` column from the source to numeric before proceeding with the append. This ensures type compatibility and successful operation.
This example explores handling incompatible data types during append. Initially, the source table `nouveaux_produits` has the `prix` column as a character string, while the target table `produits` has `prix` as numeric. A direct attempt to append with `addCols=TRUE` (which by default handles missing columns) could fail or result in implicit type conversion with data loss if the types are too different. The solution shown is to create an intermediate `DATA` step (`nouveaux_produits_clean`) to explicitly convert the `prix` column from the source to numeric before proceeding with the append. This ensures type compatibility and successful operation.
Copied!
| 1 | /* Préparation de l'environnement CAS */ |
| 2 | options cashost="cloud.example.com" casport=5570; |
| 3 | cas casauto; |
| 4 | LIBNAME mycas cas caslib="CASUSER(your-username)"; |
| 5 | |
| 6 | /* Création de la table cible (produits) */ |
| 7 | DATA mycas.produits; |
| 8 | LENGTH ref 8 nom_produit $15 prix 8; |
| 9 | ref = 1; |
| 10 | nom_produit = 'Pommes'; |
| 11 | prix = 1.50; |
| 12 | OUTPUT; |
| 13 | RUN; |
| 14 | |
| 15 | /* Création d'une table source (nouveaux_produits) avec type de données incompatible pour 'prix' */ |
| 16 | DATA mycas.nouveaux_produits; |
| 17 | LENGTH ref 8 nom_produit $15 prix $10; |
| 18 | ref = 2; |
| 19 | nom_produit = 'Poires'; |
| 20 | prix = '2.25 EUR'; /* Prix comme chaîne de caractères */ |
| 21 | OUTPUT; |
| 22 | ref = 3; |
| 23 | nom_produit = 'Bananes'; |
| 24 | prix = '1.80'; /* Prix comme chaîne de caractères */ |
| 25 | OUTPUT; |
| 26 | RUN; |
| 27 | |
| 28 | /* Tentative d'ajout avec l'option 'addCols=FALSE' pour ignorer les colonnes incompatibles, ou 'addCols=TRUE' pour tenter d'ajouter les colonnes */ |
| 29 | /* Dans cet exemple, nous allons d'abord illustrer un échec potentiel en omettant 'addCols' ou en l'utilisant avec 'promote=FALSE' sur source, puis une solution. */ |
| 30 | /* Tentative échouée si les types sont trop différents et sans gestion spécifique */ |
| 31 | PROC CAS; |
| 32 | TABLE.append / |
| 33 | SOURCE={caslib='CASUSER(your-username)',name='nouveaux_produits'} |
| 34 | target={caslib='CASUSER(your-username)',name='produits'} |
| 35 | addCols=TRUE; /* Tente d'ajouter de nouvelles colonnes si elles n'existent pas */ |
| 36 | /* Si les colonnes existent mais ont des types incompatibles, cela peut échouer ou entraîner une conversion de type */ |
| 37 | RUN; |
| 38 | |
| 39 | /* Pour gérer l'incompatibilité de type sur 'prix', on peut la renommer ou la supprimer/convertir dans la source */ |
| 40 | /* Ici, nous allons convertir 'prix' en numérique avant l'ajout */ |
| 41 | DATA nouveaux_produits_clean; |
| 42 | SET mycas.nouveaux_produits; |
| 43 | prix_num = INPUT(prix, BEST.); /* Convertit la chaîne en numérique */ |
| 44 | drop prix; |
| 45 | rename prix_num=prix; |
| 46 | RUN; |
| 47 | |
| 48 | /* Ajout avec la table source nettoyée */ |
| 49 | PROC CAS; |
| 50 | TABLE.append / |
| 51 | SOURCE={caslib='CASUSER(your-username)',name='nouveaux_produits_clean'} |
| 52 | target={caslib='CASUSER(your-username)',name='produits'}; |
| 53 | RUN; |
| 54 | |
| 55 | /* Affichage de la table produits mise à jour */ |
| 56 | PROC PRINT DATA=mycas.produits; 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.
