The alterTable action allows for modifying the metadata and properties of an in-memory CAS table. This includes renaming the table, changing its label, modifying column properties such as names, labels, and formats, as well as dropping columns or reordering them. It is a versatile tool for managing table structures directly on the CAS server without needing to recreate tables.
| Parameter | Description |
|---|---|
| caslib | Specifies the caslib for the input table. By default, the active caslib is used. |
| columnOrder | Specifies the order of columns in the table's metadata. |
| columns | Specifies a list of dictionaries, each defining alterations for a specific column (e.g., renaming, dropping, changing format/label). |
| drop | Specifies a list of column names to be dropped from the table. |
| keep | Specifies a list of column names to be kept in the table; all other columns are dropped. |
| label | Assigns a new label to the table. |
| lifetime | Specifies the number of seconds to keep the table in memory after its last access. After this period, the table is dropped. |
| name | Specifies the name of the table to be altered. |
| rename | Specifies a new name for the table. |
| tableRedistUpPolicy | Specifies the table redistribution policy when the number of worker pods increases on a running CAS server. Can be 'DEFER', 'NOREDIST', or 'REBALANCE'. |
This code creates a sample table 'CARS_DATA' in the 'casuser' caslib, which will be used in the following examples. The table contains information about different car models.
| 1 | DATA casuser.CARS_DATA; |
| 2 | LENGTH Make $ 15 Model $ 20 Type $ 10; |
| 3 | INFILE DATALINES delimiter=','; |
| 4 | INPUT Make $ Model $ Type $ MSRP Horsepower; |
| 5 | DATALINES; |
| 6 | Toyota,Camry,Sedan,24000,203 |
| 7 | Ford,Mustang,Coupe,27000,310 |
| 8 | Honda,CR-V,SUV,25000,190 |
| 9 | Chevrolet,Silverado,Truck,30000,355 |
| 10 | Tesla,Model 3,Sedan,40000,283 |
| 11 | ; |
| 12 | RUN; |
This example renames the 'CARS_DATA' table to 'AUTOMOBILES'.
| 1 | |
| 2 | PROC CAS; |
| 3 | TABLE.alterTable / caslib='casuser' name='CARS_DATA' rename='AUTOMOBILES'; |
| 4 | |
| 5 | RUN; |
| 6 |
This example adds the label 'Car Information' to the 'AUTOMOBILES' table.
| 1 | |
| 2 | PROC CAS; |
| 3 | TABLE.alterTable / caslib='casuser' name='AUTOMOBILES' label='Car Information'; |
| 4 | |
| 5 | RUN; |
| 6 |
This example drops the 'MSRP' column from the 'AUTOMOBILES' table.
| 1 | |
| 2 | PROC CAS; |
| 3 | TABLE.alterTable / caslib='casuser' name='AUTOMOBILES' drop={'MSRP'}; |
| 4 | |
| 5 | RUN; |
| 6 |
This example demonstrates altering multiple columns at once. It renames 'Horsepower' to 'HP', adds a label to 'HP', and applies a currency format to the 'MSRP' column (assuming it wasn't dropped).
| 1 | |
| 2 | PROC CAS; |
| 3 | TABLE.alterTable / caslib='casuser' name='AUTOMOBILES' columns={{name='Horsepower', rename='HP', label='Engine Horsepower'}, {name='MSRP', FORMAT='DOLLAR8.'}}; |
| 4 | |
| 5 | RUN; |
| 6 |
This example drops the 'Type' and 'MSRP' columns and then specifies a new order for the remaining columns in the table metadata.
| 1 | |
| 2 | PROC CAS; |
| 3 | TABLE.alterTable / caslib='casuser' name='AUTOMOBILES' drop={'Type', 'MSRP'} columnOrder={'Model', 'Make', 'HP'}; |
| 4 | |
| 5 | RUN; |
| 6 |
This example sets the 'AUTOMOBILES' table to be automatically dropped from memory if it is not accessed for 600 seconds (10 minutes).
| 1 | |
| 2 | PROC CAS; |
| 3 | TABLE.alterTable / caslib='casuser' name='AUTOMOBILES' lifetime=600; |
| 4 | |
| 5 | RUN; |
| 6 |
A marketing team has just received a new list of customer leads. The data is structurally sound but uses inconsistent and non-standard column names and formats. The goal is to c...
A financial analyst needs to create a summary report on customer transactions. The source table contains sensitive Personally Identifiable Information (PII) and other irrelevant...
An administrator is managing a CAS environment that processes large volumes of IoT sensor data. A daily raw table needs to be processed, its columns reordered for query efficien...