As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Macro Freq1Var

From sasCommunity
Jump to: navigation, search

%FREQ1VAR: Frequency of One Variable with Format: a Macro to standardize Proc Freq Output Data sets

Author: Ronald_J._Fehd

Proc Format

Create the formats in a separate program and store them in a permanent library.

PROC Format library = Work 
            fmtlib;
     value  $Gender 'F' = 'female'
                    'M' = 'male';

If you do not use libref Library then you need the fmtsearch option to set the search path.

Proc Freq

Here is the basic code that is in the paper.

%Let Libname = sashelp;
%Let Memname = class;
%Let Name    = Sex;
%Let Format  = $Gender.;
%Let XlsPath = C:\temp\xls;
%Let OutData = &Name.;
 
PROC Freq data   = &Libname..&Memname;
          tables   &Name.
                 / list missing noprint
          out    = Work.Freq;
 
*todo: if fileexist then delete;
 
libname XlsFile "&XlsPath.\&OutData..xls";
 
DATA XlsFile.&OutData.;
     attrib Label length = $40;
     drop   &Name.;
 
do until(EndoFile);
   set Work.Freq end = EndoFile;
   Label = put(&Name.,&Format.);
   *RJF2 2008-Oct-24 from SAS-L;
   *Label = vvalue(&Name.);
   output;
   end;
stop;
run;
 
Proc Print data = &SysLast.;
run;
libname XlsFile clear;
run;

Thanks to C Chung for the libname to *.xls.

Note: the libname to *.xls will not overwrite an existing .xls file with the same name. Consider an x command to preemptively delete the file before the libname statement.

  • 2008-Oct-28 SAS-L question about functions:
    • Subject: Call Funtions explanations please
    • call vname
    • call label
    • vvalue

--macro maven == the radical programmer 11:37, 5 August 2008 (EDT)

Or more concisely using ODS OUTPUT OneWayFreqs, as in this example.

PROC Format;
value $sex 'F'='Female' 
           'M'='Male';
value wt   low - 75   = 'Low' 
           75 <- high = 'High';
value ht   low - 60   = 'Low' 
           60 <- high = 'High';
run;
 
ods listing close;
PROC Freq data  = sashelp.class(drop=name);
          format  sex    $sex. 
                  height ht. 
                  weight wt.;
 
ods output OneWayFreqs = OneWayFreqs;
run;
ods listing;
 
DATA Work.OneWayFreqs;
     attrib Label length = $256 
            Name  length = $ 32 
            Type  length = $  1;
     keep   Label Name Type Frequency Percent CumFrequency CumPercent;
 
do until(EndoFile);
   set Work.OneWayFreqs
       end = EndoFile;
   label = coalesceC(of f_:);
   name  = scan(table,2,' ');
   type  = vtypex(name);
   output;
   end;
stop; 
run;

Orginal Macro in paper

