Macro FreqAll

From sasCommunity
Jump to: navigation, search

SGF2007.028:

Journeymen’s Tools: Data Review Macro FreqAll: Using Proc SQL List Processing with Dictionary.Columns to Eliminate Macro Do Loops,

Ronald J. Fehd

Abstract:

The SAS(R) macro language is simple, yet powerful. List Processing (dynamic programming) with Proc SQL is also simple, yet powerful. This paper provides a data review macro FreqAll which illustrates using Proc SQL reading Dictionary.Columns to replace macro %do loops.

http://www2.sas.com/proceedings/forum2007/028-2007.pdf

WARNING: DEPRECATED!*!

This macro was broken within a week of publication by a user with a data set of 1,000 variables. There are limites to what can be done with sql select into!

 /*       Name: FreqAll.sas
 
Requirements  : description  : Proc Freq of all vars in data set
                purpose      : provide shorter listing than tables _all_
 
------------------------------------------------------------------------
Contexts      : program group: data review
                program  type: routine
                SAS      type: program with parameters
                uses routines: in-program macro %FreqOf
 
------------------------------------------------------------------------
Specifications: input  : libref
                         data
                         max number of high and low obs to view
                process: SQL  writes macro calls
                         Proc Freq of each var
                              save to data
                         append to report
                              subset if Nobs greater than Max-N-to-view
                output : print report
 
------------------------------------------------------------------------
Information   :  author: Ronald J. Fehd
 
------------------------------------------------------------------------
Usage Examples:
%Let In_Lib      = Library;
%Let In_Data     = MyData ;
%Let HiLowToView = 5      ;
 
%Inc 'FreqAll.sas';
 
Usage Examples:/* ---------------------- */
options mprint linesize = 72;
%Let In_Lib      = SAShelp;
%Let In_Data     = Class  ;
%Let In_Data     = PrdSal2;
%Let HiLowToView = 5      ;
 
/*** ................................... */
 
%Macro Freqof
(Name      = /* var                                 */
,Type      = /* in (char,num)                       */
,Length    = /* integer                             */
,Format    = /* $char                               */
,Label     = /* $char40                             */
             /* for hi and low, use either:  ****** */
,Order     = internal /* default: hi and low values */
             /* for mode    use:  ** *** *** ****** **
,Order     = freq    /* hi and low descending count */
,InLib     = &In_Lib.     /* FreqAll scope: global  */
,InData    = &In_Data.    /* FreqAll scope: global  */
,Nobs2View = &HiLowToView /* FreqAll scope: global  */
,Testing   = 0            /* show stuff? ,Testing=1 */
)/des      = 'site: FreqOf: subroutine of prog FreqAll'
;/*change notes
RJF2 04Nov04 rewrite of v6.06 FreqOSSD from 1990-March
RJF2 05Sep23 downloaded from mainframe, polishing
RJF2 05Dec19 posted to SAS-L Subject: tip: macro FreqAllVars
http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0512C&L=sas-l&P=R22891
RJF2 06Apr06 polishing for fall SUG papers todo: use cats function
*** .................................... */
%local Nobs; %Let Nobs = 0;%*initialize for symput;
 
PROC Freq data   = &InLib..&InData.
          order  = &Order.;
          tables   &Name
                 / list missing noprint
             out = Freq(rename = (&Name. =
                   %If &Type = char %then  ValuC;
                   %else                   ValuN; ));
 
DATA    Freq;               %* name32 type4  $200 label40;
attrib  Attributes length = $%eval(32 +1 +4 +1 +4 +1 + 40)
        ValuC      length = $20
        ValuN      length =   8
        Count      length =   4  format = comma.
        Percent    length =   8  format = 6.2
        Level      length =   4;
 
retain  Attributes "&Name. &Type.:&Length. &Label"
        ValuC      "."     ValuN   Level .;
 
do      until(EndoFile);
set     Freq end  = EndoFile
             nobs = Nobs;
