April 2007 Technical Tip SAS: Aggregating ODS Output from Multiple PROC FREQs

Today I received the following email from a former student:

Sorry to bug you with a SAS question, but I'm hung up something that I need to implement relatively quickly and I thought this might be old hat for you. Do you have a sample code or a suggestion on how to run 36 individual frequency reports and then aggregate the numbers to create a frequency report on the total population? I started coding something to use ODS to capture the frequency output in 36 pieces (in the SAS table the proc creates), but I'm hung up on how to aggregate the numbers at the end for the population totals? It looks like it's going to be a lot of coding to create the final report. Have you ever had this type of request? I appreciate any direction you can provide.

He's on the right track. ODS is needed to capture the output from the individual PROC FREQs. The trick is (1) create a single dataset from the concatenated ODS outputs eliminating unnecessary rows and columns, and (2) run a final PROC FREQ on that dataset using the WEIGHT statement. The following fully functional SAS code demonstrates the techniques used. The internal documentation should provide sufficient explanation.


* ------------------------------------------------------------------------
  Generate some test data.
* ------------------------------------------------------------------------ ;

data alldata;
input ltr $ x y f;
do i = 1 to f;
   output;
end;
drop i;
datalines;
a 1 1 3
a 1 2 4
a 2 1 1
a 2 2 2
b 1 1 0
b 1 2 2
b 2 1 3
b 2 2 4
b 1 3 5
;

* ------------------------------------------------------------------------
  the "ods trace" statements can be dropped. they were used to determine
  the name of the ods output, which happens to be "crosstabfreqs". 
  do NOT remove the "ods output" statements in the proc freq's!
* ------------------------------------------------------------------------ ;

ods trace on;

* ------------------------------------------------------------------------
  run the first proc freq for the "a" records only, and send the output
  to a dataset called "a".
* ------------------------------------------------------------------------ ;

proc freq data=alldata;
where ltr = "a";
tables x*y;
ods output crosstabfreqs = a;
run;

* ------------------------------------------------------------------------
  run the second proc freq for the "b" records only, and send the output
  to a dataset called "b". of course, these datasets would presumably
  be created in separate jobs as permanent sas datasets.
* ------------------------------------------------------------------------ ;

proc freq data=alldata;
where ltr = "b";
tables x*y;
ods output crosstabfreqs = b;
run;

ods trace off;

* ------------------------------------------------------------------------
  these proc contents can be dropped. they were used to determine column 
  names, especially "frequency" and "_type_" used below.
* ------------------------------------------------------------------------ ;

proc contents data=a;
proc contents data=b;

* ------------------------------------------------------------------------
  create a new dataset containing selected columns from the concatenated
  ods output datasets. note _type_ = "11" indicates a cell value as 
  opposed to a row total, column total, or grand total.
* ------------------------------------------------------------------------ ;

data freqs (keep = x y frequency);
set a b;
where _type_ = "11";

* ------------------------------------------------------------------------
  now run a proc freq on that dataset. note the use of the weight 
  statement in proc freq. this statement specifies a numeric variable
  whose value represents the frequency of the observation. The sum of
  the weight variable values represents the total number of observations.
* ------------------------------------------------------------------------ ;

proc freq data=freqs;
tables x*y;
weight frequency;
run;

We hope you will consider Caliber Data Training when deciding upon a SAS training provider.


Go to the articles index. Written by Bill Qualls. Copyright © 2007 by Caliber Data Training 800.938.1222