Aggregating in SAS - Efficiency of two approaches

How do you aggregate in SAS? You often have to use some PROC’s to get what you need.

What is aggregating?

Aggregating, if you’re not aware, is simply rolling up information from a lot of records into a more concise record.

Think of it like attempting to sum the number of antidepressants dispensed across year-quarter intervals with a PDE file.

Most beneficiaries taking antidepressants will several/hundreds of records per month over a given period. How can we get them down to one record?

Translating from other languages

If you have a Stata background, I’m talking about using the collapse command.

If you have an SPSS background, I’m talking about the AGGREGATE command.

If you have an R background, I’m talking about the aggregate, dplyr::summarize(), or any other variant of aggregation within R.

Methods of Aggregating

Note: I try to be careful when using the word ‘count’ with aggregating. While counting is a form of aggregation, it can get confusing depending on what research question you are trying to examine. Counting in SAS typically refers to row-by-row processing/indexing, while aggregating usually involves some single measure for a given classification.

Also, I am using a simulated data set of drugs from the DE-SynPUF PDE file. I run two PROC SURVEYSELECT statements with different seeds to get two data sets, and then I pull from each one 1,250 times for a total of 2,500 runs.

I also use Redbook to get the drug names based on the NDC number and remove records that do not have a match in Redbook.

This creates a data set with 12,225,787 records.

Since the size of this data set is too large to post, I will not be able to make this reproducible, but I will make code available that uses data sets in the SASHELP library.

Here are the first ten observations:

PROC SQL

The first and most popular is probably PROC SQL. It’s intuitive, versatile, and efficient with large data sets.

proc sql;
	create table			aggregate_proc_sql_no_count as
		select
					year_qtr,
					gennme,		
					sum(qty_dspnsd_num) as total_rx_prescribed
		from
					base_file
		group by
					year_qtr, formatted_name;
quit;

And if we wanted to get a value count, we just use the COUNT function.

proc sql;
	create table			aggregate_proc_sql_w_count as
		select
					year_qtr, /* #1 */
					gennme, /* #2 */
                    			count(gennme) as freq, /* #3 */
					sum(qty_dspnsd_num) as total_rx_prescribed /* #3 */
		from
					want_year_qtr
		group by
					year_qtr, gennme; /* #4 */
quit;

1. Select the year_qtr variable which is the date the drug was dispensed formatted using the YYQ6. format.
2. Select the gennme variable which represents the generic drug name.
3. COUNT the number of instances of the generic name.
4. Finally, group the results by year_qtr and gennme

We get this (first ten observations):

The issue with the PROC SQL is that it is slower than other SAS procedures when aggregating. Odd, right? But here was the amount of time it took to run the above code:

NOTE: PROCEDURE SQL used (Total process time):
      real time           5.37 seconds
      cpu time            7.76 seconds

PROC MEANS

But most SAS PROC’s require sorting, right?

While you can sort and use the BY statement in PROC MEANS, you can completely avoid the PROC SORT by using the CLASS argument in PROC MEANS. Here’s what I mean:

proc means 
	data = want_year_qtr
	noprint /* #1 */
    	nway; /* #2 */
		class year_qtr formatted_name; /* #3 */
		var qty_dspnsd_num; /* #4 */
	    	output out = aggregate_proc_means /* #5 */
	    		sum = / autoname; /* 6 & 7 */
run;

1. NOPRINT suppresses the default HTML listing. You want to add this so SAS isn’t trying to produce a report containing all the observations.
2. NWAY tells SAS to aggregate to the maximum number of elements in your CLASS statement. This is an oversimplification, but you can read about the formal definition here
   • You can use a WAYS statement instead, but you have to explicitly define how many levels you want the information aggregated to. WAYS 0 will produce overall statistics for the data set, while WAYS 1 will produce overall statistics by year_qtr, and so on.
3. CLASS tells SAS what level of detail you want.
4. I am requesting SAS to calculate statistics for the qty_dspnsd_num variable and that variable only.
   • You can request additional variables here. If you omit this statement, you are going to be returned summary statistics for all numeric variables in the data set.
5. I am outputting a data set to my WORK library called aggregate_proc_means
6. SUM = tells SAS that I want to do with the field listed no the VAR statement. You can choose a variety of statistics here - mean, median, etc. The right side of the argument typically specifies what you want to aggregate. I could add sum(qty_dspnsd_num) = sum if I want to explicitly request that it aggregates a given field. Omitting this implies that you want to SUM the field on the VAR statement. The left side is your summary statistic request, and the right side is the variable name that you want.
7. The / autoname tells SAS that you want to access certain options. Here, I am requesting that SAS automatically names the variables once they’re aggregated. This means the summarized output variable will be prefixed by the requested statistic and the variable name (Sum_qty_dspnsd_num).

The CLASS statement essentially, in my eyes, classifies or cases the records to the level of detail that we desire. In other words, we don’t want patient-level aggregations, we want it cased or rolled-up by year_qtr and formatted_name.

Also, it automatically creates two variables on the output data set: _TYPE_ and _FREQ_. _TYPE_ represents the number of classifications. Notice how _FREQ_ gives the same results as the COUNT function from PROC SQL.

You can drop these fields on your OUT = statement by specifying output out = your_ds_here (drop = _:)

This results in this:

Notice how PROC MEANS retains the original formatting/specificity of the field.

And the amount of time it took on my system (i7-10857H, 32GB RAM, Windows 10):

NOTE: PROCEDURE MEANS used (Total process time):
      real time           1.02 seconds
      cpu time            4.07 seconds

This is much quicker than PROC SQL, and you don’t have to use PROC SORT!

Summary

Overall, while we do like PROC SQL due to it’s translatability and intuitive syntax, you will see that some base SAS procedures can be significantly faster.

This isn’t always the case, but you sometimes have to play around and see.

Also, I would be remiss if I didn’t mention some other ways of aggregating, namely: PROC UNIVARIATE and PROC SUMMARY.

Additionally, the times that I posted are from a single run. Times can certainly vary across multiple runs, but the times will be normally distributed across hundreds of runs – think Central Limit Theorem!

You can find an example program here. Again, since it isn’t practical to post the entire data set, this will just show you and provide you a way to examine how these PROC’s work.

I encourage you to play around with the CLASS statement to see how you can modify the level of aggregation.