Note: ~400 lines with test data.

 /*      name: Freq1Var.sas
-------------: User Requirements:
  description: what to expect
      purpose: why was this written?
-------------: Program Specifications:
program group: import export restructure report 
program group: review data  modify data        
program group: summarization development and testing         
program  type: job routine subroutine
    SAS  type: code generator  project preparation (format autoexec)
    SAS  type: caller include w/parms  include w/o parms 
    SAS  type: macro function  macro procedure
        input: parameter description
      process: how does this work?
       output: parameter description
        notes: uses routines: subroutines:
        usage: batch  submit  %Inc Pgm(filename)  %DoThis(parm = a)
       author: Ronald J. Fehd
 
 /* RJF2 03Apr23
 
RJF2 01Apr14 need to add code to exit on VAR==BY_VAR
 
MACRO: FREQ1VAR returns summary data set        NOTE: uses macro NOBS*
                from data set, variable and format
AUTOEXEC: %LET BLANK  =BLANK;
          %LET INVALID=INVALID;
          %LET DATA_SET=<data-set-name>;
USAGE: 1) %FREQ1VAR(var-name,format);
       2) %FREQ1VAR(var-name,format,OUT=ABC);
       3) %FREQ1VAR(var-name,format,PRINT=1);
       4) %FREQ1VAR(var-name,format,TESTING=1);
       5) %FREQ1VAR(var-name,format,GRFXPTRN=PIE); DISABLED
       6) %FREQ1VAR(var-name,format,ORDER=DATA);
       7) %FREQ1VAR(var-name,format,WHERE=Var1 eq 'A');
          do NOT use comparison operator   symbols: =
                 use comparison operator mnemonics: eq
PROCESS:
1. proc FREQ
 ?order=freq?
 apply format
 where: exclusion of BLANK, INVALID
        other subset if present
 %NOBS: if output data set is empty then exit
2. read data
 make Label using format
 if Colon in Label then front-trim Label
 if GRFXPTRN = PIE then append Count+Percent to Label  DISABLED
 calculate max widths, accumulate Count to N_Resp
 make mac-vars: VarLabel N_Resp
                max-width of: Label, Count, Percent
 calculate %-Resp
3. make output data set
  rename: name(Variable) := in[ValueChr, ValueNum]
  place attributes in variable labels
NOTES:
 * mac-vars BLANK and INVALID usually defined in autoexec
    so format program and macro can access them as global variables
 * variable labels may have colons, if so, front-trim to colon
    e.g.: 'Q06: Supervisor'; change to: 'Supervisor'
KEYWORDS: autoexec formats %NOBS FREQ object standardization
AUTHOR: Ronald J. Fehd
*** .................................... ............................ */
%MACRO FREQ1VAR /* --------------------- ---------------------------- */
(VAR      = /* variable name                                          */
,FORMAT   = /* format of variable with suffix=dot                     */
,DATA     = &DATA_SET./*global macro variable, else hardcode here     */
,fromProg = &ProgName. /*in data set label                            */
,ID       = MpepNum  /* var for N_IDS                                 */
,_BLANK   = &BLANK.  /*common format label indicating blank/missing   */
,_INVALID = &INVALID./*common format label indicating out of range    */
/*********CHOOSE for storage usage                                   ***
,LIBRARY  = WORK   /* library name for read and write ,LIBRARY=LIBRARY*/
/*********CHOOSE for storage usage                                    */
,LIBRARY  = LIBRARY/* library name for read and write ,LIBRARY=LIBRARY*/
,LIB_OUT  = LIBSMRY/* output data set library name                    */
,LBL_VAR  = ./* label of variable, see %local VARLABEL                **
            /* provide to overwrite &DATA_SET label of &VAR           */
,LBL_CNT  = Number of Laboratories Responding/*                       **
            /* label of frequency count                               */
,LBL_PCT  = Percentage of Laboratories Responding/*                   **
            /* label of frequency percent                             */
            /* RJF2 00Jan31 DISABLED ***********************************
,MISSING  = 0/* ?include BLANK/MISSING in FREQ?                       */
,EXCLUDE  = 1/* ?exclude BLANK and INVALID?                           */
,OUT      = ./* output data set name, if not &VAR.                    */
,ORDER    = FREQ/* ?sort descending Count?, else ,ORDER   =DATA       */
,PRINT    = 0/* ?print output data_set? ,PRINT=1                      */
,BY_VAR   = ./* by_var for subsetting                                 */
,BY_VALUE = ' '/* char12 value of by_var(s)                           */
,WHERE    = 1/* where statement for subsetting                        **
            /* where=var1 eq 'A'                                      **
            /* where=var1 eq 'B' and var2 eq 'A'                      **
            /* where=var1 eq 'B' and not var2 eq 'A'                  */
,TITLE    = Freq1Var/* title for graphics                             */
,TESTING  = 0/* ?print intermediate data-sets and messages?           */
)/des = "freq of one var w/format"/**********************/
 /* ** store catalog /* */
 /* ** store /* ** secure /* ** source /* */ 
