Macro Freqxtab

From sasCommunity
Jump to: navigation, search

Q: How can I get a summary row for each higher level of a frequency cross-tabulation?

A: save the output data set from the lowest level x-tab and use that with weight count to calculate the summary for each less x-tab.

Author: Ronald_J._Fehd

Macro FreqXtab

 /*    name: freqxtab
       is.a: macro subroutine
description: produce a data set
             with freq cross-tabulation
             and each lesser x-tab
purpose    : provide row with sum of each larger x-tab
example    :
%FreqXtab(data = sashelp.Shoes
         ,vars = Region Subsidiary Product
         ,out  = Work.xtab_shoes
         );
output:
Region  Subsidiary   Product         COUNT  PERCENT
------  -----------  --------------  -----  -------
Africa                                 56   14.1772
Africa  Addis Ababa                     8    2.0253
Africa  Addis Ababa  Boot               1    0.2532
******* */
%MACRO FreqXtab
(data =
,vars =
,sort         = 0
,singles      = 0
,combinations = 0
,out     =
,testing = 0
)/des = 'site subroutine: freqxtab of all vars and combinations'
 /* ** store source /* */
;%let Vars = %cmpres(&Vars.);
 
%let Testing = %eval(&Testing.
                      or %sysfunc(getoption(mprint)) eq MPRINT);
%local I;%let I = 0;%*loop counter;
%local Xtab;
%let   Xtab = %sysfunc(translate(&Vars.,*,%str( )));%* tables &Xtab.;
%local Dim_Vars;
%let   Dim_Vars = %eval(%sysfunc(countc(&Vars.,%str( ))));
 
%if &Testing. %then %Put _local_;
 
PROC Freq data   = &Data;
          tables   &Xtab.
                 / list missing noprint
             out = Work._Freq_Xtab_large;
 
DATA &Out.;
if 0 then set Work._Freq_Xtab_large;
stop;
 
%let Xtab = %scan(&Vars,1);%* tables &Xtab.;
 
%do I = 1 %to &Dim_Vars.;
    %if &I. gt 1 %then %let Xtab = &Xtab. * %scan(&Vars,&I.);
    %if &Testing. %then %Put note: &SysMacroName..Xtab=&Xtab.;
    PROC Freq data   = Work._Freq_Xtab_large;
              tables   &Xtab.
                     / list missing noprint
                 out = Work._Freq_Xtab_small;
              weight   Count;
 
    PROC Append base = &Out.
                data = &SysLast.;
    %end;
 
PROC Append base = &Out.
            data = Work._Freq_Xtab_large;
 
%if &Sort. %then %do;
    Proc Sort  data   = &Out. ;
               by       &Vars.;
    %end;
 
%if &Testing. %then %do;
    Proc Print data   = &SysLast.;
               title3   &SysLast.;
    %end;
 
PROC Delete data = Work._Freq_Xtab_large;
PROC Delete data = Work._Freq_Xtab_small;
run;
%Mend;

Test Program

options mprint;
%FreqXtab(data = sashelp.Shoes
         ,vars = Region Subsidiary Product
         ,out  = Work.xtab_shoes
         )

References

--macro maven == the radical programmer 17:15, 6 February 2009 (EST)