When you design a SAS job to prepare data for analysis, you often use the same common data manipulation techniques to organize, optimize, customize, tweak your data before moving to the next steps. And you have been doing that for years, regardless of the SAS version, regardless of the storage layer, …
So now, with CAS, is that different? No, it’s not. Or maybe a little.
Let’s have a look at some common data manipulation techniques (append, sort, de-duplicate and aggregate) that require some attention when done in a CAS context. We have seen some mistakes in recent SAS Viya implementations. In this first part, we’ll focus on appending data.
Appending data in CAS
Appending a secondary data set to a primary data set means adding observations from the secondary data set to the end of the primary data set without reading the primary data set (which can be big). This is what makes APPEND more efficient than concatenating 2 data sets and storing the results into a brand new data set.
When we think about appending data in the SAS world, we first think about PROC APPEND. However, PROC APPEND does not work in the CAS world. You’ll get an error that the CAS table does not support update access (which is not exactly true as you will see later).
73 proc append base=casdm.bigprdsale data=casdm.smallprdsale ;
ERROR: Update access is not supported for file CASDM.BIGPRDSALE.DATA.
74 run ;
Forget about PROC APPEND. What are the solutions then?
Before SAS Viya 3.4
Appending data to a CAS table was initially possible when the records to be added were coming from the client (client-side append😞
proc casutil ;
load data=work.smallprdsale casout="bigprdsale" outcaslib="DM" append ;
run ;
quit ;
Here, in PROC CASUTIL, you can use the append option only with the load data statement (and not the load casdata statement). The table work.smallprdsale is on the SPRE machine (or SAS machine if you are using SAS 9.4).
If you wanted to append a CAS table to another CAS table, you had to first download the CAS table on the SPRE machine.
The second option was to use a DATA Step with the APPEND data set option:
data casdm.bigprdsale(append=yes) ;
set casdm.smallprdsale ;
run ;
But, although it looked “CAS-enabled” when both the “data” and “set” tables were CAS tables, it was still a client-side append. The “set” table was first downloaded in SPRE (or SAS) before moving back in CAS to be added to the “data” table, with some size limitations. If the “set” table was bigger than 100MB, the user had to adjust the DATALIMIT option.
Both options were fine when the data set to be added was small. When it started to be big (gigabytes sized tables) then some performance issues started to arise.
A third option was to concatenate the 2 input tables to create a brand new table (and possibly replace one of the 2 input tables):
data casdm.bigprdsale ;
set casdm.bigprdsale casdm.smallprdsale ;
run ;
As mentioned earlier, this is not as efficient as a true APPEND because the “bigprdsale” table is read entirely, but it runs in CAS without any data movement between SPRE and CAS. Also, notice that it doesn’t work with global tables. If “bigprdsale” is global, you have to:
- Give a temporary name to the output table in this DATA Step
- Drop the old table once the data step is done (dropTable CAS action)
- Rename the new table with the original name (alterTable CAS action)
Now in SAS Viya 3.4
The DATA Step with the APPEND data set option has been extended to support “server-side” APPEND. So, this is the only way to go to append a CAS table to another CAS table efficiently without triggering any data movement. It works even if the target table is global. The restriction is to have the exact same table structure.
data casdm.bigprdsale(append=yes) ;
set casdm.smallprdsale ;
run ;
No more question about appending data in CAS. The best practice is to use the DATA Step with the APPEND data set option.
Takeaways
- PROC APPEND does not work on CAS data
- Best practice: use the DATA Step with the APPEND data set option
- Mistake to avoid: download the “secondary” CAS table on the client (SPRE or SAS) before appending it to the “primary” CAS table using a client-side technique
Thanks for reading and stay tune for next article on sorting data.