;/*** ---------------------------------- ---------------------------- **
RJF2 98Sep18 added by_var, conform to CHECKALL SHOWCOMB FREQXTAB
RJF2 98Sep25 polishing changed NMBRRESP: N_RESP, disabled var GRFXPTRN
RJF2 98Dec31 add global FREQ1VAR as return code
RJF2 99Jan05 added WHERE and SUBSET to N_IDS section
RJF2 99Jul26 proposed change N_??? to numeric
RJF2 00Jan31          change N_??? to numeric, polishing
     NOTE: DUPLICATION of WHERE and SUBSET
                       of BY_VAR and SUBVARS
     note: MISSING is unnecessary because missing/blank are excluded
RJF2 00Feb15 dropped SUBSET+SUBVARS replaced w/WHERE
RJF2 00Feb25 polishing
RJF2 00May19 polishing: label of _subset set to constant for long WHERE
RJF2 00Dec22 adding EXCLUDE=1
RJF2 01Feb13 added LIB_OUT parm
RJF2 01Apr14 need to add code to exit on VAR==BY_VAR
RJF2 02Nov20 added ValueChr, conform to CHECKALL SHOWCOMB FREQXTAB
RJF2 03Apr23 polishing, changed WHERE = dot to one
RJF2 04Aug31 removed %global FREQ1VAR;%LET FREQ1VAR = 1;
                     %**LET FREQ1VAR = 0;                         
RJF2 05Mar07 added parm FromProg to data set label
*** .................................... ............................ */
%IF &TESTING %then %DO; 
    %LET PRINT=1;
    options   mprint   notes;                  
    %END;
%ELSE %DO; 
    options nomprint nonotes;                  
    %END;
 /*********************************************************************/
%IF "&VAR." eq "&BY_VAR." %then %DO;
    %PUT FREQ1VAR:VAR==BY_VAR;
    %GOTO EXIT;               
    %END;
 /*********************************************************************/
%*1;
proc FREQ data   = &LIBRARY..&DATA.
          (where = (   put(&VAR.,&FORMAT.) not in("&_BLANK","&_INVALID")
                    and &WHERE.
          )        )%*proc closure; ;
     /******************************************************************
            %IF "&WHERE"  ne "." %then  and &WHERE. ;))%*proc closure; ;
     /*****************************************************************/
          format   &VAR. &FORMAT.;
          tables
                   %IF "&BY_VAR." ne "." %then &BY_VAR. *;
                   &VAR. / noprint
          out    = FREQ1VAR
                   %*IF &MISSING. %then missing;     %*tables closure; ;
 
%IF &TESTING %then %DO;
    proc PRINT data = FREQ1VAR;                 
    %END;
%local N_OBS;  
%NOBS(N_OBS);  
run;
%IF not &N_OBS %then %DO;
    %PUT @@@FREQ1VAR:obs=0 for &VAR. &FORMAT.;
    %GOTO EXIT;                            
    %END;
 
%IF "&ORDER" = "FREQ" %then %DO;
    proc SORT
         data = FREQ1VAR;
         by
            %IF "&BY_VAR." ne "." %then &BY_VAR. ;
            descending Count;                                         
    %END;
 
%NOBS(N_OBS,DATA=&LIBRARY..&DATA.);  
run;
%local LENCNT;  
%LET LENCNT = %length(&N_OBS);
%*usage:2.1 make mac-vars;
 
%*make N_OBS;%*see also in FXT/FREQXTAB SHOWCOMB;
proc MEANS    noprint
     data =   FREQ1VAR;
     var      Count;
     output   sum = N_Obs
     out =    N_OBS(drop  =_Type_ _Freq_);
     %IF      "&BY_VAR." ne "." %then %DO;
     by       &BY_VAR.;                                            
              %END;
 
%*make N_IDS;
proc FREQ data   = &LIBRARY..&DATA.
          (where = (put(&VAR.,&FORMAT.) not in("&_BLANK","&_INVALID")
                    and &WHERE.
          )        ) %*proc closure; ;
          /*************************************************************
      %IF     "&WHERE"  ne "." %then  and &WHERE. ;) ) %*proc closure; ;
          /************************************************************/
          tables
          %IF      "&BY_VAR" ne "." %then  &BY_VAR. *;
                   &ID. / noprint
          out    = FREQ_IDS;%*(drop = Percent);
 
%IF "&BY_VAR." eq "." %then %DO;
    %local N_IDS;    
    %NOBS(N_IDS);  
    run;
    DATA   N_IDS; 
    N_Ids = &N_IDS;   
    output;  
    stop;                    
    %END;
%ELSE %IF "&BY_VAR." ne "." %then %DO;
      proc FREQ
           data =  FREQ_IDS;
           tables  &BY_VAR / list noprint
           out =   N_IDS(drop  = Percent
                         rename=(Count=N_Ids));                         
      %END;
 
%*2.1;
DATA FREQ1VAR;
     length  Label VarLabel $ 40;
     %local  WLABEL WCOUNT WPERCENT N_RESP VARLABEL;
     retain  WLabel WCount WPercent N_Resp 0;
     drop    WLabel WCount WPercent N_Resp VarLabel Colon;
