Thursday, May 19, 2016

Hack 3.8 Deriving Summary Statistics with PROC SQL

SAS Programming Professionals,

Did you know that you can use PROC SQL to quickly derive summary statistics from SAS data sets? 

You can then use those summary statistics in subsequent DATA steps and procedures.  Here is an example:

 proc sql noprint;
        select count(name), avg(age), min(height), max(height),sum(weight)
        into   :totalobs,   :avgage,  :minheight, :maxheight,   :totweight
        from sashelp.class;

%put "Number of observations = " &totalobs;
%put "Average age = " &avgage;
%put "Minimum height = " &minheight;
%put "Maximum height = " &maxheight;
%put "Total weight of class = " &totweight;

data class_stats;
set  sashelp.class;

format percent_weight percent.;

percent_weight = weight/&totweight;

abovemin_height = height - &minheight;

belowmax_height = &maxheight - height;

if age > &avgage then older = "Yes";
        else older = "No ";


proc print noobs data=class_stats;
title1 "Selected Class Comparisons";

In the SQL Procedure above, we specify various summary statistics for five variables and store them INTO macro variables.  (The NOPRINT options keeps the List destination clean).  Next, we print the values of the macro variables to the SAS log to document the summary statistics.  Then, we use the newly minted macro variables in a DATA step to derive the values of four new variables.

The first portion of the output looks like this:

                         Selected Class Comparisons

                                    percent_    abovemin_    belowmax_
Name    Sex  Age  Height  Weight     weight       height       height     older

Alfred   M    14   69.0    112.5       6%          17.7          3.0       Yes
Alice    F    13   56.5     84.0       4%           5.2         15.5       No
Barbara  F    13   65.3     98.0       5%          14.0          6.7       No
Carol    F    14   62.8    102.5       5%          11.5          9.2       Yes

Copy the example and take it for a test drive to see how easy it is to derive summary statistics with PROC SQL.

Best of luck in all your SAS endeavors!

(aka Michael A. Raithel)

Excerpt from the book:  Did You Know That?  Essential Hacks for Clever SAS Programmers

I plan to post each and every one of the hacks in the book to social media on a weekly basis.  Please pass them along to colleagues who you know would benefit.