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.


Processing Variables

From sasCommunity
Jump to: navigation, search

These programs are from SESUG 2010 Hands-On Workshop which are an extrapolation from the SESUG 2007 paper.

How_To_Use_Proc_SQL_select_into_for_List_Processing

Macros

Two macros are shown here:

  • ProcVar is.a subroutine
  • ProcVars using arrays of macro variables
  • ProcVarsDL using delimited list

ProcVar is a subroutine. ProcVars and ProcVarsDL are list-processing routines used to generate calls to the macro subroutine ProcVar for each variable in a data set.

Macro ProcVar

This is the subroutine which processes one variable. Predecessor is Freq1Var.

 /*    name: ProcVar.sas
description: process a variable
purpose    : called by SQL list processing
usage:
%ProcVar(Data=SAShelp.Class,Name=Sex,Type=char);
NOTE SQL dictionary.tables.type in (char,num);
     compare Contents.type in (1==num,2==char);
******/
%Macro ProcVar(Data    = SAShelp.class
              ,Name    = Sex
              ,Type    = char
              ,Testing = 0
)/ /* ** store source /* */
des = 'project: process one variable');
 
%Let Testing = %eval(&Testing
           or  %sysfunc(getoption(mprint)) eq MPRINT);
 
%If &Testing. %then %put _local_;
 
%if       &Type. eq char %then %do;
      Proc Freq       data   = &Data.;
                      tables   &Name.;
      %end;
%else %if &Type. eq num  %then %do;
      Proc Summary    data = &Data. print;
     *Proc Univariate data = &Data.;
                      var    &Name.;
      %end;
 
title2 "&Data..&Name. type: &Type.";
run;%Mend;

Macro ProcVars

This is the list-processing caller of macro ProcVar.

 /*    name: ProcVars.sas
description: process all variables in data set
             using named macro
purpose    : list processing
usage:
%ProcVars(Data=SAShelp.Class,macroname=ProcVar);
******/
%Macro ProcVars(Data      = SAShelp.Class
               ,MemType   = data /* or view */
               ,MacroName = put ProcVar
               ,SQLprint  = noprint
               ,Testing   = 0
)/ /* ** store source /* */
des = 'site: process all vars in data set');
 
%let Testing = %eval(   &Testing
                     or %sysfunc(getoption(mprint)) eq MPRINT);
%if &Testing %then %let SQLprint = print;
 
%local LibName MemName I;
 
%if   %index(&Data,.) %then %do;
      %* is.a two-level name: Libref.data;
      %Let Libname = %scan(&Data.,1,.);
      %Let MemName = %scan(&Data.,2,.);
      %end;
%else %do; %*is.a one-level name: Data==Work.Data;
      %Let Libname = Work;
      %Let MemName = &Data.;
      %end;
 
PROC SQL &SQLprint.;
         select Name, Type
         into  :Name1 - :Name&SysMaxLong.
             , :Type1 - :Type&SysMaxLong.
         from   Dictionary.Columns
         where  LibName eq "%upcase(&LibName.)"
           and  MemName eq "%upcase(&MemName.)"
           and  MemType eq "%upcase(&MemType.)";
         quit;
 
%if &Testing %then %put _local_;
 
%do I = 1 %to &SqlObs.;
    %if &Testing %then %do;
        %put note2:Name&I: &&Name&I.;
        %put note2:Type&I: &&Type&I.;
        %end;
    %*note: semicolon required to close testing: put MacroName;
    %&MacroName.(data=&Data.,name=&&Name&I.,type=&&Type&I.);
    %end;
run;
%Mend;

Macro ProcVarsDL

This routine does the same processing as ProcVars, using a delimited list.

 /*    name: ProcVarsDL.sas
description: process all variables in data set
             using named macro
purpose    : list processing
usage:
%ProcVarsDL(Data=SAShelp.Class,macroname=ProcVar);
notes:
  this method uses a delimited list
  compare to ProcVar which uses macro array
******/
%Macro ProcVarsDL(Data      = SAShelp.Class
                 ,MemType   = data /* or view */
                 ,MacroName = put ProcVar
                 ,SQLprint  = noprint
                 ,Testing   = 0
)/ /* ** store source /* */
des = 'site: process all variables in data set');
 