do until(EndoFile);%* - - - - - - - - - - - - - - - - - - - - - - - -*;
  set FREQ1VAR  end = EndoFile;
  Label = left(put(&VAR.,&FORMAT.));
  %*if colon present, front-trim to colon;
  Colon = index(Label,':');
  if Colon then Label = left(substr(Label,Colon+1));
  /*RJF2 00Jan31 *********************************************DISABLED**
  %IF %index(%upcase(&GRFXPTRN.),PIE) %then %DO;
      %*append "<new-line> Count (Percent%)" to Label;
      Label= trim(Label)  !! '^'  !! trim(left(put(Count  ,&LENCNT..0)))
                          !! ' (' !! trim(left(put(Percent,5.1)))
                          !! '%)';                                  
      %END;
  /***********************************************************DISABLED*/
  WLabel  = max(WLabel  ,length(              Label                 ));
  WCount  = max(WCount  ,length(trim(left(put(Count   ,&LENCNT..0)))));
  WPercent= max(WPercent,length(trim(left(put(Percent ,5.1       )))));
  N_Resp + Count;
  output; 
  %*do until(EndoFile); 
  end;
%*2.2 make mac-vars;
 call label(&VAR.,VarLabel);
 %*change squote to explanation mark to avoid mac-error;
 VarLabel = translate(VarLabel,'!',"'");
 %*if colon present, front-trim to colon;
 Colon = index(VarLabel,':');
 if Colon then VarLabel = left(substr(VarLabel,Colon+1));
 call symput('VARLABEL',trim(left(    VarLabel           )));
 call symput('N_RESP'  ,trim(left(put(N_Resp  ,&LENCNT..))));
 call symput('WLABEL'  ,trim(left(put(WLabel  ,2.       ))));
 call symput('WCOUNT'  ,trim(left(put(WCount  ,&LENCNT..))));
 WPercent = max(WPercent,3);%*kludge;
 call symput('WPERCENT',trim(left(put(WPercent,5.       )))); stop; run;
 
%LET PCNTRESP = %eval(100 * &N_RESP / &N_OBS);
%IF &TESTING %then %DO;
    proc PRINT data = FREQ1VAR;
    %put PCNTRESP=<&PCNTRESP.> WLABEL=<&WLABEL.>; 
    %END;
 
%*3;%IF "&OUT."     eq "." %then %LET OUT     = &VAR.;
    %IF "&LBL_VAR"  eq "." %then %LET LBL_VAR = &VARLABEL.;
    %*note if need to substr DS label see FREQXTAB;
DATA &LIB_OUT..&OUT.(
     label = "&FromProg.:Freq1Var: &Var. fmt: &Format."
     rename=(&Var. =
     %IF "%substr(&Format.,1,1)"="$" %then ValueChr;
     %ELSE                                 ValueNum; ))
     %*DATA closure; 
     ;
 attrib    %local         LEN;
           %*ET           LEN  = %eval(2 + %length(&N_OBS.));
           %LET           LEN  = %eval(    %length(&N_OBS.));
  N_Ids    length =     4
           format =&LEN..0         label = "N Ids"
  N_Obs    length =     4
           format =&LEN..0         label =
                      "N=&N_RESP data:&DATA Obs:&N_OBS Resp:&PCNTRESP.%"
  By_Value length = $    12
           format = $char12.       label = "by value"
/***********************************************************************
  ValueChr length = $    8
           format = $char8.        label = "ValueChr"
/**********************************************************************/
  Label    length = $    &WLABEL.
           format = $char&WLABEL.. label = "&LBL_VAR."
  Count    length = 4
           format = &WCOUNT..0     label = "&LBL_CNT."
  Percent  format = &WPERCENT..1   label = "&LBL_PCT."
  &VAR.                            label = "&VAR. value"
 /**********************************************************************
           %LET           LEN =%length(&TITLE.);
  Title    length = $    &LEN.      label  = "Title"
           format = $char&LEN..
DROP   TITLE;
  _Subset  length = $    1
           format = $char1.       label  =
           %IF "&WHERE." eq "." %then      "subset: NONE";
           %IF "&WHERE." ne "." %then      "subset: YES" ; %*closure;  ;
           %*need kludge for long WHERE scan? substr?;
 /*********************************************************************/
   ;
