Macro Smryxtab

From sasCommunity
Jump to: navigation, search

Q: How can I add a summary row to a proc freq output data set?

A: Use proc summary instead.

This macro produces all levels of the classification variables and a summary row.

i.e. for class = a b c it also provides: a, a*b, a*b*c, as well as <null>, the 100% summary row.

Author: Ronald_J._Fehd

Macro SmryXtab

 /*    name: SmryXtab
       is.a: macro subroutine
description: produce an output summary data set
purpose    : provide one row with summary
options    : add percent to conform to proc freq output
author     : Ronald J. Fehd 2009
see also   : proc MDDB
example    :
%SmryXtab(data    = sashelp.Shoes
         ,class   = Region Subsidiary Product
         ,vars    = Sales
         ,stats   = n nmiss min mean max
         ,percent = 1
         ,out     = smry_shoes
         ,outLib  = Work
         );
output:                              _Freq_  Stats
Region  Subsidiary   Product  _Type_  Count  1.. N  Percent
------  -----------  -------  -----   -----  -----  -------
                                000    395          100.000
Africa                            1     56       3  14.1772
Africa  Addis Ababa               2      8       2   2.0253
Africa  Addis Ababa  Boot         3      1       1   0.2532
 
******* */
%MACRO SmryXtab
(data  =
,class =
,vars  =
,out   =
,outLib= Work
,OutOptions= /* drop = rename= ()*/
,percent = 0
,types   = /* a*b a*c a*d ... a*b*c ... a*c*d */
,ways    = /* 2 3 */
,ClassOptions = /***********************
,ClassOptions = ascending
,ClassOptions = descending
,ClassOptions = ascformatted
,ClassOptions = descformatted
,ClassOptions = dsorder /**************/
,stats  = n nmiss min mean median max /*****************************
,stats  = n     nmiss min   p25   mean  median p75   max
css     cv      kurtosis lclm    p1      p5      p10     p25     p50
p75     p90     p95     p99     probt   qrange  range   skewness
stddev  stderr  sum     uclm    uss     var /***********************/
,sort    = 0
,sortby  = /* default= &class or descending count */
,testing = 0
)/des = 'site subroutine: freq xtab of vars and lesser cross-tabs'
 /* ** store source /* */
;%let Class = %cmpres(&Class.);
 
%let Testing = %eval(&Testing.
                      or %sysfunc(getoption(mprint)) eq MPRINT);
 
%local I;%let I = 0;%* loop counter;
%local J;%let J = 0;%* loop counter;
%let       Stats = %cmpres(&Stats.);%* remove double spaces;
%local     Stat;%let Stat = %scan(&Stats.,1);
%local Dim_Stats;
%let   Dim_Stats = %eval(1 + %sysfunc(countc(&Stats.,%str( ))));
%local     Var ;%let Var = %scan(&Vars.,1);
%local Dim_Vars;
%let   Dim_Vars  = %eval(1 + %sysfunc(countc(&Vars.,%str( ))));
 
PROC Summary data   = &Data.  chartype;
             class    &Class. / &ClassOptions.;
             var      &Vars.;
             output
                out = Work.&Out.
            (label  = "&SysMacroName. &Vars."
             where  = (not(index(_Type_,'01'))) )
             %if &Dim_Vars. eq 1 %then
                 %do I  = 1 %to &Dim_Stats.;
                     %let Stat = %scan(&Stats.,&I.);
                     &Stat (&Var.) = &Stat.
                     %end;
             %else %* &Dim_Vars. ge 2;
                 %do I  = 1 %to &Dim_Vars.;
                     %let Var = %scan(&Vars.,&I.);
                     %do J  = 1 %to &Dim_Stats.;
                         %let Stat = %scan(&Stats.,&J.);
                         &Stat. (&Var.) = &Var._&Stat.
                         %end;
                     %end;
                     ;%*end output;
 
%if &Percent. %then %do;
    DATA   Work.&Out.(drop = Denominator &OutOptions.);
    if 0   then set &Out.;
    attrib _Freq_      length = 8 label = '_Freq_ Count'
           Percent     length = 8 label = 'Percent'
           Denominator length = 8 label = 'Denominator';
    retain Denominator 0;
    set    Work.&Out.(obs    = 1
                 keep   =  _Freq_
                 rename = (_Freq_ = Denominator));
    do until(EndoFile);
       set Work.&Out. end = EndoFile;
       Percent = 100*(_Freq_/Denominator);
       output;
       end;
    stop;
    %end;
 
%if &Sort. %then %do;
    %if "&SortBy." eq "" %then %let SortBy = &Class.;
    PROC Sort  data = Work.&Out.
               out  = Work.&Out.;
               by     &SortBy.;
    %end;
 
%if &Testing. %then %do;
    Proc Print data   = &SysLast.;
               title3   &SysMacroName.: &SysLast.;
    PROC SQL;  describe table &SysLast.;
               quit;
    %end;
 
PROC Copy in      = Work
          out     = &OutLib.
          memtype = data;
          select    &Out.;
run;
%Mend SmryXtab;

Usage Example

DATA Work.GAL;
retain One 1;
set Library.GAL
         (keep   =      Agency CoCenter CIO Div MngrId
          where  = (    Agency   ne ' '
                    and CoCenter ne ' '
                    and CIO      ne ' '
                    and Div      ne ' '
         )         );
 
options mprint;
%SmryXtab(data    = Work.GAL
         ,class   = Agency
                   Cocenter
                   Cio
                   Div
                   MngrId
         ,vars    = One
         ,stats   = n
         ,percent = 1
         ,out     = CDC_xtab_Agency_MngrId
         ,outLib  = LibSite
         ,OutOptions = drop = _TYPE_    _FREQ_
                    rename = (N       = CDC_N
                              Percent = CDC_Percent)
         );
 
PROC SQL;  describe table &SysLast.;
           quit;
 
PROC Print data = &SysLast.;
<source lang="sas">

References

--macro maven == the radical programmer 23:07, 2 June 2009 (UTC)