%let Testing = %eval(   &Testing
                     or %sysfunc(getoption(mprint)) eq MPRINT);
%if &Testing %then %let SQLprint = print;
 
%local Dlm I LibName MemName;
%Let Dlm = *;
 
%if   %index(&Data,.) %then %do;
      %* is.a two-level name: Libref.data;
      %Let Libname = %scan(&Data.,1,.);
      %Let MemName = %scan(&Data.,2,.);
      %end;
%else %do; %*is.a one-level name: Data==Work.Data;
      %Let Libname = Work;
      %Let MemName = &Data.;
      %end;
 
PROC SQL &SQLprint.;
         select Name, Type
         into  :List_Name separated by "&Dlm."
             , :List_Type separated by "&Dlm."
         from   Dictionary.Columns
         where  LibName eq "%upcase(&LibName.)"
           and  MemName eq "%upcase(&MemName.)"
           and  MemType eq "%upcase(&MemType.)";
         quit;
 
%if &Testing %then %put _local_;
 
%do I = 1 %to &SqlObs.;
    %Let Item_Name = %scan(&List_Name,&I.,&Dlm.);
    %Let Item_Type = %scan(&List_Type,&I.,&Dlm.);
    %if &Testing %then %do;
        %put note2:Item_Name&I: &Item_Name.;
        %put note2:Item_Type&I: &Item_Type.;
        %end;
    %*note: semicolon required to close testing: put MacroName;
    %&MacroName.(data=&Data.,name=&Item_Name.,type=&Item_Type.);
    %end;
run;
%Mend;

Testing Programs

ProcVar-Test

This program is the unit test of the subroutine ProcVar.

*name: ProcVar-Test;
options mprint ; *testing: view macro statements;
*either of:
*autoexec:;
*filename Project '.';
*options sasautos = (Project SASautos);
*
*or;
%Include  'ProcVar.sas';
*default=SAShelp.Class;
%ProcVar();
%ProcVar(Data=SAShelp.Class,name=Age,type=num);

ProcVars-Test

This program is the unit test of the calling routine ProcVars.

*name: ProcVars-Test;
options mprint ; *testing: view macro statements;
*either of:
*autoexec:;
*filename Project '.';
*options sasautos = (Project SASautos);
*
*or;
%Include 'ProcVars.sas' 'ProcVar.sas';
*Unit test;
*default=SAShelp.Class;
%ProcVars();
*Integration Test;
%ProcVars(Data=SAShelp.PrdSal2,macroname=procvar);
*ProcVars(Data=SAShelp.PrdSal3);
*ProcVars(Data=SAShelp.PrdSale);

ProcVarsDL-Test

This program is the unit test of the calling routine ProcVarsDL.

*name: ProcVarsDL-Test;
options mprint ; *testing: view macro statements;
*either of:
*autoexec:;
*filename Project '.';
*options sasautos = (Project SASautos);
*
*or;
%Include 'ProcVarsDL.sas' 'ProcVar.sas';
*Unit test;
*default=SAShelp.Class;
%ProcVarsDL();
*Integration Test;
%ProcVarsDL(Data=SAShelp.PrdSal2,macroname=procvar);
*ProcVarsDL(Data=SAShelp.PrdSal3);
*ProcVarsDL(Data=SAShelp.PrdSale);

Modules

ProcVars-caller

This program is the template for a production program.

*name: ProcVars-caller;
options mprint ; *testing: view macro statements;
*either of:
*autoexec:;
*filename Project '.';
*options sasautos = (Project SASautos);
*or;
%Include 'ProcVars.sas' 'ProcVar.sas';
 
*required:;
*libname Library '<directory-specification>';
 
%ProcVars(Data=Library.MyData,macroname=procvar);


References

--macro maven == the radical programmer 23:48, 6 June 2010 (UTC)