retain By_Value ' ';
do until(EndoFile);
   merge  N_IDS
          N_OBS
          FREQ1VAR
          end = EndoFile;
   %IF  "&BY_VAR." ne "." %then %DO;
       by    &BY_VAR.;
       By_Value = &BY_VALUE.;                                             
       %END;
   output;
   %* do until(EndoFile)*; 
   end;
stop;
%*4;
%IF &PRINT or &TESTING %then %DO;
    proc PRINT data   = &LIB_OUT..&OUT.
               double   label noobs;
               format   _all_;
    %IF &TESTING %then %DO;
        proc CONTENTS data = &LIB_OUT..&OUT.;                              
        %END;
    %* %IF &PRINT or &TESTING;  
    %END;
%*9: copy output to &LIBRARY.;%*and not &TESTING;
%IF "&LIBRARY." ne "LIBRARY" %then %DO;
    proc COPY in      = &LIB_OUT.
              out     = &LIBRARY.
              memtype = data;
              select    &OUT;                                         
    %END;
%EXIT: 
run; 
%MEND Freq1Var;

Test Program

 /*test data***********************************************************/
%*autoexec;
%LET DATA_SET=TESTDATA;
%LET BLANK=BLANK;
%LET INVALID=INVALID;
 
*libname LIBRARY '<lib-ref>';
 
proc FORMAT;
 value $C1_4_ '1'='C1-4:one-North  '
              '2'='C1-4:two-East'
              '3'='C1-4:three-South'
              '4'='C1-4:four-West' ' ','.'="&BLANK." other="&INVALID.";
 value $CA_C_ 'A'='CA-C:apple '
              'B'='CA-C:banana'
              'C'='CA-C:cherry'    ' ','.'="&BLANK." other="&INVALID.";
 value N1_3_  1 = 'N1-3: one  '
              2 = 'N1-3: two'
              3 = 'N1-3: three'        .,0="&BLANK." other="&INVALID.";
 value N1_9_  1 -  4  = 'N1-9: 1..4'
              5 -  9  = 'N1-9: 5..9'
              9<- high= 'N1-9: >9'     .,0="&BLANK." other="&INVALID.";
data TESTDATA;
attrib C1_4 length=$1 format=$C1_4_. label='Chr four directions'
       CA_C length=$1 format=$CA_C_. label='Chr fruit list'
       N1_3 length= 4 format= N1_3_. label='Num three levels'
       N1_9 length= 4 format= N1_9_. label='Num three ranges'
       ;
do N1_9 = 0 to 9;     
   N1_3 = N1_9; C1_4 = put(N1_9,1.);
   MpepNum=put(N1_9,z2.);byVar = (N1_9 le 5);
   if N1_9 then         CA_C = substr('ABCDEFGHI',N1_9,1);
   if mod(N1_9,2) then  output;
   output;                                
   end; 
stop;
proc PRINT
     data =   TESTDATA;
     format   _all_;
proc CONTENTS
     data     = TESTDATA;
 /*********************************************************************/
%FREQ1VAR(N1_3, N1_3_.);
%FREQ1VAR(N1_3, N1_3_.);
%FREQ1VAR(N1_9, N1_9_.,by_var=byVar);
%FREQ1VAR(C1_4,$C1_4_.);
%FREQ1VAR(CA_C,$CA_C_.);
%FREQ1VAR(N1_3,N1_3_.,ORDER=DATA);%*descending Count;
%FREQ1VAR(N1_3,N1_3_.,WHERE=N1_3 gt 3);%*obs=0 msg;
 /* END TEST SECTION  . . . . . . . . . . . . . . . . . . . . . . . ..*
%*FREQ1VAR(LabType,LT_rtv2_.,data = NRP&SHIPYYMM.&PHASE.
,OUT=AAF2I&PHASE.
,WHERE =RptI eq '1'
,TITLE=F2 reporting IIF
,TESTING=1);
 /*********************************************************************/

References

This macro is the routine that eventually became this suite:

--Ronald_J._Fehd macro.maven == the radical programmer 15:10, 12 June 2012 (EDT)

  • Page Freq1Var was renamed Macro Freq1Var on 2012-Jun-30.
    • This page had been accessed 11,458 times.