Working Efficiently with Large Data

SAS processes tables from disk, so it handles data far larger than memory — but the way you write a step decides whether it takes seconds or hours.

Read less

• Filter early with WHERE — set big(where=(year = 2024)); skips rows before they reach the step; a subsetting IF reads them all first.
• Keep only the columns you need — set big(keep=id year amount); reads less from disk.
• Don't copy data needlessly — a DATA step that only renames or relabels rewrites the whole table; PROC DATASETS with modify changes attributes in place.

Turn on options fullstimer; to see real time, CPU time, and memory for every step in the log.

Indexes

An index speeds up WHERE queries that select a small fraction of rows, and lets BY work without sorting:

/* illustrative */
proc datasets library=mydata nolist;
   modify transactions;
   index create customer_id;
quit;

Indexes help when a query selects a few percent of rows or less; for queries that read most rows, a sequential scan is faster.

Hash objects: lookups without sorting

A hash object loads a small table into memory for instant key lookups — a fast alternative to sorting both tables and merging. Using customers and orders from Level 2:

data orders_named;
   if 0 then set customers;
   if _n_ = 1 then do;
      declare hash h(dataset: 'customers');
      h.defineKey('id');
      h.defineData('name');
      h.defineDone();
   end;
   set orders;
   if h.find() ne 0 then name = '';
run;

proc print data=orders_named noobs;
run;

id    name     amount

 1    Ada        50
 1    Ada        20
 3    Grace      75
 4               10

• if 0 then set customers; never executes, but at compile time it adds id and name to the PDV with the right types and lengths.
• declare hash runs once (_n_ = 1) and loads customers into memory.
• h.find() looks up the current id; it returns 0 when found and copies name into the PDV.
• Variables that come from a SET data set are retained, so on a failed lookup name would keep the previous row's value — hence the explicit name = ''.

Neither table needs sorting, and the large table is read only once. Hash objects also support iteration, counting, and de-duplication.

Other techniques

• Compression — options compress=yes; shrinks tables with long character variables, trading CPU for disk I/O.
• Database pass-through — with data in Oracle, SQL Server, Snowflake, and so on, let the database do the filtering and joining. SAS translates simple WHERE clauses automatically; explicit pass-through (connect to ... ; select * from connection to ...) sends native SQL.
• Avoid repeated sorts — sort once and keep the sorted copy, or use CLASS instead of BY in procedures.
• Views — data v / view=v; or create view define a query that runs only when read, saving a copy of the data.