Level   ++1;               %*increment retained counter;
output; end;               %*do until EndoFile;
call    symput(    'Nobs', compress(put(Nobs,32.)));
stop;   run;
%If &Testing. %then %Put Note:&SysMacroName.: nobs<&Nobs.>;
 
%If &Nobs. le %eval(2 * &Nobs2View. + 2) %then %do;
PROC Append base = ListFreq
            data = Freq;                       %end;
 
%Else %do;%* --------------------------- *;
DATA Snipped;
set  Freq(obs = 1);ValuC = '<snipped>';ValuN   = .;
                   Count = .;          Percent = .;
                   Level = .;          output;      stop;
 
PROC Append base = ListFreq   %*high values;
            data = Freq(obs = &Nobs2View.);
 
PROC Append base = ListFreq
            data = Snipped;
 
PROC Append base = ListFreq   %*low values;
            data = Freq(firstobs = %eval( &Nobs.
                                        - &Nobs2View. +1));
%end;%*Else do ......................... *;
run;%* ................................. *; %Mend Freqof;
 
%Let In_Lib  = %upcase(&In_Lib.);
%Let In_Data = %upcase(&In_Data.);
 
%Let SQLprint =   print;%*testing: view FreqOf statements;
%Let SQLprint = noprint;
 
PROC SQL &SQLprint.;
         create table  ListAttributes as
                select Varnum, Name, Type, Length
                     , Format, Informat, Label, Npos
                       from    Dictionary.Columns
                       where     Libname eq "&In_Lib."
                             and MemName eq "&In_Data."
                             and MemType eq 'DATA'
       ; select '%FreqOf(name = ' !! trim(Name)
                !!     ',type = ' !! trim(Type)
                !!   ',length = '
                !!           compress(put(Length,32.))
                !!    ',label = ' !! trim(Label)
                !!    ')'
                into  :List  separated by ' '
                from   ListAttributes
       ; select Nobs   into :NobsData
                from   Dictionary.Tables
                where      Libname eq "&In_Lib."
                       and MemName eq "&In_Data."
                       and MemType eq 'DATA'
       ; quit;
&List. ; %*execute macro calls;
 
%Let NobsData = &NobsData;%* remove leading blanks;
Title2
   "&SysProcessName.: &In_Lib..&In_Data. nobs:&NobsData.";
 
PROC Print data = ListAttributes noobs;
           Title3 "variable attributes";
 
PROC Print data = ListFreq;
           var    Valu: Count Percent Level;
           by     Attributes notsorted;
           id     Attributes          ;
           Title3 "list of variable frequencies";
run;
%symdel In_Data In_Lib HiLowToView List NobsData SQLprint;
%Put _user_;

Listing:

;/*\begin{TestSuite.program.listing} ***
Program FreqAll: SASHELP.CLASS nobs:19

varnum   name     type   length   format   informat   label   npos

   1     Name     char      8                                  24
   2     Sex      char      1                                  32
   3     Age      num       8                                   0
   4     Height   num       8                                   8
   5     Weight   num       8                                  16

Program FreqAll: SASHELP.CLASS nobs:19

list of variable frequencies

 Attributes     ValuC        ValuN    Count    Percent    Level

Name char:8     Alfred          .       1        5.26        1
                Alice           .       1        5.26        2
                Barbara         .       1        5.26        3
                Carol           .       1        5.26        4
                Henry           .       1        5.26        5
                <snipped>       .       .         .          .
                Philip          .       1        5.26       15
                Robert          .       1        5.26       16
                Ronald          .       1        5.26       17
                Thomas          .       1        5.26       18
                William         .       1        5.26       19

Sex char:1      F               .       9       47.37        1
                M               .      10       52.63        2
;/*\end{TestSuite.program.listing} ***** */

References

-- created by User:Rjf2 11:19, 14 May 2008 (EDT)

--Ronald_J._Fehd macro.maven == the radical programmer