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 Data Sets

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

See also: Processing_Variables


Macros

ProcDsn

This macro processes one data set.

 /*    name: ProcDsn.sas
description: process a data set
purpose    : called by SQL list processing
usage:
%ProcDsn(Data=SAShelp.PrdSal2,MemType=data);
******/
%Macro ProcDsn(Data    = SAShelp.Class
              ,MemType = data /* catalog view */
              ,Testing = 0
/ /* ** store source /* */
des = 'project: process one data set');
 
*upcase for logical expression evaluation;
%let MemType= %upcase(&MemType.);
 
%let Testing = %eval(   &Testing
                     or %sysfunc(getoption(mprint)) eq MPRINT);
 
%if &Testing %then %put _local_;
 
%if       &MemType. eq CATALOG %then %do;
      Proc Catalog catalog = &Data.;
                   contents;
                   quit;
      %end;
%else %if &MemType. eq DATA %then %do;
      Proc SQL; describe table &Data.;
                quit;
      %end;
%else %if &MemType. eq VIEW %then %do;
      Proc Contents data = &Data.;
      %end;
%else %Put &Data. type unknown: &type.;
 
title3 "&Data. type: &MemType.";
run; 
%Mend;

ProcDsns

This macro processes all data sets in a libref.

 /*    name: ProcDsns.sas
description: process all data sets in libref
             using named macro
purpose    : list processing
usage:
%ProcDsns(libref=SAShelp,macroname=ProcDsn);
******/
%Macro ProcDsns(LibName   = SAShelp
               ,MemType   = data /* or view */
               ,MacroName = put ProcDsn
               ,SQLprint  = noprint
               ,Testing   = 0
/ /* ** store source /* */
des = 'site: process all data sets in libref');
%local I;
%let   I = 0;
 
%Let Testing = %eval(&Testing
           or  %sysfunc(getoption(mprint)) eq MPRINT);
 
%if &Testing. %then %let SQLprint = print;
 
PROC SQL &SQLprint.;
         select MemName,    MemType
         into  :MemName1 - :MemName&SysMaxLong.
             , :MemType1 - :MemType&SysMaxLong.
         from   Dictionary.Tables
         where  LibName eq "%upcase(&LibName.)"
           and  MemType eq "%upcase(&MemType.)";
         quit;
 
%if &Testing %then %put _local_;
 
%do I = 1 %to &SqlObs.;
    %&MacroName.(data=&Libname..&&MemName&I.,memtype=&&MemType&I.);
    %end;
run;
%Mend;

ProcDsnsDL

This macro processes all data sets in a libref. It differs from above in assigning a delimited list of values to one macro variable.

 /*    name: ProcDsnsDL.sas
description: process all data sets in libref
             using named macro
purpose    : list processing
note       : uses delimited list of values
usage:
%ProcDsns(libref=SAShelp,macroname=ProcDsn);
******/
%Macro ProcDsnsDL(LibName    = SAShelp
                 ,MemType   = data /* or view */
                 ,MacroName = put ProcDsn
                 ,SQLprint  = noprint
                 ,Testing   = 0
/ /* ** store source /* */
des = 'site: process all data sets in libref');
 
%Let Testing = %eval(   &Testing
                     or %sysfunc(getoption(mprint)) eq MPRINT);
 
%if &Testing. %then %let SQLprint = print;
 
%local Dlm I;
%Let   Dlm = *;
%Let   I   = 0;
 
PROC SQL &SQLprint.;
         select MemName, MemType
         into  :List_Name separated by "&Dlm."
             , :List_Type separated by "&Dlm."
         from   Dictionary.Tables
         where  LibName eq "%upcase(&LibName.)"
           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;
    %&MacroName.(libname=&Item_Name.,memtype=&Item_Type.);
    %end;
run;
%Mend;

Testing Programs

ProcDsn-Test

*name: ProcDsn-Test;
options mprint ; *testing: view macro statements;
*either of:
*autoexec:;
*filename Project '.';
*options sasautos = (Project SASautos);
*
*or;
%Include 'ProcDsn.sas';
 
*unit test;
*default=SAShelp.Class;
%ProcDsn();
 
%ProcDsn(Data=SAShelp.PrdSal2,MemType=data);

ProcDsns-Test

*name: ProcDsns-Test;
options mprint ; *testing: view macro statements;
*either of:
*autoexec:;
*filename Project '.';
*options sasautos = (Project SASautos);
*
*or;
%Include 'ProcDsns.sas' 'ProcDsn.sas';
 
*unit test;
*default = SAShelp;
%ProcDsns();
 
%ProcDsns(Libname=SAShelp,MacroName = ProcDsn);

Modules

ProcDsns-caller

*name: ProcDsns-caller;
options mprint ; *testing: view macro statements;
*either of:
*autoexec:;
*filename Project '.';
*options sasautos = (Project SASautos);
 
*or;
%Include 'ProcDsns.sas' 'ProcDsn.sas';
 
*Libname Library '<directory-specification>';
*ProcDsns(Libname=Library);


References

--macro maven == the radical programmer 11:00, 9 June 2010 (UTC)