/*-------------------------------------------------------------------* * TABLE-DRIVEN STRATEGIES FOR RAPID SAS(r) APPLICATIONS DEVELOPMENT * * by Tanya Kolosova & Samuel Berestizhevsky * * Copyright(c) 1995 by SAS Institute Inc., Cary, NC, USA * *-------------------------------------------------------------------* * * * This material is provided "as is" by SAS Institute Inc. There * * are no warranties, express or implied, as to merchantability or * * fitness for a particular purpose regarding the materials or code * * contained herein. The Institute is not responsible for errors * * in this material as it now exists or will exist, nor does the * * Institute provide technical support for it. * * * *-------------------------------------------------------------------* * * * Questions or problem reports concerning this material may be * * addressed to the authors, Tanya Kolosova & Samuel Berestizhevsky * * * * by electronic mail: * * * * sasconsl@actcom.co.il * * * * by ordinary mail: * * * * P.O. Box 1169, Nazerath-Ellit 17100, Israel * * * *-------------------------------------------------------------------*/ /* This document describes the SAS macro and SCL programs included in the book "Table-Driven Strategies for Rapid SAS Applications Development." Section references contained here indicate the sections in the book where the programs are illustrated. **** In some cases there are minor differences between the programs on this document and those in the book. These changes were made to correct errors in original. The changes should not affect the results produced by the programs.**** Program Requirements -------------------- The programs were developed under the SAS System, Version 6.10 (operating systems OS/2 and Windows), and tested under the SAS System, Versions 6.07, 6.08 and 6.09 (operating systems MVS, VM/CMS, Unix, Sun/OS, AIX). The programs should run under all releases (6.07 and later) of the SAS System on all operating systems. The programs all require the base SAS product. Some programs require SAS/CONNECT, SAS/AF, and SAS/FSP. General Usage Notes ------------------- 1. You may receive unanticipated results of the programs if you use SAS data sets with data that do not conform to the relational data model (e.g. duplicate rows in the primary key, the missing value for character values differs from the missing value for numeric values). To prevent such results we recommend that you apply the %DATMODEL macro to your SAS data sets (see the %DATMODEL macro in the extra programs section). 2. If you want to create the same data environment as in the book, you can use the DATABASE program from the extra program section. */ ***************************************************************************************** /*The following example code appears on page 13.*/ ************************************************** /* PROGRAM LIBREF. DESCRIPTION Assigns SAS library references according to the Library table meta data. USAGE %libref(libname); PARAMETERS libname - is the name of the library storing the library data set. REQUIRES The Library data set corresponding to the Library table. AUTHORS T.Kolosova and S.Berestizhevsky. */ %macro libref (libname) ; /* The following DATA step creates macro variables and fills them with data from the library data set: libs - contains the number of libraries, defined in the library data set lib - is a series of macro variables containing names of the libraries loc - is a series of macro variables containing physical locations of these libraries. */ %let libs = 0 ; data _null_ ; set &libname..Library ; call symput("libs", _n_) ; call symput("lib" || left(_n_), trim(library)) ; call symput("loc" || left(_n_),trim(location)) ; run ; /* The following loop generates required LIBNAME statements. */ %if &libs > 0 %then %do i = 1 %to &libs ; libname &&lib&i "&&loc&i" ; %end ;; %mend libref ; ***************************************************************************************** /*The following example code appears on pages 40-43.*/ ****************************************************** /* PROGRAM KERNEL. DESCRIPTION Creates four SAS data sets: library, object, location, and property, and fills in these data sets meta data. USAGE %kernel (libref, mis) ; PARAMETERS libref - is the name of the library that stores created data sets. mis - is the code for the missing value. REQUIRES Prerequisites are not required. AUTHORS T.Kolosova and S.Berestizhevsky. */ %macro kernel (libref, mis) ; /* The following four PROC's SQL create library, object, location, and property data sets and fills in meta data. */ proc sql ; create table &libref..Library (library char(8), location char(80)) ; insert into &libref..Library values ("kernel", "d:\kernel") ; proc sql ; create table &libref..Object (table char(8), title char(80), dataset char(80), screen char (2)) ; insert into &libref..Object values ("Library", "List of SAS libraries", "library", "L") values ("Object", "List of tables", "object", "L") values ("Location", "List of tables and correspondent libraries", "location", "L") values ("Property", "List of tables properties", "property", "L") ; proc sql ; create table &libref..Location (table char(8), library char(8)) ; insert into &libref..Location values ("Library", "kernel") values ("Object", "kernel") values ("Location", "kernel") values ("Property", "kernel") ; proc sql ; create table &libref..Property (table char(8), column char(8), title char(80), type char(1), length num, format char(20), attribut char(2), domtab char(8), domcol char(8), meantab char(8), meancol char(8), place num, initval char(80), formula char(80), updtype char(1), missing char(1), message num) ; insert into &libref..Property values ("Library", "LIBRARY", "SAS library name", "C", 8, "&mis", "P", "&mis", "&mis", "&mis", "&mis", 1, "&mis", "&mis", "R", ".", &mis) values ("Library", "LOCATION", "File name for SAS library", "C", 80, "&mis", "&mis", "&mis", "&mis", "&mis", "&mis", 2, "&mis", "&mis", "R", ".", &mis) values ("Object", "TABLE", "Table name", "C", 8, "&mis", "P", "&mis", "&mis", "&mis", "&mis", 1, "&mis", "&mis", "R", ".", &mis) values ("Object", "TITLE", "Title", "C", 80, "&mis", "&mis", "&mis", "&mis", "&mis", "&mis", 2, "&mis", "&mis", "R", ".", &mis) values ("Object", "DATASET", "SAS data set name", "C", 8, "&mis", "&mis", "&mis", "&mis", "&mis", "&mis", 3, "&mis", "&mis", "R", ".", &mis) values ("Object", "SCREEN", "Screen type", "C", 2, "&mis", "&mis", "&mis", "&mis", "&mis", "&mis", 4, "&mis", "&mis", "R", ".", &mis) values ("Location", "TABLE", "Table name", "C", 8, "&mis", "P", "Object", "TABLE", "Object", "TITLE", 1, "&mis", "&mis", "R", ".", &mis) values ("Location", "LIBRARY", "SAS library name", "C", 8, "&mis", "&mis", "Library", "LIBRARY", "Library", "LOCATION", 2, "&mis", "&mis", "R", ".", &mis) values ("Property", "TABLE", "Table name", "C", 8, "&mis", "PI", "Object", "TABLE", "Object", "TITLE", 1, "&mis", "&mis", "R", ".", &mis) values ("Property", "COLUMN", "Column name", "C", 8, "&mis", "P", "&mis", "&mis", "&mis", "&mis", 2, "&mis", "&mis", "R", ".", &mis) values ("Property", "TITLE", "Column title", "C", 80, "&mis", "&mis", "&mis", "&mis", "&mis", "&mis", 3, "&mis", "&mis", "R", ".", &mis) values ("Property", "TYPE", "Column type", "C", 1, "&mis", "&mis", "&mis", "&mis", "&mis", "&mis", 4, "&mis", "&mis", "R", ".", &mis) values ("Property", "LENGTH", "Column length", "N", 8, "&mis", "&mis", "&mis", "&mis", "&mis", "&mis", 5, "&mis", "&mis", "R", ".", &mis) values ("Property", "FORMAT", "Format", "C", 20, "&mis", "&mis", "&mis", "&mis", "&mis", "&mis", 6, "&mis", "&mis", "R", ".", &mis) values ("Property", "ATTRIBUT", "Column property", "C", 2, "&mis", "&mis", "&mis", "&mis", "&mis", "&mis", 7, "&mis", "&mis", "R", ".", &mis) values ("Property", "DOMTAB", "Domain table name", "C", 8, "&mis", "&mis", "Object", "TABLE", "&mis", "&mis", 8, "&mis", "&mis", "R", ".", &mis) values ("Property", "DOMCOL", "Domain column name", "C", 8, "&mis", "&mis", "&mis", "&mis", "&mis", "&mis", 9, "&mis", "&mis", "R", ".", &mis) values ("Property", "MEANTAB", "Meaning table name", "C", 8, "&mis", "&mis", "Object", "TABLE" "&mis", "&mis", 10, "&mis", "&mis", "R", ".", &mis) values ("Property", "MEANCOL", "Meaning table name", "C", 8, "&mis", "&mis", "&mis", "&mis", "&mis", "&mis", 11, "&mis", "&mis", "R", ".", &mis) values ("Property", "PLACE", "Place on the screen form", "N", 8, "&mis", "&mis", "&mis", "&mis", "&mis", "&mis", 12, "&mis", "&mis", "R", ".", &mis) values ("Property", "INITVAL", "Initial column value", "C", 80, "&mis", "&mis", "&mis", "&mis", "&mis", "&mis", 13, "&mis", "&mis", "R", ".", &mis) values ("Property", "FORMULA", "Formula for computed column", "C", 80, "&mis", "&mis", "&mis", "&mis", "&mis", "&mis", 14, "&mis", "&mis", "R", ".", &mis) values ("Property", "UPDTYPE", "Type of column update", "C", 1, "&mis", "&mis", "&mis", "&mis", "&mis", "&mis", 15, "&mis", "&mis", "R", ".", &mis) values ("Property", "MISSING", "Code of missing value", "C", 1, "&mis", "&mis", "&mis", "&mis", "&mis", "&mis", 16, "&mis", "&mis", "R", ".", &mis) values ("Property", "MESSAGE", "Message id", "N", 8, "&mis", "&mis", "&mis", "&mis", "&mis", "&mis", 17, "&mis", "&mis", "R", ".", &mis) ; /* The following PROC SQL creates a simple index on the TABLE column of the Property table. */ proc sql ; create index table on &libref..Property (table) ; quit ; %mend kernel ; ****************************************************************************************** /*The following example code appears on pages 44-47.*/ ****************************************************** /* PROGRAM LSCREEN. DESCRIPTION Creates a data entry screen form for specified table and stores this form in the external file. USAGE %lscreen (libref, table, file, mis) ; PARAMETERS libref - is the name of the library storing the data dictionary data sets, such as library, object, property, location, etc. table - is the name of the table that is required to create the data entry screen form. file - is the name of the external file storing the generated screen form. mis - is the code identifying the missing value. REQUIRES The object, property, and location data sets must exist. AUTHORS T.Kolosova and S.Berestizhevsky. */ %macro lscreen (libref, table, file, mis) ; /* The following DATA step selects from the property data set observations relevant to the properties of the specified table. */ data _proper_ ; set &libref..Property ; where upcase(left(table)) = upcase("&table") and place ^= &mis ; run ; proc sort data = _proper_ ; by place ; run ; /* The following DATA step creates macro variables and fill them with data from _proper_ data set: count - contains the number of columns that must be placed on the screen form _c - is a series of macro variables containing titles of the columns. _t - is a series of macro variables containing tables that contain columns with meaningful information for each column of the specified table. _m - is a series of macro variables containing columns that contain meaningful information for each column of the specified table. _s - is a series of macro variables containing the service words, such as: userformat, noformat, SASformat, for each column of the specified table. _f - is a series of macro variables containing formats defined for each column of the specified table. _fl - is a series of macro variables containing length of each column of the specified table. */ data _null_ ; retain _count 1 ; set _proper_ ; call symput("count", _count) ; call symput("_c" || left(_count), left(title)) ; call symput("_t" || left(_count), "&mis") ; call symput("_m" || left(_count), "&mis") ; call symput("_s" || left(_count), "&mis") ; call symput("_f" || left(_count), "&mis") ; call symput("_fl" || left(_count), "&mis") ; if left(meantab) ^= "&mis" then do ; call symput("_t" || left(_count), left(meantab)) ; call symput("_m" || left(_count), left(meancol)) ; call symput("_s" || left(_count), "userformat") ; _count + 1 ; return; end ; if left(format) = "&mis" then do ; call symput("_s" || left(_count), "noformat") ; call symput("_fl" || left(_count), left(length)) ; _count + 1 ; return; end ; call symput("_s" || left(_count), "SASformat") ; ind = indexc(format, "123456789") ; if ind >= 1 then call symput("_fl" || left(_count), substr(format, ind, index(format, ".") - ind)) ; else call symput("_f" || left(_count), left(format)) ; _count + 1 ; run ; %let defined = 0 ; %let exist = 0 ; /* In the following loop the DATA steps create, according to the values stored in the _s macro variable, the _b macro variable, that is, a series of macro variables containing for each column of the specified table an associated length of field where the column's value will be displayed or entered. This field is marked by a line of underscore symbols. */ %do i = 1 %to &count ; %if &&_s&i = SASformat and &&_fl&i = &mis %then %do ; /* The following DATA steps and PROC DATASETS check existence of the data set corresponding to the FORMAT table and it contents. */ data _null_ ; call symput("_b" || left(&i), "***Format not defined***") ; run ; %if &defined = 0 %then %do ; data _null_ ; set &libref..Object ; where upcase(left(table)) = "FORMAT" ; call symput("dataset", left(dataset)) ; call symput("defined", 1) ; run ; %end ; %if &defined = 1 and &exist = 0 %then %do ; data _null_ ; set &libref..Location ; where upcase(left(table)) = "FORMAT" ; call symput("libname", left(library)) ; run ; proc datasets library = &libname memtype = data nolist ; contents data = _all_ memtype = data out = work._out_ (keep = memname nobs) noprint ; run ; quit ; data _null_ ; set _out_ ; if upcase(left(memname)) = upcase("&dataset") then do ; if nobs > 0 then call symput("exist",1) ; stop; end ; run ; %end ; %if &exist = 1 %then %do ; data _null_ ; set &libname..&dataset ; if upcase(trim("&&_f&i")) = upcase(left(format)) then do ; if indexc(detail, "123456789") > 0 then do ; ind = index(detail, ".") ; if ind > 0 then call symput("_fl" || left(&i), substr(detail, 1, ind - 1)) ; else call symput("_fl" || left(&i), left(detail)) ; end ; else call symput("_fl" || left(&i), length(detail)) ; call symput("_b" || left(&i), repeat("_", symget("_fl" || left(&i)) - 1)) ; stop ; end; run ; %end ; %end ; %if &&_s&i = noformat or (&&_s&i = SASformat and &&_fl&i ^= &mis) %then %do ; data _null_ ; call symput("_b" || left(&i), repeat("_", &&_fl&i - 1)) ; run ; %end ; %if &&_s&i = userformat %then %do ; data _null_ ; set &libref..Property ; where upcase(left(table)) = upcase("&&_t&i") and upcase(left(column)) = upcase("&&_m&i") ; call symput("_b" || left(&i), repeat("_", length - 1)) ; stop ; run ; %end ; %end ; /* The following DATA step creates the screen form for the specified table and stores it in the external file. */ data _null_ ; set &libref..Object ; length _line $ 200 ; file "&file" ; where upcase(left(table)) = upcase("&table") ; put title /; %do i = 1 %to &count ; _line = trim("%nrbquote(&&_c&i)") || " :" ; put _line ; put "&&_b&i" ; %end ; run ; proc datasets library = work memtype = data ; delete _out_ _proper_ ; run ; quit ; %mend lscreen ; ***************************************************************************************** /*The following example code appears on pages 53-57.*/ ****************************************************** /* PROGRAM FORMATS. DESCRIPTION Generates SAS catalog containing user-defined formats specifying in the data dictionary tables. USAGE %formats(libref, mis) ; PARAMETERS libref - is the name of the library storing the data dictionary data sets, such as object, property, location, etc. mis - is the code identifying the missing value. REQUIRES The object, property, and location data sets must exist. AUTHORS T.Kolosova and S.Berestizhevsky. */ %macro formats (libref, mis) ; /* The following DATA step selects from the property data set the observations in which in the FORMAT column exist format specifications. */ %let chekfm = 0 ; data _forms_ (keep = format domtab domcol meantab meancol) ; set &libref..property ; if left(format) = "&mis" then delete ; call symput("chekfm", 1) ; run ; %if &chekfm = 1 %then %do ; /* The following DATA steps and PROC DATASETS check existence of the data set corresponding to the FORMAT table and it contents. */ %let chekfmt = 0 ; %let defined = 0 ; proc sort data = _forms_ nodupkey ; by format ; run ; data _null_ ; set &libref..object ; where upcase(left(table)) = "FORMAT" ; call symput("dataset", left(dataset)) ; call symput("defined", 1) ; run ; %if &defined = 1 %then %do ; data _null_ ; set &libref..location ; where upcase(left(table)) = "FORMAT" ; call symput("libname", left(library)) ; run ; proc datasets library = &libname memtype = data nolist ; contents data = _all_ memtype = data out = work._out_ (keep = memname nobs) noprint; run ; quit ; data _null_ ; set _out_ ; if upcase(left(memname)) = upcase("&dataset") then do ; if nobs > 0 then call symput("chekfmt", 1) ; stop ; end ; run ; %if &chekfmt = 1 %then %do ; /* The following PROC SORT sorts the data set corresponding to the Format table. */ proc sort data = &libname..&dataset out = _format_ nodupkey ; by format ; run ; /* The following DATA step merges the data set corresponding to the Format table with the data set containing selected rows from the property data set (see the first DATA step). */ data _forms_ ; merge _forms_ _format_ ; by format ; run ; %end ; %let pcount = 0 ; %let vcount = 0 ; %let count = 0 ; /* The following DATA step creates macro variables and fills them with data from the data set, produced in the previous DATA step: vcount - contains the number of value-based formats. pcount - contains the number of picture-based formats. _p - is a series of macro variables containing names of picture-based formats. _f - is a series of macro variables containing labels for each picture-based format. _v - is a series of macro variables containing names of value-based formats. _d - is a series of macro variables containing names of the tables that contain column domain. _c - is a series of macro variables containing names of the domain columns that contain values for the formats. _m - is a series of macro variables containing names of the column that contain labels for the formats. */ data _null_ ; retain pcount vcount 1 ; call symput("_f"||left(pcount), "&mis") ; call symput("_d"||left(vcount), "&mis") ; call symput("_c"||left(vcount), "&mis") ; call symput("_m"||left(vcount), "&mis") ; set _forms_ ; if (trim(left(domtab)) = "" or left(domtab) = "&mis") and trim(left(detail)) ^= "" then do ; call symput("_f"||left(pcount),trim(left(detail))) ; call symput("_p"||left(pcount),left(substr(format, 1 , index(format, ".") - 1))) ; call symput("pcount", pcount) ; pcount + 1 ; end ; if trim(left(domtab)) ^= "&mis" then do ; call symput("_d"||left(vcount), left(domtab))= ; call symput("_c"||left(vcount), left(domcol))= ; call symput("_m"||left(vcount), left(meancol))= ; call symput("_v"||left(vcount),= left(substr(format, 1, index(format,".") - 1))) ; call symput("vcount", vcount) ; vcount + 1 ; end ; run ; /* In the following loop PROC FORMAT generates the picture-based formats. */ %if &pcount > 0 %then %do ; %do i = 1 %to &pcount ; %if %index(&&_f&i,&mis) = 0 %then %do ; proc format ; picture &&_p&i other = "&&_f&i" ; run ; %end ; %end ; %end ; %if &vcount > 0 %then %do ; /* In the following loop PROC FORMAT generates the value-based formatsthat use values from the domain and labels from the meaning data sets. The %TRANSFER macro brings these data sets from their locations to the work SAS library (see the %TRANSFER macro later in this section). */ %do i = 1 %to &vcount ; data _null_ ; set &libref..location ; where upcase(left(table)) = upcase("&&_d&i") ; call symput("_l"||left(&i), left(library)) ; run ; %transfer (&libref, &&_l&i, work, &&_d&i) ; data _domain_ (keep = &&_c&i &&_m&i) ; set &&_d&i ; %if %index(&&_v&i, $) = 1 %then %do ; &&_c&i = upcase(&&_c&i) ; %end ; run ; proc sort data = _domain_ nodupkey ; by &&_c&i ; run ; data _null_ ; retain _count 1 ; set _domain_ ; call symput("_n" || left(_count), left(&&_c&i)) ; call symput("_k" || left(_count), trim(left(&&_m&i))) ; call symput("count", _count) ; _count + 1 ; run ; proc format ; value &&_v&i %do j = 1 %to &count ; &&_n&j = "%nrbquote(&&_k&j)" %end ; ; run ; %end ; %end ; /* The following PROC COPY copies a formats catalog from the work library to its permanent location. */ proc copy in = work out = &libref ; select formats / memtype = catalog; run; %end ; proc datasets library = work memtype = data ; delete _domain_ _out_ _format_ _forms_ ; run ; quit ; %end ; %mend formats ; **************************************************************************************** /*The following example code appears on pages 57-61.*/ ***************************************************** /* PROGRAM TRANSFER. DESCRIPTION Implements communication operations defined in the Comoper and Commacc tables. USAGE %transfer(libref, source, target, table, mis) ; PARAMETERS libref - is the name of the library storing the data dictionary data sets. source - is the name of the library storing the data set corresponding to the table, specified by the table parameter. target - is the name of the library that will store the data set corresponding to the table, specified by the table parameter. table - is the name of the table that must be transferred from the library specified by the source parameter to the library, specified by the target parameter. mis - is the code identifying the missing value. REQUIRES The object, location, library, commacc, and comoper data sets must exist. AUTHORS T.Kolosova and S.Berestizhevsky */ %macro transfer (libref, source, target, table, mis) ; /* The following DATA step creates a dataset macro variable containing the name of the data set corresponding to the specified table. */ %let srcflag = 0 ; %let trgflag = 0 ; data _null_ ; set &libref..Object ; where upcase(left(table)) = upcase("&table") ; call symput("dataset", trim(dataset)) ; run ; /* The following DATA step creates srcloc and trgloc macro variables containing the names of source and target physical locations of the libraries specified by the source and target parameters. */ data _null_ ; set &libref..Library ; if upcase(left(library)) = upcase("&source") then call symput("srcloc", left(location)) ; if upcase(left(library)) = upcase("&target") then call symput("trgloc", left(location)) ; run ; data _null_ ; length tmp $ 200 ; set sashelp.vslib ; retain srcflag trgflag 0 ; if srcflag = 0 then do ; i = 1 ; tmp = scan(upcase(path), i, " ") ; do while (tmp ^= upcase("&srcloc") and trim(tmp) ^= "") ; i + 1 ; tmp = scan(upcase(path), i, " ") ; end ; if trim(tmp) ^= "" then do ; srcflag = 1 ; call symput("srcflag", 1) ; end ; end ; if trgflag = 0 then do ; i = 1 ; tmp = scan(upcase(path), i, " ") ; do while (tmp ^= upcase("&trgloc") and trim(tmp) ^= "") ; i + 1 ; tmp = scan(upcase(path), i, " ") ; end ; if trim(tmp) ^= "" then do ; trgflag = 1 ; call symput("trgflag", 1) ; end ; end ; if srcflag = 1 and trgflag = 1 then stop ; run ; %let exist = 0 ; %let comexist = 0 ; %let defined = 0 ; %let accesex = 0 ; /* The following DATA step creates operds and accds macro variables containing the names of the data sets corresponding to the Comoper and Commacc tables. */ data _null_ ; set &libref..Object ; if upcase(left(table)) = "COMOPER" then do ; call symput("operds", left(dataset)) ; operex = 1 ; end ; if upcase(left(table)) = "COMMACC" then do ; call symput("accds", left(dataset)) ; accesex = 1 ; end ; if operex = 1 and accesex = 1 then call symput("defined", 1) ; run ; /* The following DATA step and PROC DATASETS check existence of the data sets corresponding to the Comoper and Commacc table. */ %if &defined = 1 %then %do ; data _null_ ; set &libref..Location; if upcase(left(table)) = "COMOPER" then call symput("liboper", left(library)) ; if upcase(left(table)) = "COMMACC" then call symput("libacc", left(library)) ; run ; proc datasets library = &liboper memtype = data nolist ; contents data = _all_ memtype = data out = work.out (keep = memname nobs) noprint ; run ; quit ; data _null_ ; set out ; if upcase(left(memname)) = upcase("&operds") then do ; if nobs > 0 then call symput("exist", 1) ; stop; end ; run ; %if &exist = 1 %then %do ; proc datasets library = &libacc memtype = data nolist ; contents data = _all_ memtype = data out = work.out (keep = memname nobs) noprint ; run ; quit ; %let exist = 0 ; data _null_ ; set out ; if upcase(left(memname)) = upcase("&accds") then do ; if nobs > 0 then call symput("exist", 1) ; stop; end ; run ; %end ; %end ; /* The following DATA step creates communid and type macro variables containing the communication access identification number and type of communication between the source and target libraries. */ %if &defined = 1 and &exist = 1 %then %do ; data _null_ ; set &liboper..&operds ; where upcase(left(library)) = upcase("&source") and upcase(left(tarlib)) = upcase("&target") ; call symput("communid", communid) ; call symput("type", commtype) ; call symput("comexist", 1) ; run ; /* The following DATA step creates macro variables containing information about communication parameters corresponding to the communication access identification number specified in the communid macro variable (see the previous DATA step). */ %if &comexist = 1 %then %do ; data _null_ ; set &libacc..&accds ; where communid = &communid ; call symput("access", communic) ; call symput("localos", left(localos)) ; call symput("remoteos", left(remoteos)) ; call symput("protocol", left(protocol)) ; call symput("comport", left(comport)) ; run ; /* The following SAS commands (libname, options, rsubmit, signon, etc), PROC UPLOAD, PROC DOWNLOAD, and PROC COPY execute required communication operations according to values of the previously created macro variables. */ %if %upcase(&type) = U or %upcase(&type) = D %then %do ; options comamid = &comamid remote = &comport ; signon "&protocol" ; rsubmit ; %if %upcase(&source) ^= WORK and &srcflag = 0 %then libname &source "&srcloc" ;; %if %upcase(&target) ^= WORK and &trgflag = 0 %then libname &target "&trgloc" ;; %if %upcase(&type) = U %then %let procname = upload ; %else %let procname = download ; proc &procname data = &source..&dataset out = &target..&dataset ; run ; endrsubmit ; signoff "&protocol" ; %end ; %if %upcase(&type) = T %then %do ; %let slash = ; %let extend = ; %if %upcase(&localos) = OS2 or %upcase(&localos) = WINDOWS or %upcase(&localos) = WINDOWS_NT %then %do ; %let slash = \ ; %let extend = .sd2 ; %end ; options comamid = &comamid remote = &comport ; signon "&protocol" ; rsubmit ; proc upload data = "&srcloc.&slash&dataset&extend" out = "&trgloc.&slash&dataset&extend" ; run ; endrsubmit ; signoff "&protocol" ; %end ; %end ; %end ; %if &comexist = 0 %then %do ; %if %upcase(&target) ^= WORK and &trgflag = 0 %then libname &target "&trgloc" ;; %if %upcase(&source) ^= WORK and &srcflag = 0 %then libname &source "&srcloc" ;; proc copy in = &source out = &target memtype = data ; select &dataset ; run ; %end ; %mend transfer ; ***************************************************************************************** /*The following example code appears on pages 61-64.*/ ****************************************************** /* PROGRAM DATASET. DESCRIPTION Creates a SAS data set for the specified table. USAGE %dataset(libref, table, mis) ; PARAMETERS libref - is the name of the library storing the data dictionary data sets. table - is the name of the table that is required to create the SAS data set. mis - is the code identifying the missing value. REQUIRES The object, property, location, library, commacc, and comoper data sets must exist. In addition the formats catalog must exist. AUTHORS T.Kolosova and S.Berestizhevsky */ %macro dataset(libref, table, mis) ; /* The following DATA step creates a dataset macro variable containing the name of the data set corresponding to the specified table. */ data _null_ ; set &libref..Object ; where upcase(left(table)) = upcase("&table") ; call symput("dataset", trim(dataset)) ; run ; %let count = 0 ; %let _icount = 0 ; /* The following DATA step creates macro variables and fills them with data from the property data set: count - contains number of columns of the specified table. _v0 - is a series of macro variables containing names of the columns of the specified table with their types and length. _v1 - is a series of macro variables containing names of the columns of the specified table with their formats. _v3 - is a series of macro variables containing names of the columns of the specified table for which indexes are defined. _v4 - is a series of macro variables containing names of the columns of the specified table with their titles. */ data _null_ ; retain _count 1 _icount 0 ; call symput("_v0" || left(_count), "&mis") ; call symput("_v1" || left(_count), "&mis") ; set &libref..Property ; where upcase(left(table)) = upcase("&table") ; call symput("_v4" || left(_count), trim(left(column)) || ' %nrbquote(' || trim(left(title)) || ')') ; if index(upcase(attribut), "I") > 0 then do ; _icount + 1 ; call symput("_v3" || left(_icount), left(column)) ; call symput("_icount", left(_icount)) ; end ; if upcase(type) = "C" then call symput("_v0" || left(_count), trim(left(column)) || " $" || left(length)) ; if upcase(type) = "N" then call symput("_v0" || left(_count), trim(left(column)) || " " || left(length)) ; if left(format) ^= "&mis" then call symput("_v1" || left(_count), trim(left(column)) || " " || trim(left(format))) ; else do ; if upcase(type) = "C" then call symput("_v1" || left(_count), trim(left(column)) || " $CHAR" || trim(left(length)) || ".") ; if upcase(type) = "N" then call symput("_v1" || left(_count), trim(left(column)) || " " || trim(left(length)) || ".") ; end ; call symput("count", _count) ; _count + 1 ; run ; /* The following DATA step generates SAS data set for the specified table according to the values of the macro variables created in the previous DATA steps. */ data &dataset ; length %do _j = 1 %to &count ; &&_v0&_j %end ; ; format %do _j = 1 %to &count ; &&_v1&_j %end ; ; label %do _j = 1 %to &count ; &&_v4&_j %end ; ; stop ; run ; /* The following PROC SQL creates indexes, if any, for the SAS data set generated in the previous DATA step according to the values of the macro variables stored in the _v3 macro variable. */ %if &_icount > 0 %then %do ; proc sql ; %if &_icount = 1 %then %do ; create index &&_v3&_icount on &dataset (&&_v3&_icount) ; %end ; %else %do ; create index __index on &dataset ( &&_v31 %do i = 2 %to &_icount ; , &&_v3&_icount %end ; ) ; %end ; %end ; /* The following DATA step creates a macro variable containing the name of the library where the generated SAS data set corresponding to the specified table must be stored. */ data _null_ ; set &libref..Location ; where upcase(left(table)) = upcase("&table") ; call symput("libname", left(library)) ; run ; /* The %TRANSFER macro delivers the generated SAS data set to its permanent location. */ %transfer (&libref, work, &libname, &table) ; proc datasets library = work memtype = data ; delete &dataset ; run ; quit ; %mend dataset ; ****************************************************************************************** /*The following example code appears on pages 95-101.*/ ******************************************************** /* PROGRAM INPUTID. DESCRIPTION Creates an input operation table for the specified process. USAGE %inputid (libname, inputid) ; PARAMETERS libname - is the name of the library storing the data dictionary data sets, such as pinput, pinit, pbinary, pselect, etc. inputid - is the input identification name. REQUIRES The pinput, pinit, pbinary, pselect, pproject data sets must exist. AUTHORS T.Kolosova and S.Berestizhevsky. */ %macro inputid (libname, inputid) ; /* The following DATA step creates _input data set containing common rows of the pinput and pinit data sets according to the primary key. */ data _input ; merge &libname..PInput (in = _left) &libname..PInit (in = _right) ; by inputid ; if _left and _right ; run ; /* The following DATA step creates macro variables and fills them with data from the _input data set (see DATA step above): count - contains the number of relational operators forming the input operation table for the specified input identification name. _opr - is a series of macro variables containing names of the relational operators for the specified input indentification name. */ data _input (drop = _n) ; retain _n 1 ; set _input ; where upcase(left(inputid)) = upcase("&inputid") ; call symput("_opr" || left(_n), upcase(operator)) ; call symput("count", _n) ; _n + 1 ; run ; %let binary = 0 ; %let sunary = 0 ; %let punary = 0 ; %do i = 1 %to &count ; %if &&_opr&i = J or &&_opr&i = U or &&_opr&i = D or &&_opr&i = V or &&_opr&i = R %then %let binary = 1 ; %if &&_opr&i = S %then %let sunary = 1 ; %if &&_opr&i = P %then %let punary = 1 ; %end ; %if &binary = 1 %then %do ; /* If the macro variable from the _opr series of macro variables contains the name of binary relational operators, such as natural join, union, etc, then the following DATA step creates the _input data set containing common rows of the _input (see DATA step above) and pbinary data sets according to the primary key. */ data _input ; merge _input (in = _left) &libname..PBinary ; by inputid operord ; if _left ; run ; %end ; %if &sunary = 1 %then %do ; /* If the macro variable from the _opr series of macro variables contains the name of the selection relational operator, then the following DATA step creates the _input data set containing common rows of the _input (see DATA step above) and pselect data sets according to the primary key. */ data _input ; merge _input (in = _left) &libname..PSelect ; by inputid operord ; if _left ; run ; %end ; %if &punary = 1 %then %do ; /* If the macro variable from _opr series of macro variables contains the name of project relational operator, the the following DATA step creates the _input data set containing common rows of the _input (see DATA step above) and pproject data sets according to the primary key. */ data _input ; merge _input (in = _left) &libname..PProject ; by inputid operord ; if _left ; run ; %end ; /* The following DATA step creates macro variables and fills them with data from the _input data set (see DATA step above): _ll - is a series of macro variables containing names of the libraries storing the data sets corresponding to the initial tables. _tl - is a series of macro variables containing names of the initial tables data sets. _lr - is a series of macro variables containing names of the libraries storing the data sets corresponding to the right-hand tables for the binary relational operators. _tr - is a series of macro variables containing names of the right-hand data sets corresponding to the right-hand tables for the binary relational operators. */ %do i = 1 %to &count ; data _null_ ; set _input ; where operator = "&&_opr&i" and operord = &i; %if &i = 1 %then %do ; call symput("_ll" || left(&i), left(trim(initlib))) ; call symput("_tl" || left(&i), left(trim(initab))) ; %end ; %if &i > 1 %then %do ; call symput("_ll" || left(&i), "work") ; call symput("_tl" || left(&i), "_intern_") ; %end ; call symput("_lr" || left(&i), left(trim(rellib))) ; call symput("_tr" || left(&i), left(trim(reltable))) ; run ; %if &&_opr&i = &mis %then %do ; data _intern ; set &&_ll&i...&&_tl&i ; run ; %end ; %else %do ; /* The following DATA step creates macro variables and fills them with data from the plink data set: _colcnt - contains the number of related columns of the data sets participating in the binary relational operators if one of these data sets is an intermediate table. _l - is a series of macro variables containing names of the related columns from the left-hand data set. _r - is a series of macro variables containing names of the related columns from the right-hand data set. */ %let _colcnt = 0 ; data _null_ ; retain _colcnt 1 ; set &libname..PLink ; where upcase(left(inputid)) = upcase("&inputid") and operord = &i ; call symput("_l" || left(_colcnt), left(column)) ; call symput("_r" || left(_colcnt), left(relcol)) ; call symput("_colcnt", _colcnt) ; _colcnt + 1 ; run ; %if &_colcnt = 0 %then %do ; /* The following DATA step creates macro variables and fills them with data from the link data set: _colcnt - contains the number of related columns of the data sets participating in the binary relational operators. _l - is a series of macro variables containing names of the related columns from the left-hand data set. _r - is a series of macro variables containing names of the related coluns from the right-hand data set. */ data _null_ ; retain _colcnt 0 ; set &libname..Link ; if upcase(left(table)) = upcase("&&_tl&i") and upcase(left(reltable)) = upcase("&&_tr&i") then do ; _colcnt + 1 ; call symput("_l" || left(_colcnt), left(column)) ; call symput("_r" || left(_colcnt), left(relcol)) ; call symput("_colcnt", _colcnt) ; end ; if upcase(left(table)) = upcase("&&_tr&i") and upcase(left(reltable)) = upcase("&&_tl&i") then do ; _colcnt + 1 ; call symput("_r" || left(_colcnt), left(column)) ; call symput("_l" || left(_colcnt), left(relcol)) ; call symput("_colcnt", _colcnt) ; end ; run ; %end ; /* According to the following conditions the macro programs that implement relational operators will be invoked. */ %if &&_opr&i = R %then %product (&i, &_colcnt) ; %if &&_opr&i = U %then %union (&i) ; %if &&_opr&i = D %then %differ (&i, &_colcnt) ; %if &&_opr&i = V %then %division (&i, &_colcnt) ; %if &&_opr&i = J %then %join (&i, &_colcnt); %if &&_opr&i = S %then %select (&i) ; %if &&_opr&i = P %then %project (&i) ; %end ; %end ; proc datasets library = work memtype = data ; delete _input ; run ; quit ; %mend inputid ; /* PROGRAM JOIN. DESCRIPTION Creates an intermediate table produced by the natural join relational operator. USAGE %join (i, _colcnt) ; PARAMETERS i - is the current number of left-hand and right-hand data sets and their libraries whose names are specified in the %INPUTID macro by the _tl, _tr, _ll and _lr series of macro variables. _colcnt - is the number of the columns linking the joined data sets. The column names are specified in the %INPUTID macro by the _l and _r series of macro variables. REQUIRES The left-hand and right-hand data sets must exist. AUTHORS T.Kolosova and S.Berestizhevsky. */ %macro join (i, _colcnt) ; proc sql ; create table _intern_ as select * from &&_ll&i...&&_tl&i inner join &&_lr&i...&&_tr&i on %do loop = 1 %to &_colcnt ; &&_tl&i...&&_l&loop = &&_tr&i...&&_r&loop and %end ; 1 = 1 ; quit ; %mend join ; /* PROGRAM UNION. DESCRIPTION Creates an intermediate table produced by the union relational operator. USAGE %union (i) ; PARAMETERS i - is the current number of left-hand and right-hand data sets and their libraries whose names are specified in the %INPUTID macro by the _tl, _tr, _ll, and _lr series of macro variables. REQUIRES The left-hand and right-hand data sets must exist. AUTHORS T.Kolosova and S.Berestizhevsky. */ %macro union (i) ; proc sql ; create table _intern_ as select * from &&_ll&i...&&_tl&i union all select * from &&_lr&i...&&_tr&i ; quit ; %mend union ; /* PROGRAM DIFFER. DESCRIPTION Creates an intermediate table produced by the difference relational operator. USAGE %differ (i, _colcnt) ; PARAMETERS i - is the current number of left-hand and right-hand data sets and their libraries whose names are specified in the %INPUTID macro by the _tl, _tr, _ll, and _lr series of macro variables. _colcnt - is the number of the columns linking the differenced data sets. The column names are specified in the %INPUTID macro by the _l and _r series of macro variables. REQUIRES The left-hand and right-hand data sets must exist. AUTHORS T.Kolosova and S.Berestizhevsky. */ %macro differ (i, _colcnt) ; proc sql ; create table _temp_ as select %do loop = 1 %to &_colcnt ; %if &loop > 1 %then , ; &&_l&loop %end ; from &&_ll&i...&&_tl&i except select %do loop = 1 %to &_colcnt ; %if &loop > 1 %then , ; &&_r&loop as &&_l&loop %end ; from &&_lr&i...&&_tr&i ; create table _intern_ as select * from _temp_ inner join &&_ll&i...&&_tl&i on %do loop = 1 %to &_colcnt ; _temp_.&&_l&loop = &&_tl&i...&&_l&loop and %end ; 1 = 1 ; quit ; %mend differ ; ***************************************************************************************** /*The following example code appears on pages 101-105.*/ ******************************************************** /* PROGRAM OUTPUTID. DESCRIPTION Creates an output operation table for the specified process. USAGE %outputid (libname, outputid, mis) ; PARAMETERS libname - is the name of the library storing the data dictionary data sets, such as poutput, pupdate, etc. outputid - is the output identification name. mis - is the code identifying the missing value. REQUIRES The poutput, pupdate, poutrule, and property data sets must exist. AUTHORS T.Kolosova and S.Berestizhevsky. */ %macro outputid (libname, outputid, mis) ; /* The following DATA step creates macro variables and fills them with data from the poutput data set: outype - is the output table type. outable - is the output operation table name. outlib - is the library name storing the output operation table. ruleid - is the updating rule identification name. */ data _null_ ; set &libname..POutput ; where upcase(left(outputid)) = upcase("&outputid") ; call symput("outype", outype) ; call symput("outable", trim(left(outable))) ; call symput("outlib", trim(left(outlib))) ; call symput("ruleid", left(ruleid)) ; run ; %if &ruleid ^= &mis %then %do ; /* The following DATA step creates macro variables and fills them with data from the pupdate data set: _count - contains number of columns corresponding to the value of ruleid macro variable. _c - is a series of macro variables containing names of the columns corresponding to the value of ruleid macro variable. _e - is a series of macro variables containing expressions corresponding to the value of ruleid macro variable. */ data _null_ ; retain _count 1 ; set &libname..PUpdate ; where upcase(left(ruleid)) = upcase("&ruleid") ; call symput("_c" || left(_count), left(column)) ; call symput("_e" || left(_count), left(expressn)) ; call symput("_count", _count) ; _count + 1 ; run ; %end ; %if %upcase(&outype) = N %then %do ; %if &ruleid ^= &mis %then %do ; /* The following DATA step creates a new data set corresponding to the output operation table. */ data &outlib..&outable (keep = %do i = 1 %to &_count ; &&_c&i %end ;); set _intern_ ; %do i = 1 %to &_count ; &&_c&i = &&_e&i ; %end ; run ; %end ; %else %do ; data &outlib..&outable ; set _intern ; run ; %end ; %end ; %if %upcase(&outype) = E %then %do ; /* The following DATA step creates macro variables and fills them with data from the poutrule data set: expressn - is the macro variable containing the expression for rows selection from the existing output operation table. updtype - is the macro variable containing the type of update for existing output operation table. */ data _null_ ; set &libname..POutrule ; where upcase(left(outputid)) = upcase("&outputid") ; call symput("expressn", trim(left(expressn))) ; call symput("updtype", updtype) ; run ; /* The following DATA step creates macro variables and fills them with data from the property data set: _pcnt - contains number of columns forming the primary key of the output operation table. _p - is a series of macro variables containing names of the columns forming the primary key of the output operation table. */ data _null_ ; retain _count 1 ; set &libname..Property ; where upcase(left(table)) = upcase("&outable") ; if index(upcase(attribut), "P") > 0 then do ; call symput("_p" || left(_count), left(column)) ; call symput("_pcnt" , _count) ; _count + 1 ; end ; run ; /* The following DATA step creates the _out_ and _outable data sets containing rows selected from the output operation table according to the value of the expressn macro variable (see DATA step above). */ data _out_ _outable; set &outlib..&outable ; if &expressn then output _out_ ; else output _outable ; run ; %if &ruleid ^= &mis %then %do ; /* The following DATA step updates the intermediate operation data set. */ data _intern_ (keep = %do i = 1 %to &_count ; &&_c&i %end ;); set _intern_ ; %do i = 1 %to &_count ; &&_c&i = &&_e&i ; %end ; run ; %end ; %if %upcase(&updtype) = A %then %do ; /* The following DATA step creates the _out_ intermediate operation data set from the updated intermediate data set and _out_ data set (see DATA step above). Such a data set contains data updating according to the Add type of update. */ data _out_ (keep = %do i = 1 %to &_count ; &&_c&i %end ;) ; ; merge _intern_ (in = _left) _out_ (in = _right) ; by %do i = 1 %to &_pcnt ; &&_p&i %end ; ; if _right or (_left and not _right) ; run ; %end ; %if %upcase(&updtype) = R %then %do ; /* The following DATA step creates the _out_ intermediate operation data set from the updated intermediate data set and _out_ data set (see DATA step above). Such a data set contains data updating according to the Replace type of update. */ data _out_ (keep = %if &ruleid ^= &mis %then %do ; (keep = %do i = 1 %to &_count ; &&_c&i %end ;) ; %end ; ; set _intern_ ; by %do i = 1 %to &_pcnt ; &&_p&i %end ; ; if last.&&_p&_pcnt then output ; run ; %end ; %if %upcase(&updtype) = S %then %do ; /* The following DATA step creates the _out_ intermediate operation data set from the updated intermediate data set and _out_ data set (see DATA step above). Such a data set contains data updatedg according to the Subtract type of update. */ data _out_ (keep = %do i = 1 %to &_count ; &&_c&i %end ;) ; ; merge _intern_ (in = _left) _out_ (in = _right) ; by %do i = 1 %to &_pcnt ; &&_p&i %end ; ; if _right and not _left ; run ; %end ; /* The following PROC APPEND appends the _out_ intermediate operation data set to the existing output operation data set corresponding to the output operation table. */ proc append base = _outable data = _out_ force; run ; proc sort data = _outable out = &outlib..&outable ; by %do i = 1 %to &_pcnt ; &&_p&i %end ; ; run ; proc datasets library = work memtype = data ; delete _out_ ; run ; quit ; %end ; %mend outputid ; **************************************************************************************** /*The following example code is on pages 105-108.*/ *************************************************** /* PROGRAM PREVIOUS. DESCRIPTION Stores the previous value of the column. USAGE %previous(varname) ; PARAMETERS varname - is the name of the table column. AUTHORS T.Kolosova and S.Berestizhevsky */ %global libref mis ; %let libref = kernel ; %let mis = . ; %macro previous(varname) ; %let prevar = _&varname ; %if %length(&prevar) > 8 %then %let prevar = %substr(&prevar, 1, 8) ; &prevar %mend previous ; /* PROGRAM SAME. DESCRIPTION Checks previous and current values of the column. USAGE %same(varname) ; PARAMETERS varname - is the name of the table column. */ %macro same(varname) ; %let prevar = _&varname ; %if %length(&prevar) > 8 %then %let prevar = %substr(&prevar, 1, 8) ; trim(&varname) = trim(&prevar) %mend same ; /* PROGRAM PREVAR. DESCRIPTION Generates length and retain SAS language statements needed for implementaion of the %PREVIOUS macro. USAGE %prevar(varname) ; PARAMETERS varname - is the name of the table column. AUTHORS T.Kolosova and S.Berestizhevsky */ %macro prevar(varname) ; %let prevar = _&varname ; %if %length(&prevar) > 8 %then %let prevar = %substr(&prevar,1,8) ; length &prevar $ 200 ; retain &prevar "&mis" ; %mend prevar ; /* PROGRAM POSTVAR. DESCRIPTION Generates the assigment SAS language statement needed for implementaion of the %PREVIOUS macro. USAGE %postvar(varname) ; PARAMETERS varname - is the name of the table column. AUTHORS T.Kolosova and S.Berestizhevsky */ %macro postvar(varname) ; %let prevar = _&varname ; %if %length(&prevar) > 8 %then %let prevar = %substr(&prevar,1,8) ; &prevar = &varname; %mend postvar ; /* PROGRAM SORT. DESCRIPTION Sorts observations in the _intern_ intermediate data set. USAGE %sort(varname) ; PARAMETERS varname - is the list of the data set columns defining the order of sorting. */ %macro sort(varname) ; proc sort data = _intern_ ; by &varname ; run ; %mend sort ; /* PROGRAM MOVE. DESCRIPTION Moves data sets between SAS libraries according to the meta data of the _intern_ intermediate operation table. USAGE %move() ; */ %macro move ; /* The following DATA step creates macro variables containing information about source target libraries and their communication parameters storing in the _intern_ data set. */ screen form. data _null_ ; set _intern_ ; call symput("table", trim(left(table))) ; call symput("library", trim(left(library))) ; call symput("tarlib", trim(left(tarlib))) ; call symput("localos", trim(left(localos))) ; call symput("remoteos", trim(left(remoteos))) ; call symput("commtype", commtype) ; call symput("protocol", trim(left(protocol))) ; call symput("comport", trim(left(comport))) ; run ; /* The following DATA step creates the dataset macro variable containing the name of the data set corresponding to the table name stored in the table macro variable. */ data _null_ ; set &libref..Object ; where upcase(left(table)) = upcase("&table") ; call symput("dataset", trim(dataset)) ; run ; /* The following SAS commands (options, rsubmit, signon, etc), PROC UPLOAD, PROC DOWNLOAD, and PROC COPY execute required communication operations according to values of the previously created macro variables. */ %if %upcase(&commtype) = U or %upcase(&commtype) = D %then %do ; options comamid = &comamid remote = &comport ; signon "&protocol" ; rsubmit ; %if %upcase(&commtype) = U %then %let procname = upload ; %else %let procname = download ; proc &procname data = &library..&dataset out = &tarlib..&dataset ; run ; endrsubmit ; signoff "&protocol" ; %end ; %if %upcase(&commtype) = T %then %do ; /* The following DATA step creates srcloc and trgloc macro variables containing the names of source and target physical locations of the libraries stored in the library and tarlib macro variables. */ data _null_ ; set &libref..Library ; if upcase(left(library)) = upcase("&library") then call symput("srcloc", left(location)) ; if upcase(left(library)) = upcase("&tarlib") then call symput("trgloc", left(location)) ; run ; %let slash = ; %let extend = ; %if %upcase(&localos) = OS2 or %upcase(&localos) = WINDOWS or %upcase(&localos) = WINDOWS_NT %then %do ; %let slash = \ ; %let extend = .sd2 ; %end ; options comamid = &comamid remote = &comport ; signon "&protocol" ; rsubmit ; proc upload data = "&srcloc.&slash&dataset&extend" out = "&trgloc.&slash&dataset&extend" ; run ; endrsubmit ; signoff "&protocol" ; %end ; %if &commtype = &mis %then %do ; proc copy in = &library out = &tarlib memtype = data ; select &dataset ; run ; %end ; proc datasets library = &library memtype = data ; delete &dataset ; run ; quit ; %mend move ; ***************************************************************************************** /*The following code appears on pages 111-114.*/ ************************************************ /* PROGRAM MANID. DESCRIPTION Implements specified manipulations with input operation table storing in the _intern_ intermediate data set. USAGE %manid (libname, manid, mis) ; PARAMETERS libname - is the name of the library storing the data dictionary data sets, such as pman and pmacro. manid - is the manipulation identification name. mis - is the code identifying the missing value. REQUIRES The pman and pmacro data sets must exist. Also "user-written" macros and "environmental" macros must be written and placed in the autocall SAS library. AUTHORS T.Kolosova and S.Berestizhevsky. */ %macro manid (libname, manid, mis) ; /* The following DATA step reads meta data from the pman and pmacro data sets and creates three temporary data sets: _before_, _in_ and _after_. These data sets contain specification of manipulations that must be executed with the input operation table. Because such manipulations are presented by the PROC step and DATA step, the _before_ data set contains specifications of the PROC step executing before the DATA step specified in the _in_ data set, and the _after_ data set contains specification of the PROC step executing after specified DATA step. */ %let _before = 0 ; %let _in = 0 ; %let _after = 0 ; data _before_ (keep = manord expressn) _in_ (keep = manord expressn) _after_ (keep = manord expressn) ; set &libname..Pman nobs = nman ; length pmacro $ 9 flag property $ 1 _str _str1 $ 80 ; retain _before _in _after 0 flag "B" ; where upcase(left(manid)) = upcase("&manid") ; _str1 = expressn ; _str2 = expressn ; mo = manord ; property = "&mis" ; ind = index(_str1, "%") ; do while(ind > 0) ; pmacro = upcase(substr(_str1, ind, index(substr(_str1, ind+1), "("))) ; do i = 1 to nmacro ; set &libname..PMacro nobs = nmacro point = i ; if pmacro = upcase(left(macro)) then do ; _str = substr(_str1, ind, index(substr(_str1, ind), ")")) ; if left(substr(_str, index(_str, "(") + 1)) = ")" then do ; len = length(_str) ; ind1 = index(_str2, trim(_str)) ; _str = substr(_str, 1, index(_str, "(") - 1) ; if ind1 + len >= length(_str2) then do ; if ind1 > 1 then _str2 = substr(_str2, 1, ind1-1) || trim(_str) ; else _str2 = trim(_str) ; end ; else do ; if ind1 > 1 then _str2 = substr(_str2,1, ind1-1) || trim(_str) || substr(_str2, ind1+len) ; else _str2 = trim(_str) || substr(_str2, ind1+len) ; end ; end ; if upcase(property) = "I" then do ; flag = "A" ; if trim(premacro) ^= "&mis" then do ; expressn = upcase(trim(tranwrd(_str, trim(macro), trim(premacro)))) || " ;" ; manord = 0 ; output _in_ ; end ; if trim(pstmacro) ^= "&mis" then do ; expressn = upcase(trim(tranwrd(_str, trim(macro), trim(pstmacro)))) || " ;" ; manord = nman + 1 ; output _in_ ; end ; end ; else do ; expressn = trim(_str) || " ;" ; if flag = "B" then do ; _before = 1 ; output _before_ ; end ; else do ; _after = 1 ; output _after_ ; end ; end ; i = nmacro ; end ; end ; _str1 = substr(_str1, ind+1) ; ind = index(_str1, "%") ; end ; if upcase(property) ^= "O" then do ; expressn = upcase(_str2) ; manord = mo ; output _in_ ; _in = 1 ; end ; call symput("_before", left(_before)) ; call symput("_in", left(_in)) ; call symput("_after", left(_after)) ; run ; /* The following PROC SORT sorts the _in_ data set and elimitates duplicate rows. */ %if &_in > 0 %then %do ; proc sort data = _in_ nodupkey ; by manord expressn ; run ; %end ; /* The following DATA step creates macro variables and fills them with data from the _before_, _in_, and _after_ data sets: _e - is a series of macro variables containing manipulation specifications. count - contains number of manipulations specified for the input operation table. */ data _null_ ; count = 0 ; %if &_before %then %do ; do i = 1 to nbefore ; set _before_ nobs = nbefore point = i; count + 1 ; call symput("_e" || left(count), expressn) ; end ; %end ; %if &_in > 0 %then %do ; count + 1 ; call symput("_e" || left(count), "data _intern_ ;") ; count + 1 ; call symput("_e" || left(count), "set _intern_ ;") ; do i = 1 to nin ; set _in_ nobs = nin point = i; count + 1 ; call symput("_e" || left(count), expressn) ; end ; count + 1 ; call symput("_e" || left(count), "run ;") ; %end ; %if &_after > 0 %then %do ; do i = 1 to nafter ; set _after_ nobs = nafter point = i ; count + 1 ; call symput("_e" || left(count), expressn) ; end ; %end ; call symput("count", left(count)) ; stop ; run ; /* The following loop generates a PROC step and a DATA step executing defined manipulations with the input operation table. */ %do i = 1 %to &count ; %str(&&_e&i) %end ; proc datasets library = work memtype = data ; delete _before_ _in_ _after_ ; run ; quit ; %mend manid ; ***************************************************************************************** /*The following example code appears on pages 115-117.*/ ******************************************************** /* PROGRAM PROCESS. DESCRIPTION Implements object-dependent processes through the trigger-message mechanism. USAGE %process (libname, procid, table, library, event, eventype, mis) ; PARAMETERS libname - is the name of the library storing the data dictionary data sets, such as process, pattach, pinput, etc. procid - is the process identification name. table - is the name of the attached table. library - is the name of the library storing the data set corresponding to the attached table. event - is the name of event according which the specified process must be executed. eventype- is the type of event determined when the specified process must be executed. mis - is the code identifying the missing value. REQUIRES The pprocess, pattach, poper and poutput data sets must exist. AUTHORS T.Kolosova and S.Berestizhevsky. */ %macro process (libname, procid, table, library, event, eventype, mis) ; %global error ; %if &procid ^= &mis %then %do ; /* The following DATA step creates macro variables and fills them with data from the pprocess data set: proctype - contains the type of specified process. title - contains the title defined for the specified process. */ data _null_ ; set &libname..PProcess ; where upcase(left(procid)) = upcase("&procid") ; call symput("proctype", left(proctype)) ; call symput("title", left(title)) ; run ; %end ; %else %do ; /* The following DATA step creates a procid macro variable containing the process identification name according to the specified attached table, its library, event, and event type. */ data _null_ ; set &libname..PAttach ; where upcase(left(table)) = upcase("&table") and upcase(left(library)) = upcase("&library") and upcase(event) = upcase("&event") and upcase(eventype) = upcase("&eventype") ; call symput("procid", left(procid)) ; run ; %if &procid ^= &mis %then %do ; /* The following DATA step creates macro variables and fills them with data from the pprocess data set: proctype - contains the type of specified process. title - contains the title defined for the specified process. */ data _null_ ; set &libname..PProcess ; where upcase(left(procid)) = upcase("&procid") ; call symput("proctype", left(proctype)) ; call symput("title", left(title)) ; run ; %end ; %end ; %if &procid ^= &mis %then %do ; /* The following DATA step creates macro variables and fills them with data from the poper data set: _inp - is a series of macro variables containing input identification names for the specified process. _man - is a series of macro variables containing manipulation identification names for the specified process. _out - is a series of macro variables containing output identification names for the specified process. order - contains the number of operations for the specified process. */ data _null_ ; set &libname..POper ; where upcase(left(procid)) = upcase("&procid") ; call symput("_inp" || left(order), inputid) ; call symput("_man" || left(order), manid) ; call symput("_out" || left(order), outputid) ; call symput("order", order) ; run ; /* The following loop invokes the %INPUTID, %MANID, and %OUTPUTID macros accomplishing specified operations. To analyze messages generated by such macros, the following loop invokes %MSG macro. */ %do _i = 1 %to &order ; %inputid (&libname, &&_inp&_i) ; %msg (&libname, &procid, &_i, inputid) ; %if &error = E %then %do ; %let _i = %eval(&order + 1) ; %goto _end_ ; %end ; %if &&_man&_i ^= &mis %then %do ; %manid (&libname, &&_man&_i, &mis) ; %msg (&libname, &procid, &_i, manid) ; %if &error = E %then %do ; %let _i = %eval(&order + 1) ; %goto _end_ ; %end ; %end ; %if &&_out&_i ^= &mis %then %do ; %outputid (&libname, &&_out&_i, &mis) ; %msg (&libname, &procid, &_i, outputid) ; %if &error = E %then %do ; %let _i = %eval(&order + 1) ; %goto _end_ ; %end ; %end ; %let fire = 0 ; /* The following DATA set checks to be sure the output operation table of the current operation is not an existing application table. */ data _null_ ; set &libname..POutput ; where upcase(left(outputid)) = upcase("&&_out&_i") and upcase(outype) = "E" ; call symput("library", left(library)) ; call symput("table", left(outable)) ; call symput("fire", 1) ; run ; %if &fire = 1 %then %do ; /* The following %PROCESS macro "fires" the process attached to the application table if the output operation table of current process is an existing application table. */ %process (&libname, &mis, &table, &library, U, A, &mis) ; %end ; %_end_ : %end ; %end ; %mend process ; **************************************************************************************** /*The following example code appears on pages 142-143.*/ ******************************************************** /* PROGRAM ERRCLEAR. DESCRIPTION Deletes from the error and errorval data sets rows containing information about the specified table. USAGE %errclear (libname, vertype, table, vernum) ; PARAMETERS libname - is the name of the library storing the data dictionary data sets. vertype - is the name of the verification type containing one of the following: TA, RE, DO, RO, CO, CR. table - is the name of the verified table. vernum - is the number of verification process derived from the table of the Rule set specifying the required verification type. REQUIRES The error and errorval data sets must exist. AUTHORS T.Kolosova and S.Berestizhevsky. */ %macro errclear(libname, vertype, table, vernum) ; /* The global macro variable lasterr accumulates the number of errors detected in the verification processes. */ %global lasterr ; %let lasterr = 0 ; %let last = 0 ; /* The following DATA step deletes rows from the error data set containing information concerning the verified table. */ data &libname..Error ; set &libname..Error nobs = _last ; if datetime <= datetime() and upcase(vertype) = upcase("&vertype") and upcase(left(table)) = upcase("&table") and vernum = &vernum then do ; call symput("lasterr", left(vprocid)); delete ; end ; call symput("last", left(_last)) ; run ; /* The following DATA step deletes rows from the errorval data set containing information about errors in the verified table, if any. */ %if &lasterr > 0 %then %do ; data &libname..ErrorVal ; set &libname..ErrorVal ; if vprocid = &lasterr then delete ; run ; %end ; %if &lasterr = 0 %then %let lasterr = %eval(&last + 1) ; ; %mend errclear ; **************************************************************************************** /*The following example code appears on pages 144-145.*/ ******************************************************** /* PROGRAM ERRWRITE. DESCRIPTION Writes information to the error and errorval data sets about errors detected in the specified table. USAGE %errwrite(libname, table, vernum, vertype, lasterr, pcount) ; PARAMETERS libname - is the name of the library storing the data dictionary data sets. table - is the name of the verified table. vernum - is the number of verification process derived from the table of the Rule set specifying required verification type. vertype - is the name of the verification type containing one of the following: TA, RE, DO, RO, CO, CR. lasterr - is the global macro variable containing the number of the curernt verification process (is updated by the %ERRCLEAR macro). pcount - is the number of columns forming the primary key of the verified table. REQUIRES The error and errorval data sets must exist. AUTHORS T.Kolosova and S.Berestizhevsky. */ %macro errwrite (libname, table, vernum, vertype, lasterr, pcount) ; /* The following DATA step writes to the error data set information concerning the date of the verification, specified table, verification type, verification number, and number of the current verification process. */ data Error (keep = datetime vertype table vernum vprocid) ; length datetime vprocid vernum 8 vertype $ 2 table $ 8 ; datetime = datetime() ; table = "&table" ; vertype = "&vertype" ; vernum = &vernum ; vprocid = &lasterr ; output ; run ; /* The following DATA step writes to the errorval data set information concerning detected errors in the specified table. Such information is stored in the errorval data set according to the primary key of the specified table. */ data ErrorVal (rename = (_x1 = vprocid _x2 = errornum _x3 = column _x4 = value) keep = _x1 _x2 _x3 _x4) ; length _x1 $8 _x2 8 _x3 $8 _x4 $80 ; set _temp_ ; _x1 = &lasterr ; _x2 = _n_ ; %do v = 1 %to &pcount ; _x4 = left(&&_c&v) ; _x3 = left("&&_c&v") ; output ; %end ; call symput("vforeign","error") ; run ; proc append base = &libname..Error data = Error force ; run ; proc append base = &libname..ErrorVal data = ErrorVal force ; run ; proc datasets library = work memtype = data ; delete Error ErrorVal ; run ; quit ; %mend errwrite ; ***************************************************************************************** /*The following example code appears on pages 145-146.*/ ******************************************************** FSEINIT : /* An initialization phase before any observations from the SAS data set are displayed. */ return ; INIT : /* An initialization phase before each observation form the SAS data set is displayed. */ return ; MAIN : /* A main processing phase. This section is repeated each time the user modifies a column on the screen and presses the ENTER key or a functional key. */ link _PRIMARY ; link _FOREIGN ; link _DOMAIN ; link _ROWCOL ; link _CROSS; /* A LINK statement tells the program to jump immediately to the label indicated in the LINK statement and to continue executing statements from that point until a RETURN statement is executed. The RETURN statement causes program execution to return to the statement immediately following the LINK statement and to continue from there. */ return ; TERM : /* A termination phase before moving to another observation. */ return ; FSETERM : /* A termination phase. */ return ; _PRIMARY : /* Intended to insert the generated statements for Table Integrity rule. */ return ; _FOREIGN : /* Intended to insert the generated statements for Referential Integrity rule. */ return ; _DOMAIN : /* Intended to insert the generated statements for Domain Integrity rule. */ return ; _ROWCOL : /* Intended to insert the generated statements for Row and Column Integrity rules. */ return ; _CROSS : /* Intended to insert the generated statements for Cross Integrity rule. */ return ; ****************************************************************************************** /*The following example code appears on pages 147-150.*/ ******************************************************** /* PROGRAM VPRIMARY. DESCRIPTION Verifies the specified table according to the Table Integrity rule. USAGE %vprimary (libname, table, event, sclfile, libedit, mis) ; PARAMETERS libname - is the name of the library storing the data dictionary data sets, such as object, property, vprimary, etc. table - is the name of the table that must be verified. event - is the event defining invocation of the verification process. sclfile - is the name of the external file containing SCL template. libedit - is the name of the library storing the updated table that must be verified. mis - is the code identifying the missing value. REQUIRES The object, property, vprimary, and message data sets must exist. Also must exist external file containing the SCL template. AUTHORS T.Kolosova and S.Berestizhevsky. */ %macro vprimary (libname, table, event, sclfile, libedit, mis); %let vercount = noverify ; /* The following DATA step creates vernum and message macro variables and fills them with verification number and message identification number from the vprimary data set according to the specified table and the specified event. */ data _null_ ; set &libname..VPrimary ; where upcase(left(table)) = upcase("&table") and upcase(left(event)) = upcase("&event") ; call symput("vernum", left(vernum)) ; call symput("message", left(message)) ; call symput("vercount", "verify") ; call symput("vprimary", "noerrors") ; run ; %if &vercount = verify %then %do ; /* The following DATA step creates macro variables and fills them with data from the property data set: pcount - contains number of columns forming the primary key of the specified table. _c - is a series of macro variables containing names of the columns of the specified table. _t - is a series of macro variables containing types of the columns of the specified table. */ data _null_ ; retain _count 1 ; set &libname..Property ; where upcase(left(table)) = upcase("&table") and index(upcase(attribut), "P") > 0 ; call symput("_c" || left(_count), left(column)) ; call symput( "_t" || left(_count), type) ; call symput("pcount", _count) ; _count + 1 ; run ; /* According to the following condition, the program generates SCL code and stores it in the external file. Such a code implements the Table Integrity verification process while updating the specified table in the data entry environment. */ %if %upcase(&event) = U %then %do ; /* The following DATA step creates text macro variable containing error message text from the message data set. */ data _null_ ; set &libname..Message ; if message = &message then call symput("text","'" || trim(left(text)) || "'") ; run ; /* The following DATA step creates _temp_ data set containing the SCL code performing the Table Integrity verification process, based on the SCL template and generated according to the values of the macro variables _c and _t (see DATA step above). */ data _temp_ ; length line $ 100 ; infile "&sclfile" lrecl = 100 pad; input line $100. ; output ; if upcase(trim(left(line))) = "_PRIMARY :" then do ; %do j = 1 %to &pcount ; if upcase("&&_t&j") = "C" then line = "if left(&&_c&j) = '" || "&mis" || "' then do ;" ; if upcase("&&_t&j") = "N" then line = "if &&_c&j = " || " &mis then do ;" ; output ; line = " _msg_ = &text ;" ; output ; line = "erroron &&_c&j ; end ; else erroroff &&_c&j ;" ; output ; %end ; input line $100. ; do while(upcase(trim(left(line))) ^= "RETURN ;") ; input line $100. ; end ; output ; end ; run ; /* The following DATA step writes generated SCL code to the external file. */ data _null_ ; set _temp_ ; file "&sclfile" ; put line ; run ; %end ; /* According to the following condition, the program implements the Table Integrity verification process before saving the specified table after it is updated in the data entry environment. */ %if %upcase(&event) = S %then %do ; /* The following DATA step creates a macro variable containing the name of the data set corresponding to the specified table. */ data _null_ ; set &libname..Object ; where upcase(trim(left(table))) = upcase("&table") ; call symput("dsname", trim(left(dataset))) ; run ; %errclear (&libname, TA, &table, &vernum) ; proc sort data = &libedit..&dsname ; by %do k = 1 %to &pcount ; &&_c&k %end ; ; run ; /* The following DATA steps create a _temp_ data set containing the errors detected by the Table Integrity verification process and check the contents of the _temp_ data set. */ data _temp_ ; set &libedit..&dsname ; by %do k = 1 %to &pcount ; &&_c&k %end ;; %let first = first.&&_c%eval(&k - 1) ; %let last = last.&&_c%eval(&k - 1) ; if not (&first = 1 and &last = 1) then output ; run ; /* The following DATA step checks the contents of the _temp_ data set. */ %let empty = 0 ; data _null_ ; set _temp_ ; call symput("empty", _n_ ) ; run ; /* According to the following condition, the program invokes the %ERRWRITE macro writing detected errors to the error and errorval data sets. */ %if &empty > 0 %then %do ; %errwrite (&libname, &table, &vernum, TA, &lasterr, &pcount) ; %end ; %end ; proc datasets library = work memtype = data ; delete _temp_ ; run ; quit ; %end ; %mend vprimary ; ***************************************************************************************** /*The following example code appears on pages 151-154.*/ ******************************************************** /* PROGRAM VFOREIGN. DESCRIPTION Verifies the specified table according to the Referential Integrity rule. USAGE %vforeign (libname, table, event, libedit, mis) ; PARAMETERS libname - is the name of the library storing the data dictionary data sets, such as object, property, link, vforeign, etc. table - is the name of the table that must be verified. event - is the event defining invocation of the verification process. libedit - is the name of the library storing the updated table that must be verified. mis - is the code identifying the missing value. REQUIRES The object, property, vforeign, and message data sets must exist. AUTHORS T.Kolosova and S.Berestizhevsky */ %macro vforeign (libname, table, event, libedit, mis); %let vercount = 0 ; /* The following DATA step creates macro variables and fills them with data from the vforeign data set according to the specified table and the specified event: vercount - contains the total number of verification processes defined for the specified table. _r - is a series of macro variables containing names of the tables linked to the specified table. _n - is a series of macro variables containing numbers of the verification processes defined for the specified table. */ data _null_ ; retain _count 1 ; set &libname..VForeign ; where upcase(left(table)) = upcase("&table") and upcase(left(event)) = upcase("&event") ; call symput( "_r" || left(_count), left(reltable)) ; call symput( "_n" || left(_count), vernum) ; call symput("vercount", _count) ; call symput("vforeign", "noerrors") ; _count + 1 ; run ; %if &vercount > 0 %then %do ; /* The following DATA step creates macro variables and fills them with data from the property data set: pcount - contains number of columns forming the primary key of the specified table. _c - is a series of macro variables containing names of the columns of the specified table. */ data _null_ ; retain _count 1 ; set &libname..Property ; where upcase(left(table)) = upcase("&table") and index(upcase(attribut), "P") > 0 ; call symput("_c" || left(_count), left(column)) ; call symput("pcount", _count) ; _count + 1 ; run ; /* The following DATA step creates macro variables and fills them with data from the object data set: dsname - contains data set name corresponding to the specified table. _d - is a series of macro variables containing names of the data sets corresponding to the tables linked to the specified table. */ data _null_ ; set &libname..Object ; if upcase(left(table)) = upcase("&table") then call symput("dsname", trim(left(dataset))) ; %do i = 1 %to &vercount ; if upcase(left(table)) = upcase("&&_r&i") then call symput("_d" || left(&i), trim(left(dataset))) ; %end ; run ; /* The following DATA step creates macro variables and fills them with data from the location data set: _l - is a series of macro variables containing the names of the libraries where the data sets corresponding to the tables related to the specified table are placed. */ data _null_ ; set &libname..Location ; %do i = 1 %to &vercount ; if upcase(left(table)) = upcase("&&_r&i") then call symput("_l" || left(&i), trim(left(library))) ; %end ; run ; %do i = 1 %to &vercount ; %errclear (&libname, RI, &table, &&_n&i) ; %if %upcase(&event) = S %then %do ; /* The following DATA step creates macro variables and fills them with data from the link data set: linkcnt - contains the number of links between the specified table and related tables. _f - is a series of macro variables containing names of the columns of the specified table. _q - is a series of macro variables containing names of the columns of related tables. */ data _null_ ; retain _count 1 ; set &libname..Link ; where (upcase(left(table)) = upcase("&table") and upcase(left(reltable)) = upcase("&&_r&i")) or (upcase(left(table)) = upcase("&&_r&i") and upcase(left(reltable)) = upcase("&table")) ; call symput("_f" || left(_count), trim(left(column))) ; call symput("_q" || left(_count), trim(left(relcol))) ; call symput("linkcnt", _count) ; _count + 1 ; run ; /* The following PROC SORT, %TRANSFER macro, and DATA steps implement verification of the specified table according to the Referential Integrity rule. */ proc sort data = &libedit..&dsname out = _temp1_ nodupkey ; by %do k = 1 %to &linkcnt ; &&_f&k %end ; ; run ; %transfer(&libname, &&_l&i, &libedit, &&_r&i) ; proc sort data = &libedit...&&_d&i out = _temp2_ nodupkey ; by %do k = 1 %to &linkcnt ; &&_q&k %end ; ; run ; data _temp_ ; merge _temp1_ (in = _left) _temp2_ (in = _right) ; by %do k = 1 %to &linkcnt ; &&_f&k %end ;; if _left and not _right ; run ; %let empty = 0 ; data _null_ ; set _temp_ ; call symput("empty", _n_) ; run ; /* According to the following condition, the program invokes the %ERRWRITE macro writing detected errors to the error and errorval data sets. */ %if &empty > 0 %then %do ; %errwrite (&libname, &table, &&_n&i, RI, &lasterr, &pcount) ; %end ; %end ; %end ; proc datasets library = work memtype = data ; delete _temp_ temp1_ _temp2_ ; run ; quit ; %end ; %mend vforeign ; ****************************************************************************************** /*The following example code appears on pages 155-157.*/ ******************************************************** /* PROGRAM VROWCOL. DESCRIPTION Verifies specified table according to the Row and/or Column Integrity rules. USAGE %vrowcol (libname, table, event, sclfile) ; PARAMETERS libname - is the name of the library storing the data dictionary data sets, such as object, property, vrowcol, etc. table - is the name of the table that must be verified. event - is the event defining invocation of the verification process. sclfile - is the name of the external file containing SCL template. REQUIRES The object, property, vrowcol, and message data sets must exist. Also must exist external file containing the SCL template. AUTHORS T.Kolosova and S.Berestizhevsky. */ %macro vrowcol (libname, table, event, sclfile); %let vercount = 0 ; /* The following DATA step creates macro variables and fills them with data from the vrowcol data set according to the specified table and the specified event: vercount - contains total number of verification processes defined for the specified table. _vn - is a series of macro variables containing numbers of the verification processes defined for the specified table. _cl - is a series of macro variables containing names of the columns of the specified table that must be verified. _vt - is a series of macro variables containing types of verification rules for the columns of the specified table. _me - is a series of macro variables containing arithmetical or logical expressions concerning the verified columns of the specified table. _cn - is a series of macro variables containing arithmetical or logical operators concerning the verified columns of the specified table. _ex - is a series of macro variables containing arithmetical or logical expressions concerning the related columns to the verified columns of the specified table. _ms - is a series of macro variables containing message identification numbers. */ data _null_ ; retain _vcount 1; set &libname..VRowcol ; where upcase(left(table)) = upcase("&table") and upcase(left(event)) = upcase("&event"); call symput("_vn" || left(_vcount), vernum) ; call symput("_cl" || left(_vcount), trim(left(column))) ; call symput("_vt" || left(_vcount), left(vertype)) ; call symput("_me" || left(_vcount), trim(left(mainexpr))) ; call symput("_cn" || left(_vcount), trim(left(conditn))) ; call symput("_ex" || left(_vcount), trim(left(express))) ; call symput("_ms" || left(_vcount), trim(left(message))) ; call symput("vercount", _vcount) ; call symput("vrowcol", "noerrors") ; _vcount + 1 ; run ; /* According to the following condition, the program generates SCL code and stores it in the external file. Such code implements the Row and/or Column Integrity verification process while updating the specified table in the data entry environment. */ %if &vercount > 0 %then %do ; %if %upcase(&event) = U %then %do ; /* The following DATA step creates macro variables and fills them with data from the message data set according to the values of the _ms series of macro variables: _tx - is a series of macro variables containing message text for the detected errors. _mt - is a series of macro variables containing message types of the messages storing in the _tx series of macro variables. */ data _null_ ; set &libname..Message ; %do j = 1 %to &vercount ; if message = &&_ms&j then do ; call symput("_tx" || left(&j),"'" || trim(left(text)) || "'") ; call symput("_mt" || left(&j), left(mestype)) ; end; %end; run ; /* The following DATA step creates a _temp_ data set containing the SCL code performing the Row and/or Column Integrity verification process, based on the SCL template and generated according to the values of the macro variables _cn, _mt, _me, _ex, _tx, _cl (see DATA step above). */ data _temp_ ; length line $ 100 ; infile "&sclfile" lrecl = 100 pad ; input line $100. ; output ; if upcase(trim(left(line))) = "_ROWCOL :" then do ; %do j = 1 %to &vercount ; line = "if NOT ((&&_me&j) " ; output; line = "&&_cn&j (&&_ex&j)) " ; output; line = " then do ;" ; output ; line = " _msg_ = &&_tx&j ; " ; output ; if upcase(trim("&&_mt&j")) = "E" then do; line = "erroron &&_cl&j ; end; else do; erroroff &&_cl&j;" ; output ; end ; line = "end ;" ; output; %end ; input line $100. ; do while(upcase(trim(left(line))) ^= "RETURN ;") ; input line $100. ; end ; output ; end ; run ; /* The following DATA step writes generated SCL code to the external file. */ data _null_ ; set _temp_ ; file "&sclfile" ; put line ; run ; proc datasets library = work memtype = data ; delete _temp_ ; run ; quit ; %end ; %end ; %mend vrowcol ; **************************************************************************************** /* To make the REPLINE program shorter and easier to understand, we divided it into two programs: REP2FILE and REPLINE. REP2FILE is merely a part of the REPLINE program as it appears in the book and is called from the REPLINE program. REPLINE is shown in the book on pages 178-192. */ /* PROGRAM REP2FILE. DESCRIPTION Puts generated report, produced by the REPLINE macro, to the external file. USAGE %rep2file (repds) ; PARAMETERS repds - is the name of the SAS data set containing the report contents. REQUIRES This program is for internal use by the %REPLINE macro only. AUTHORS T.Kolosova and S.Berestizhevsky. */ %macro rep2file( repds ) ; %if %nrbquote(&rtpage) ^= &mis %then %let file = &tmpfile ; %else %let file = &outfile ; /* The following DATA step creates a report from the contents of the SAS data set whose name is stored in repds macro variable and writes it to external file whose name is stored in tmpfile macro variable. */ data _null_ ; length tit $ 200 ; %if &curpage = 0 %then %do ; file "&file" print notitle header = newpage ll = remain lrecl = &lsize1 ; %end ; %else %do ; file "&file" mod print notitle header = newpage ll = remain lrecl = &lsize1 ; %end ; retain count &curpage tail &rpcount p 0 ; brtemplt = symget("rtemplt") ; brtemplt = translate(trim(left(brtemplt)), "-", " ") ; set &repds ; if trim(repline) = "." || %if &rtitlen > 2 %then %do ; repeat(" ", &rtitlen-2) || %end ; %else %do ; " " || %end ; left(brtemplt) and remain = &psize - 5 then do ; tail = tail - 1 ; return ; end ; if trim(repline) = "#" || %if &rtitlen > 2 %then %do ; repeat(" ", &rtitlen-2) || %end ; %else %do ; " " || %end ; left(symget("rtemplt")) then do ; tail = tail - 1 ; return ; end ; repline = substr(repline,2) ; if remain = 2 and trim(left(repline)) = trim(left(brtemplt)) then do ; link under ; tail = tail - 1 ; if tail = 0 and &iii = &rpbrcnt then link repend ; else put ; return ; end ; if remain < tail + 3 and tail <= 7 then do ; if trim(left(repline)) ^= trim(left(brtemplt)) then put repline $char%eval(&lsize-1). ; tail = tail - 1 ; if trim(left(repline)) ^= repeat("-", &rlablen - 1) then link under ; if tail = 0 and &iii = &rpbrcnt then link repend ; else do j = remain to 1 by -1 ; put ; end ; return ; end ; put repline $char%eval(&lsize-1). ; tail = tail - 1 ; if tail = 0 and &iii = &rpbrcnt then link repend ; else if remain = 1 then link under ; return ; /* The repend block creates and writes the text to the bottom of the report. */ repend: if trim(left("%nrbquote(&rtend)")) ^= "&mis" then do ; tit = trim(left("%nrbquote(&rtend)")) ; c = int((&lsize-length(tit))/2) ; put / @c tit ; end ; return ; /* The newpage block creates and writes the text at the top of each report page. */ newpage : count + 1 ; call symput("curpage", left(count)) ; put "*"@ ; if trim(left("%nrbquote(&rtcomp)")) ^= "&mis" then do ; tit = trim(left("%nrbquote(&rtcomp)")) ; %if &lang = L %then %do ; put tit@ %str(;) %end ; %else %do ; c = &lsize - length(tit) %str(;) put @c tit@ %str(;) %end ; end ; if trim(left("%nrbquote(&rtdate)")) ^= "&mis" then do ; %if &lang = L %then %do ; tit = trim(left("%nrbquote(&rtdate)")) || " " || symget("curdate") %str(;) %end ; %else %do ; tit = trim(symget("curdate")) || " " || trim(left("%nrbquote(&rtdate)"))%str(;) %end ; c = int((&lsize - length(tit))/2) ; put @c tit@ ; end ; if trim(left("%nrbquote(&rtpage)")) ^= "&mis" then do ; %if &lang = L %then %do ; tit = trim(left("%nrbquote(&rtpage)")) || " " || left(put(count,4.)) ; c = &lsize - length(tit) %str(;) put @c tit@ %str(;) %end ; %else %do ; tit = trim(left(put(count,4.))) || " " || trim(left("%nrbquote(&rtpage)")) ; put @2 tit@ %str(;) %end ; end ; put ; c = int(&lsize/2) - 9 ; put @c "Report Line System" / ; c = (&lsize - length("%nrbquote(&rtitle)"))/2 ; tit = "%nrbquote(&rtitle)" ; put @c tit / ; if p = 0 then p = 1 ; else do ; link under ; tit = symget("rlablst") ; put @&rtitlen tit ; link under ; end ; return ; /* The under block creates and writes an underline closing report tables. */ under : put @&rtitlen "-"@ ; do i = 2 to &rlablen ; put "-"@ ; end ; put ; return ; run ; %mend rep2file ; /* PROGRAM REPLINE. DESCRIPTION Reports generation. USAGE %repline (report, libname, tmpfile, outfile, lang, psize, lsize, mis) ; PARAMETERS report - report number as it specified in RMain table. libname - is the name of the library storing the data dictionary data sets, such as object, location, and report definition data sets, such as rmain, rvars, rstat, etc. tmpfile - is the name of the external file reserved for temporary use by REPLINE program. outfile - is the name of the external file storing the generated report. lang - is language orientation specification, should be L (left-to-right orientation, like English) or R (right-to-left orientation, like Hebrew). psize - is the page size (in lines). lsize - is the page width (in columns). mis - is the code identifying the missing value. REQUIRES The following data sets must exist: object, location, rmain, rinfo, rvars, rstat, rparams, rtotal. AUTHORS T.Kolosova and S.Berestizhevsky. NOTE: In the current version of the %REPLINE macro the P and Q statistics are not implemented. */ %macro repline(report, libname, tmpfile, outfile, lang, psize, lsize, mis) ; options pagesize = &psize linesize = &lsize missing = "&mis" ; %let curpage = 0 ; %let observ = 0 ; %let rbrcount = 0 ; %let rpbrcnt = 0 ; %let rcolnum = 0 ; %let rpcount = 0 ; %let rrbrlst = ; %let rtitlen = ; %let rpgcount = 0 ; %let rpbrlst = ; %let numlst = ; %let rsrtlst = ; %let rtemplt = | ; %let rlablst = | ; %let rrevvar = ; %let rowbreak = 0 ; %let ful_lst = | ; %let lang = %upcase(&lang) ; %let lsize1 = %eval(&lsize + 2) ; /* The following DATA step creates the macro variable curdate that contains the date of the report generation. */ data _null_ ; call symput("curdate",trim(left(day(date()))) || "/" || trim(left(month(date()))) || "/" || trim(left(year(date())))) ; run ; /* The following DATA step creates macro variables containing data from the rmain data set: rtab - contains the name of the table containing data for the report. rattr - contains the report attributes. rtitle - contains the report title. rtcomp - contains the name of the organization producing the report. rtdate - contains prompt text for the date. rtpage - contains prompt text for page numbers. rtend - contains the text that will appear at the bottom of the report. */ data _null_ ; set &libname..RMain (where = (report = &report)) ; call symput("rtab", left(table)) ; call symput("rattr", repattr) ; call symput("rtitle", left(reptitle)) ; call symput("rtcomp", left(reporgan)) ; call symput("rtdate", left(repdate)) ; call symput("rtpage", left(repage)) ; call symput("rtend", left(repend)) ; run ; /* The following DATA step is intended to get from the location data set information about rtab table location. It creates the libdat macro variable that contains the name of the library where data set corresponding to rtab table is stored. */ data _null_ ; set &libname..Location ; where upcase(left(table)) = upcase("&rtab") ; call symput("libdat", trim(left(library))) ; run ; /* The following DATA step is intended to get, from the object data set, the name of the data set corresponding to rtab table and put it to rtab macro variable. */ data _null_ ; set &libname..Object ; where upcase(left(table)) = upcase("&rtab") ; call symput("rtab", trim(left(dataset))) ; run ; /* The following DATA step creates macro variables and fills them with data from rinfo data set: rtitle - is a series of macro variables containing titles for common use parameters. rpform - is a series of macro variables containing formats for common use parameters. rfromc - is a series of macro variables containing names of common use parameters. rpgcount - contains number of common use parameters. */ data _null_ ; set &libname..RInfo (where = (report = &report)) ; call symput("rtitle" || left(infono), left(subtitle)) ; if left(format) ^= "&mis" then call symput("rpform" || left(infono), left(format)) ; else call symput("rpform" || left(infono)," ") ; if trim(left(reparam)) ^= "&mis" then call symput("rfromc" || left(infono), left(reparam)) ; else call symput("rfromc" || left(infono), "0") ; call symput("rpgcount", left(infono)) ; run ; /* The following PROC CATALOG creates the _out_ data set containing names and descriptions of all user-defined formats. */ proc catalog c = &libname..formats ; contents out = _out_ (keep = name desc) ; run ; quit ; /* The following PROC CONTENTS creates the _rtab_ data set containing the names of rtab data set columns and information about their formats. */ proc contents data = &libdat..&rtab noprint out = _rtab_ (keep = name length type format formatl formatd nobs) ; run ; quit ; /* The following DATA step creates macro variables and fills them with data from _rtab_ data set: v - is a series of macro variables containing names of the columns of the rtab table. l - is a series of macro variables containing lengths of these columns. t - is a series of macro variables containing types of these columns. f - is a series of macro variables containing formats of these columns. fl - is a series of macro variables containing lengths of formatted presentation these columns. vars - contains number of columns in the rtab table. observ - contains number of rows in the rtab table. */ data _null_ ; set _rtab_ ; call symput("v" || left(_n_), left(upcase(name))); call symput("l" || left(_n_), left(length)); call symput("t" || left(_n_), left(type)); if trim(format) = "" then format = "&mis" ; else format = trim(format) || "." ; call symput("f" || left(_n_), left(format)); call symput("fl" || left(_n_), left(formatl)); call symput("vars", left(_n_)) ; call symput("observ", left(nobs)) ; run ; /* The following DATA step creates macro variables and fills them with data from the rvars data set: rcolnum - number of columns from the rtab data set that present in the report. _var - is a series of macro variables containing names of the columns that present in the report. colt - is a series of macro variables containing titles for the columns. numlst - contains list of numeric columns names. rrevvar - contains list of columns names (separated by "*") that must be cared specially for right-oriented report (for example, must be reversed before sorting). rsrtlst - contains list of columns names that the rtab data set must be sorted by. rhf - is a series of macro variables containing SAS statements for formatting output of each column. rtemplt - contains template string for the report table. rlablst - contains header for the report table. ful_lst - contains full header for the report table. rbrcount - contains number of classifiers (both table and row classifiers) specified for the report. rpbrlst - contains list of columns names specified as table classifiers. rrbrlst - contains list of columns names specified as row classifiers. rowbreak - contains number of row classifiers. */ data _null_ ; length tmp fulltmp $ 200 ; set &libname..RVars (where = (report = &report)) ; lngth = 0 ; retain nval count _rowb 0 ; call symput("rcolnum", left(order)) ; call symput("_var" || left(order), left(upcase(column))) ; call symput("colt" || left(order), left(coltitle)) ; %do i = 1 %to &vars ; if left(upcase(column)) = "&&v&i" and &&t&i = 1 then call symput("numlst", trim(symget("numlst")) || " " || left(column)) ; %end ; if index(upcase(colattr),"H") ^= 0 then call symput('rrevvar',trim(symget('rrevvar'))|| trim(left(column))||"*") ; if index(upcase(colattr), "S") ^= 0 or index(upcase(colattr), "T") ^= 0 or index(upcase(colattr), "R") ^= 0 then call symput("rsrtlst", trim(symget("rsrtlst")) || " " || left(column)) ; /* The following part of the DATA step calculates the width of each report column based on its format and title. */ tmp = coltitle ; len = length(coltitle) ; if left(format) = left("&mis") then do ; select(left(upcase(column))) ; %do i = 1 %to &vars ; when ("&&v&i") format = "&&f&i" ; %end ; otherwise ; end ; end ; if left(format) ^= left("&mis") then do ; i0 = indexc(format,"123456789") ; i1 = index(format, ".") ; if i0 > 0 and i1 > 0 then lngth = substr(format, i0, i1 - i0) ; else do ; lngth = 0 ; select(left(upcase(column))) ; %do i = 1 %to &vars ; when ("&&v&i") lngth = &&fl&i ; /*???? fl instead of l - check it*/ %end ; otherwise ; end ; if lngth = 0 then do j = 1 to nn ; set _out_ nobs = nn point = j; if upcase(trim(name) || ".") = left(upcase(format)) then do ; lngth = scan(desc, 3, ", "); j = nn ; end ; end ; end ; tmp = " " || trim(left(column)) || " " || trim(left(format)) ; call symput("rhf" || left(order), "put(" || trim(left(column)) || "," || trim(left(format)) || ")") ; end ; else do ; select(left(upcase(column))) ; %do i = 1 %to &vars ; when ("&&v&i") do ; call symput("rhf" || left(order), "left(" || trim(left(column)) || ")") ; lngth = &&l&i ; end ; %end ; otherwise ; end ; end ; lngth = max(len, lngth) ; /* The following part of the DATA step creates the report table template and header. */ if index(upcase(colattr), "T") = 0 then do ; %if &lang = L %then %do; tmp = trim(symget("rtemplt")) || " " || repeat(" ",lngth-1) || " |" ; %end ; %else %do ; tmp = "| " || repeat(" ",lngth-1) || " " || trim(symget("rtemplt")) ; %end ; call symput("rtemplt", trim(tmp)) ; end ; len = lngth - len ; if len > 0 then do ; %if &lang = R %then %do ; if index(upcase(colattr), "T") = 0 then tmp = "| " || repeat(" ", len-1) || trim(left(coltitle)) || " " || trim(symget("rlablst")) %str(;) fulltmp = "| " || repeat(" ", len-1) || trim(left(coltitle)) || " " || trim(symget("ful_lst")) %str(;) %end ; %else %do ; if index(upcase(colattr), "T") = 0 then tmp = symget("rlablst") || " " || trim(left(coltitle)) || repeat(" ", len-1) || " |" %str(;) fulltmp = symget("ful_lst") || " " || trim(left(coltitle)) || repeat(" ", len-1) || " |" %str(;) %end ; end ; else do ; %if &lang = L %then %do ; if index(upcase(colattr), "T") = 0 then tmp = trim(symget("rlablst")) || " " || trim(left(coltitle)) || " |" %str(;) fulltmp = trim(symget("ful_lst")) || " " || trim(left(coltitle)) || " |" %str(;) %end ; %else %do ; if index(upcase(colattr), "T") = 0 then tmp = "| " || trim(left(coltitle)) || " " || trim(symget("rlablst")) %str(;) fulltmp = "| " || trim(left(coltitle)) || " " || trim(symget("ful_lst")) %str(;) %end ; end ; if index(upcase(colattr), "T") = 0 then call symput("rlablst", trim(tmp)) ; call symput("ful_lst", trim(fulltmp)) ; /* The following part of the DATA step counts classification columns and creates lists of their names. */ if index(upcase(colattr), "T") > 0 then do ; count + 1 ; call symput("rbrcount", left(count)) ; call symput("rpbrlst", (trim(symget("rpbrlst")) || " " || trim(left(column)))) ; end ; if index(upcase(colattr), "R") > 0 then do ; count + 1 ; _rowb + 1 ; call symput("rbrcount", left(count)) ; call symput("rrbrlst", (trim(symget("rrbrlst")) || " " || left(column))) ; call symput("rowbreak", left(_rowb)) ; end ; run ; /* The following DATA step copies the rtab data set from its location to the WORK library. */ data &rtab ; set &libdat..&rtab ; run ; %if %length(&rsrtlst) ^= 0 %then %do ; %if &lang = R and %length(&rrevvar) > 0 %then %do ; /* The following DATA step reverses those columns of the rtab data set that need special care for right-to-left oriented reports. */ data &rtab ; set &rtab ; %let i = 1 ; %let tmp = %scan(&rrevvar, 1, *) ; %do %while(%length(&tmp) > 0) ; &tmp = reverse(trim(left(&tmp))) ; %let i = %eval(&i + 1) ; %let tmp = %scan(&rrevvar, %eval(&i), *) ; %end ; run ; %end ; /* The following PROC SORT sorts the rtab data set in required order. */ proc sort data = &rtab ; by &rsrtlst ; run ; %end ; /* The following PROC SORT and DATA step create the rstat data set containing merged information about classifiers and associates with them statistics. */ proc sort data = &libname..rvars (where = (report = &report)) out = rvars ; by column ; run ; data rstat ; merge rvars &libname..rstat (where = (report = &report)) ; by column ; run ; /* The following PROC SORT prepares the rstat data set for the next DATA step. */ proc sort data = rstat ; by order statno ; run ; /* The following DATA step creates macro variables and fills them with information from the rstat data set: slb - is a series of macro variables containing number of statistics for each classifier. slb - is a series of two-dimensional macro variables containing titles for each statistic. rso - is a series of two-dimensional macro variables containing the number of columns (analysis variables) to which each statistics should be applied. s - is a series of three-dimensional macro variables containing a reference of each analysis variable to the _var representing all report columns. r - is a series of three-dimensional macro variables containing SAS expression for statistics calculation. */ data _null_ ; length tmp $ 20 varname $ 8; retain count 0 ; set rstat (where = (report = &report)) ; by order statno ; if first.order then call symput("slb" || left(order),0) ; if trim(repstat) ^= "" then do ; if first.statno then do ; count = 0 ; call symput("slb" || trim(left(order)) || "_" || left(statno), trim(left(statitle))) ; call symput("slb" || left(order), left(statno)) ; call symput("rso" || trim(left(order)) || "_" || left(statno), 0) ; end ; count + 1 ; call symput("rso" || trim(left(order)) || "_" || left(statno), left(count)) ; select (upcase(analvar)) ; %do i = 1 %to &rcolnum ; when("&&_var&i") do ; tmp = "s" || trim(left(statno)) || "_" || left("&i") ; call symput("s" || trim(left(order)) || "_" || trim(left(statno)) || "_" || left(count), left(&i)) ; end ; %end ; otherwise ; end ; /* The following