Sometimes it feels like there's a CAS action for everything ("There's a CAS action for that!"). However, they're not always named or formed like you might expect. For example, to create a match code you might need to use the DATA Step CAS action, and, to load a CAS table into a database, you might need to use the SAVE action.
If you're a database guy, you might expect CAS to have an explicitly named "Truncate" action but it does not. Starting back in 2021.1.2, CAS supports table trunctation with the DeleteRows action. To trigger truncation you specify a WHERE clause with an (always) TRUE condition, i.e. WHERE = '1'. ("Delete Rows Where True." Since the WHERE clause always resolves to TRUE, every record gets deleted. Get it?) So truncating a CAS table looks like this:
How is this different than just deleting all of the table's rows? You could do that before with the DeleteRows action. The difference boils down to performance. Like any other data processing task, deleting rows from a table can be process intensive. CAS has to locate the records according to WHERE conditions and mark each as logically deleted. Truncation minimizes the impact on system resources by manipulating the table definition instead of the table data. Using CAS' internal table management mechanisms, truncation marks the table as empty and frees the storage space instead of processing the table records. Thus truncation can be orders of magnitude faster than deleting all of the rows.
How much faster is truncating versus explicitly deleting all a CAS table's records? Let's look...
In this example, we have an ~80 million row CAS table. First we'll delete all of the rows with a data value subsetting WHERE clause and then we'll truncate the table. As you can see from the screen shots, deleting all of the rows takes around 6 minutes while truncating the table takes around 6 seconds.
And, why do we care about table truncation? For near-real time CAS table updates of course, namely using the Truncate and Append method.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.