Makro zum Abgleich zweier LIBRARIES

Aus BI-Snippets - Business Intelligence Code und Module
Wechseln zu: Navigation, Suche

Wer kennt das Problem nicht, aufgrund eines Changes (z.B. SAS Version, Release, etc.) möchte man die Datenbasis über zwei Bibliothek übergreifend abgleichen. Meiner Meinung nach ist der PROC COMPARE hierfür nur bedingt geeignet, da er zu viel Output liefert und dadurch unübersichtlich ist. Dies war der Grund für die Entwicklung des Macros COMPARE_LIB! Dabei arbeitet das Macro folgenden Programmablauf ab:

  1. Abgleich der Librarys auf Tabellenebene und Ausgabe eines HTML-Reports über vorhandene Abweichungen. Aufsetzend auf Tabellen welche in beiden Librarys vorhanden sind:
  2. Abgleich der Anzahl an DataSet Attributen und Observations (auch Compress-Option) und Ausgabe eines HTML-Reports über vorhandene Abweichungen. Aufsetzend auf Tabellen welche eine identische Anzahl an Attributen und Observations (und gesetztem Compress-Flag) vorweisen:
  3. Abgleich der DataSet Attribute (beschreibender Teil wie Formate, Label, Datentyp und Länge) und Ausgabe eines HTML-Reports über vorhandene Abweichungen. Aufsetzend auf Tabellen welche ausschließlich identische Attribute vorweisen:
  4. Abgleich der DataSets auf Observation-Ebene und Ausgabe eines HTML-Reports über vorhandene Abweichungen. Der Übersicht halber werden nur die ersten 10 abweichenden Observations ausgegeben.


<syntaxhighlight lang="SAS" line="1" > /********************* Standard Programm Header *****************************************

  • Programm  : Abgleich von Librarys
  • Beschreibung : Ziel des Programms ist es ein Abgleich von Library-Inhalten zu ermöglichen
  • Based on Doug Zirbel, Pinnacle Solutions Inc. Paper 88-27
  • Ersteller  : Christopher Gies | accantec consulting AG
  • Datum  : 08.05.15
  • Parameter  : BASELIB = Pfad SAS-Baselib
  • COMPLIB = Pfad SAS-Comparelib
  • SORTVARS = BY-Variablen für Sortierung des Output-Reports
  • EXCLUDEVARS = Variablen welche vom Compare ausgeschlossen werdne sollen
                                                          • ToDo ******************************************************
  • nothing
                                                          • Änderungen ************************************************
  • Version Datum Autor Beschreibung der Änderung
  • V0.1 08.05.15 ChG Initale Erstellung
                                                                                                                                                                                • /

OPTIONS SPOOL COMPRESS=YES /*MPRINT MLOGIC SYMLBOLGEN*/;

PROC FORMAT;

 VALUE $TEMP  "YES"=red
              " "=#E8E6DA;

RUN;

%MACRO COMPARE_LIB(BASELIB=,COMPLIB=,SORTVARS=,EXCLUDEVARS=,DECIMAL_PLACES=); /***********************************************************************

  • C O M P A R E _ L I B R A R Y _ C O N T E N T
  • MACRO PARMS:
  • BASELIB = LIBREF OF BASE SASLIB
  • COMPLIB = LIBREF OF COMPARE SASLIB
  • SORTVARS = BY-VARS FOR PRINTING OUTPUT REPORTS
  • EXCLUDEVARS = VARS WILL NOT REGARD FOR COMPARE
                                                                                                                                              • /

LIBNAME BASELIB "&BASELIB." ; LIBNAME COMPLIB "&COMPLIB." ;

/*************************************************************/ /*** DETERMINE AND MATCH DATASETS IN BASE AND COMP LIBRARY ***/ /*************************************************************/ PROC SQL;

 CREATE TABLE WORK._BASE_DS AS
   SELECT DISTINCT MEMNAME
   FROM SASHELP.VCOLUMN
   WHERE LIBNAME EQ "BASELIB" AND
         MEMTYPE EQ "DATA"
   ORDER BY MEMNAME;
 CREATE TABLE WORK._COMP_DS AS
   SELECT DISTINCT MEMNAME
   FROM SASHELP.VCOLUMN
   WHERE LIBNAME EQ "COMPLIB" AND
         MEMTYPE EQ "DATA"
   ORDER BY MEMNAME;
 CREATE TABLE WORK.DIFF_LIB_TABLES AS
   SELECT
     A.MEMNAME AS IN_BASE_LIB,
     B.MEMNAME AS IN_COMP_LIB
   FROM WORK._BASE_DS AS A FULL JOIN WORK._COMP_DS AS B
     ON A.MEMNAME = B.MEMNAME
   WHERE A.MEMNAME = "" OR B.MEMNAME = "";
 CREATE TABLE WORK._DS2CHECK_ATTRIBUTES AS
   SELECT MEMNAME
   FROM WORK._BASE_DS
   INTERSECT
   SELECT MEMNAME
   FROM WORK._COMP_DS;

QUIT;

/*************************************************************/ /*** IF THERE ARE ANY OBS IN THIS DATASET, PRINT THEM! ***/ /*************************************************************/ PROC PRINT DATA=WORK.DIFF_LIB_TABLES; TITLE1 "************************************************************"; TITLE2 "*** DELTA LIBRARY TABLES ***"; TITLE3 "************************************************************"; RUN;

/************************************************************/ /*** DETERMINE AND MATCH DATASET ATTRIBUTES ***/ /************************************************************/ PROC SQL NOPRINT;

 SELECT  DISTINCT(MEMNAME), COUNT(DISTINCT(MEMNAME))
         INTO  :DS_LST SEPARATED BY '~',
               :DS_CNT
         FROM WORK._DS2CHECK_ATTRIBUTES
         ORDER BY MEMNAME;

QUIT;

PROC DATASETS LIB=WORK NOLIST NODETAILS NOWARN;

 DELETE _BASE_ATTRIB;

RUN; %DO I=1 %TO &DS_CNT;

 %LET DS_NM = %SCAN(&DS_LST., &I., ~);
 PROC CONTENTS DATA=BASELIB.&DS_NM. OUT=WORK._BASE_ATTRIB_TMP NOPRINT;
 PROC APPEND BASE=WORK._BASE_ATTRIB DATA=WORK._BASE_ATTRIB_TMP FORCE;
 RUN;

%END; PROC DATASETS LIB=WORK NOLIST NODETAILS NOWARN;

 DELETE _BASE_ATTRIB_TMP;

RUN;

PROC DATASETS LIB=WORK NOLIST NODETAILS NOWARN;

 DELETE _COMP_ATTRIB;

RUN; %DO I=1 %TO &DS_CNT;

 %LET DS_NM = %SCAN(&DS_LST., &I., ~);
 PROC CONTENTS DATA=COMPLIB.&DS_NM. OUT=WORK._COMP_ATTRIB_TMP NOPRINT;
 PROC APPEND BASE=WORK._COMP_ATTRIB DATA=WORK._COMP_ATTRIB_TMP FORCE;
 RUN;

%END; PROC DATASETS LIB=WORK NOLIST NODETAILS NOWARN;

 DELETE _COMP_ATTRIB_TMP;

RUN;

PROC SQL NOPRINT;

 CREATE TABLE WORK._BASE_NO_OBS_VAR AS 
   SELECT MEMNAME, NOBS, COUNT(MEMNAME) AS NVAR, COMPRESS
   FROM WORK._BASE_ATTRIB
   GROUP BY MEMNAME, NOBS, COMPRESS
   ORDER BY MEMNAME;
 CREATE TABLE WORK._COMP_NO_OBS_VAR AS 
   SELECT MEMNAME, NOBS, COUNT(MEMNAME) AS NVAR, COMPRESS
   FROM WORK._COMP_ATTRIB
   GROUP BY MEMNAME, NOBS, COMPRESS
   ORDER BY MEMNAME;
 
 CREATE TABLE WORK.DIFF_ANZ_OBS_VAR AS
   SELECT 
     A.MEMNAME, 
     A.NOBS AS BASE_NOBS, 
     B.NOBS AS COMP_NOBS, 
     CASE WHEN A.NOBS NE B.NOBS THEN 'YES' END AS DIFF_NOBS,
     A.NVAR AS BASE_NVAR, 
     B.NVAR AS COMP_NVAR,
     CASE WHEN A.NVAR NE B.NVAR THEN 'YES' END AS DIFF_NVAR,
     A.COMPRESS AS BASE_COMPRESS, 
     B.COMPRESS AS COMP_COMPRESS,
     CASE WHEN A.COMPRESS NE B.COMPRESS THEN 'YES' END AS DIFF_COMPRESS
   FROM WORK._BASE_NO_OBS_VAR AS A FULL JOIN WORK._COMP_NO_OBS_VAR AS B 
     ON A.MEMNAME = B.MEMNAME
   WHERE 
     CALCULATED DIFF_NOBS='YES' OR 
     CALCULATED DIFF_NVAR='YES' OR
     CALCULATED DIFF_COMPRESS='YES';
 CREATE TABLE WORK.DIFF_VAR_ATTRIB AS
   SELECT 
     A.MEMNAME,
     A.NAME, 
     A.TYPE AS BASE_TYPE, 
     B.TYPE AS COMP_TYPE, 
     CASE WHEN A.TYPE NE B.TYPE THEN 'YES' END AS DIFF_TYPE,
     A.LENGTH AS BASE_LENGTH, 
     B.LENGTH AS COMP_LENGTH,
     CASE WHEN A.LENGTH NE B.LENGTH THEN 'YES' END AS DIFF_LENGTH,
     A.LABEL AS BASE_LABEL, 
     B.LABEL AS COMP_LABEL,
     CASE WHEN A.LABEL NE B.LABEL THEN 'YES' END AS DIFF_LABEL,
     A.FORMAT AS BASE_FORMAT, 
     B.FORMAT AS COMP_FORMAT,
     CASE WHEN A.FORMAT NE B.FORMAT THEN 'YES' END AS DIFF_FORMAT
   FROM WORK._BASE_ATTRIB AS A FULL JOIN WORK._COMP_ATTRIB AS B 
     ON A.MEMNAME = B.MEMNAME AND A.NAME = B.NAME
   WHERE 
     CALCULATED DIFF_TYPE='YES' OR 
     CALCULATED DIFF_LENGTH='YES' OR 
     CALCULATED DIFF_LABEL='YES' OR 
     CALCULATED DIFF_FORMAT='YES';

QUIT;

/*************************************************************/ /*** IF THERE ARE ANY OBS IN THIS DATASET, PRINT THEM! ***/ /*************************************************************/ PROC PRINT DATA=WORK.DIFF_ANZ_OBS_VAR; TITLE1 "************************************************************"; TITLE2 "*** DELTA DATASET ATTRIBUTES ***"; TITLE3 "************************************************************";

 VAR MEMNAME BASE_NOBS COMP_NOBS;
 VAR DIFF_NOBS / STYLE={BACKGROUND=$TEMP.};
 VAR BASE_NVAR COMP_NVAR;
 VAR DIFF_NVAR / STYLE={BACKGROUND=$TEMP.};
 VAR BASE_COMPRESS COMP_COMPRESS;
 VAR DIFF_COMPRESS / STYLE={BACKGROUND=$TEMP.};

RUN;

/*************************************************************/ /*** IF THERE ARE ANY OBS IN THIS DATASET, PRINT THEM! ***/ /*************************************************************/ PROC PRINT DATA=WORK.DIFF_VAR_ATTRIB; TITLE1 "************************************************************"; TITLE2 "*** DELTA VARIABLES ATTRIBUTES ***"; TITLE3 "************************************************************";

 VAR MEMNAME NAME BASE_TYPE COMP_TYPE;
 VAR DIFF_TYPE / STYLE={BACKGROUND=$TEMP.};
 VAR BASE_LENGTH COMP_LENGTH;
 VAR DIFF_LENGTH / STYLE={BACKGROUND=$TEMP.};
 VAR BASE_LABEL COMP_LABEL;
 VAR DIFF_LABEL / STYLE={BACKGROUND=$TEMP.};
 VAR BASE_FORMAT COMP_FORMAT;
 VAR DIFF_FORMAT / STYLE={BACKGROUND=$TEMP.};

RUN;

/************************************************************/ /*** COMPARE OBSERVATIONS ***/ /************************************************************/ PROC SQL NOPRINT;

 /* DETERMINE DATASETS WITH EQUAL STRUCTURE AND NUMBER OF OBS */
 CREATE TABLE WORK._DS2CHECK_CONTENT AS
   SELECT 
     A.MEMNAME, 
     B.NAME
   FROM
     (SELECT MEMNAME
     FROM WORK._DS2CHECK_ATTRIBUTES
     EXCEPT
     (SELECT MEMNAME
     FROM WORK.DIFF_ANZ_OBS_VAR
     UNION
     SELECT DISTINCT MEMNAME
     FROM WORK.DIFF_VAR_ATTRIB)) AS A INNER JOIN WORK._BASE_ATTRIB AS B
     ON A.MEMNAME = B.MEMNAME
   ORDER BY MEMNAME;
 /* SAVE ALL DATASET-NAMES SEPERATED BY ~ AND NUMBER OF DATASETS BY MACROVAR */
 SELECT  DISTINCT(MEMNAME), COUNT(DISTINCT(MEMNAME))
         INTO  :DS_LST SEPARATED BY '~',
               :DS_CNT
         FROM WORK._DS2CHECK_CONTENT
         ORDER BY MEMNAME ASC;

QUIT;

%DO J=1 %TO &DS_CNT;

 %LET DS_NM = %SCAN(&DS_LST., &J., ~);
   %COMPARE_TABLE_CONTENT(LIB1=BASELIB,LIB2=COMPLIB,FILE=&DS_NM.,SORTVARS=&SORTVARS.,EXCLUDEVARS=&EXCLUDEVARS.,DECIMAL_PLACES=&DECIMAL_PLACES.);

%END; %MEND COMPARE_LIB;

%MACRO COMPARE_TABLE_CONTENT(LIB1=,LIB2=,FILE=,SORTVARS=,EXCLUDEVARS=,DECIMAL_PLACES=); /***********************************************************************

  • C O M P A R E _ T A B L E _ C O N T E N T
  • MACRO PARMS:
  • LIB1 = LIBNAME OF BASE SASLIB
  • LIB2 = LIBNAME OF COMPARE SASLIB
  • FILE = FILENAME OF SAS FILE THATS IN BOTH SASLIBS
  • SORTVARS = BY-VARS FOR PRINTING OUTPUT REPORTS
  • EXCLUDEVARS = VARS WILL NOT REGARD FOR COMPARE
                                                                                                                                              • /

%PUT "== STARTING COMPARE_TABLE_CONTENT MACRO EXECUTION FOR TABLE &FILE.";

/***********************************************************************

  • D O----L I B 1----F I L E
                                                                                                                                              • /

%LET DSID=%SYSFUNC(OPEN(&LIB1..&FILE)); /*USE THE SCL OPEN FUNCTION*/ %LET CNT=%SYSFUNC(ATTRN(&DSID.,NVARS)); /*SCL ATTRN FUNC FOR # OF VARS*/

/* Determine list of excluding vars */ %LET EXCLUDEVARS_TMP=&EXCLUDEVARS.; %LET EXCLUDEVARS1=;

%LET M=1; %DO %WHILE(%LENGTH(%SCAN(&EXCLUDEVARS_TMP.,&M.)));

 %LET CURRENT_VAR=%SCAN(&EXCLUDEVARS_TMP.,&M.);
 %IF %SYSFUNC(VARNUM(&DSID.,&CURRENT_VAR.)) %THEN %DO;
   %LET EXCLUDEVARS1=&EXCLUDEVARS1. &CURRENT_VAR.;
 %END;
 %LET M=%EVAL(&M.+1); 

%END;

/***********************************************************************

  • CREATE A DIFF MACRO VAR FOR EACH VAR IN DATASET. THE SCL VARNAME
  • FUNCTION GETS THIS FROM THE FILE IDENTIFIED BY THE SCL DATASET ID
                                                                                                                                              • /

%DO I = 1 %TO &CNT.;

 %LET VARNAME&I.=%SYSFUNC(VARNAME(&DSID.,&I.));
 %LET VARTYPE&I.=%SYSFUNC(VARTYPE(&DSID.,&I.));
 %LET VARFMT&I.=%SYSFUNC(VARFMT(&DSID.,&I.));
 %LET VARINFMT&I.=%SYSFUNC(VARINFMT(&DSID.,&I.));
 %LET FMTLEN&I.=%SYSFUNC(PRXCHANGE(s/(\D*)(\d+)\.\d*/$2/,1,&&VARFMT&I.));
 %LET FMTBEZ&I.=%SYSFUNC(PRXCHANGE(s/(\D*)(\d+)\.\d*/$1/,1,&&VARFMT&I.));

 %PUT |VARNAME&I.=&&VARNAME&I.|;
 %PUT |VARTYPE&I.=&&VARTYPE&I.|;
 %PUT |VARFMT&I.=&&VARFMT&I.|;
 %PUT |VARINFMT&I.=&&VARINFMT&I.|;
 %PUT |FMTLEN&I.=&&FMTLEN&I.|;
 %PUT |FMTBEZ&I.=&&FMTBEZ&I.|;
 %PUT ***;

%END; %LET RC=%SYSFUNC(CLOSE(&DSID.)); /* CLOSE DS USING SCL FUNCTION */

/***********************************************************************

  • SORT ON ALL VARS (_ALL_ DOES THIS WITHOUT HAVING TO NAME EACH ONE)
                                                                                                                                              • /

PROC SORT DATA=&LIB1..&FILE OUT=WORK.&FILE._BASE (LABEL="&LIB1..&FILE");

 BY _ALL_;

RUN;

/***********************************************************************

  • FIND ANY DUPS BY LOOKING AT THE FINAL (RIGHTMOST) VARIABLE`S BY-GROUP.
  • IF IT`S BOTH THE FIRST AND LAST VALUE OF THAT BY-GROUP, THEN IT`S
  • UNIQUE, OTHERWISE IT IS A DUPLICATE
                                                                                                                                              • /

%IF "&DECIMAL_PLACES." EQ "" %THEN %DO;

 DATA  WORK.&FILE._BAOK
       WORK.&FILE._BADU;
   SET WORK.&FILE._BASE;
   BY _ALL_;
   IF FIRST.&&VARNAME&CNT AND LAST.&&VARNAME&CNT THEN DO;
     OUTPUT WORK.&FILE._BAOK;
   END;
   ELSE DO;
     OUTPUT WORK.&FILE._BADU;
   END;
 RUN;

%END; %ELSE %DO;

 DATA  WORK.&FILE._BAOK
       WORK.&FILE._BADU
       WORK.&FILE._BASE;
   SET WORK.&FILE._BASE;
   BY _ALL_;

/*** --> ROUND ALL NUMERIC VARS ON DETERMINED NUMBER OF DECIMAL PLACES ***/

   %DO I = 1 %TO &CNT.;
     %IF &&VARTYPE&I.=N AND (%SYSFUNC(PRXMATCH(/(TIME|DATE|DATETIME)/i,&&VARFMT&I.)) = 0) %THEN %DO;
       %IF %STR(&&FMTBEZ&I.)=%STR() %THEN %DO; /* Query for FMTBEZ is necessary because CATS in combination with SYSFUNC don't work if FMTBEZ is empty */
         %IF %STR(&DECIMAL_PLACES.)=%STR(0) %THEN %DO;
           &&VARNAME&I.=INPUT(PUT(&&VARNAME&I.,%SYSFUNC(CATS(&&FMTLEN&I.,.))),&&VARINFMT&I.);
         %END;
         %ELSE %DO;
           &&VARNAME&I.=INPUT(PUT(&&VARNAME&I.,%SYSFUNC(CATS(&&FMTLEN&I.,.,&DECIMAL_PLACES.))),&&VARINFMT&I.);
         %END;
       %END;
       %ELSE %DO;
         %IF %STR(&DECIMAL_PLACES.)=%STR(0) %THEN %DO;
           &&VARNAME&I.=INPUT(PUT(&&VARNAME&I.,%SYSFUNC(CATS(&&FMTBEZ&I.,&&FMTLEN&I.,.))),&&VARINFMT&I.);
         %END;
         %ELSE %DO;
           &&VARNAME&I.=INPUT(PUT(&&VARNAME&I.,%SYSFUNC(CATS(&&FMTBEZ&I.,&&FMTLEN&I.,.,&DECIMAL_PLACES.))),&&VARINFMT&I.);
         %END;
       %END;
     %END;
   %END;

/*** <-- ROUND ALL NUMERIC VARS ON DETERMINED NUMBER OF DECIMAL PLACES ***/

   IF FIRST.&&VARNAME&CNT AND LAST.&&VARNAME&CNT THEN DO;
     OUTPUT WORK.&FILE._BAOK;
   END;
   ELSE DO;
     OUTPUT WORK.&FILE._BADU;
   END;
   OUTPUT WORK.&FILE._BASE;
 RUN;

%END;

/***********************************************************************

  • PRINT DUPS IF THEY EXIST
                                                                                                                                              • /

TITLE "*** &LIB1..&FILE DUPS ***"; PROC PRINT DATA=WORK.&FILE._BADU (obs=10); RUN;

/***********************************************************************

  • N O W----D O----L I B 2----F I L E
                                                                                                                                              • /

%LET DSID=%SYSFUNC(OPEN(&LIB2..&FILE.)); %LET CNT=%SYSFUNC(ATTRN(&DSID.,NVARS));

%LET EXCLUDEVARS_TMP=&EXCLUDEVARS.; %LET EXCLUDEVARS2=;

%LET M=1; %DO %WHILE(%LENGTH(%SCAN(&EXCLUDEVARS_TMP.,&M.)));

 %LET CURRENT_VAR=%SCAN(&EXCLUDEVARS_TMP.,&M.);
 %IF %SYSFUNC(VARNUM(&DSID.,&CURRENT_VAR.)) %THEN %DO;
   %LET EXCLUDEVARS2=&EXCLUDEVARS2. &CURRENT_VAR.;
 %END;
 %LET M=%EVAL(&M.+1); 

%END;

/**********************************************************************

  • CREATE A DIFF MACRO VAR FOR EACH VAR IN DATASET
                                                                                                                                              • /

%DO I = 1 %TO &CNT.;

 %LET VARNAME&I.=%SYSFUNC(VARNAME(&DSID.,&I.));
 %LET VARTYPE&I.=%SYSFUNC(VARTYPE(&DSID.,&I.));
 %LET VARFMT&I.=%SYSFUNC(VARFMT(&DSID.,&I.));
 %LET VARINFMT&I.=%SYSFUNC(VARINFMT(&DSID.,&I.));
 %LET FMTLEN&I.=%SYSFUNC(PRXCHANGE(s/(\D*)(\d+)\.\d*/$2/,1,&&VARFMT&I.));
 %LET FMTBEZ&I.=%SYSFUNC(PRXCHANGE(s/(\D*)(\d+)\.\d*/$1/,1,&&VARFMT&I.));

 %PUT |VARNAME&I.=&&VARNAME&I.|;
 %PUT |VARTYPE&I.=&&VARTYPE&I.|;
 %PUT |VARFMT&I.=&&VARFMT&I.|;
 %PUT |VARINFMT&I.=&&VARINFMT&I.|;
 %PUT |FMTLEN&I.=&&FMTLEN&I.|;
 %PUT |FMTBEZ&I.=&&FMTBEZ&I.|;
 %PUT ***;

%END; %LET RC=%SYSFUNC(CLOSE(&DSID.)); /* CLOSE DS USING SCL FUNCTION */

/**********************************************************************

  • SORT ON ALL VARS (_ALL_ DOES THIS WITHOUT HAVING TO NAME EACH ONE)
                                                                                                                                              • /

PROC SORT DATA=&LIB2..&FILE OUT=WORK.&FILE._COMP (LABEL="&LIB2..&FILE");

 BY _ALL_;

RUN;

/**********************************************************************

  • FIND ANY DUPS BY LOOKING AT THE FINAL (RIGHTMOST) VARIABLE`S BY-GROUP.
  • IF IT`S BOTH THE FIRST AND LAST VALUE OF THAT BY-GROUP, THEN IT`S
  • UNIQUE, OTHERWISE IT IS A DUPLICATE
                                                                                                                                              • /

%IF "&DECIMAL_PLACES." EQ "" %THEN %DO;

 DATA  WORK.&FILE._COOK
       WORK.&FILE._CODU;
   SET WORK.&FILE._COMP;
   BY _ALL_;
   IF FIRST.&&VARNAME&CNT AND LAST.&&VARNAME&CNT THEN DO;
     OUTPUT WORK.&FILE._COOK;
   END;
   ELSE DO;
     OUTPUT WORK.&FILE._CODU;
   END;
 RUN;

%END; %ELSE %DO;

 DATA  WORK.&FILE._COOK
       WORK.&FILE._CODU
       WORK.&FILE._COMP;
   SET WORK.&FILE._COMP;
   BY _ALL_;

/*** --> ROUND ALL NUMERIC VARS ON DETERMINED NUMBER OF DECIMAL PLACES ***/

   %DO I = 1 %TO &CNT.;
     %IF &&VARTYPE&I.=N AND (%SYSFUNC(PRXMATCH(/(TIME|DATE|DATETIME)/i,&&VARFMT&I.)) = 0) %THEN %DO;
       %IF %STR(&&FMTBEZ&I.)=%STR() %THEN %DO; /* Query for FMTBEZ is necessary because CATS in combination with SYSFUNC don't work if FMTBEZ is empty */
         %IF %STR(&DECIMAL_PLACES.)=%STR(0) %THEN %DO;
           &&VARNAME&I.=INPUT(PUT(&&VARNAME&I.,%SYSFUNC(CATS(&&FMTLEN&I.,.))),&&VARINFMT&I.);
         %END;
         %ELSE %DO;
           &&VARNAME&I.=INPUT(PUT(&&VARNAME&I.,%SYSFUNC(CATS(&&FMTLEN&I.,.,&DECIMAL_PLACES.))),&&VARINFMT&I.);
         %END;
       %END;
       %ELSE %DO;
         %IF %STR(&DECIMAL_PLACES.)=%STR(0) %THEN %DO;
           &&VARNAME&I.=INPUT(PUT(&&VARNAME&I.,%SYSFUNC(CATS(&&FMTBEZ&I.,&&FMTLEN&I.,.))),&&VARINFMT&I.);
         %END;
         %ELSE %DO;
           &&VARNAME&I.=INPUT(PUT(&&VARNAME&I.,%SYSFUNC(CATS(&&FMTBEZ&I.,&&FMTLEN&I.,.,&DECIMAL_PLACES.))),&&VARINFMT&I.);
         %END;
       %END;
     %END;
   %END;

/*** <-- ROUND ALL NUMERIC VARS ON DETERMINED NUMBER OF DECIMAL PLACES ***/

   IF FIRST.&&VARNAME&CNT AND LAST.&&VARNAME&CNT THEN DO;
     OUTPUT WORK.&FILE._COOK;
   END;
   ELSE DO;
     OUTPUT WORK.&FILE._CODU;
   END;
   OUTPUT WORK.&FILE._COMP;
 RUN;

%END;

/**********************************************************************

  • PRINT DUPS IF THEY EXIST
                                                                                                                                              • /

TITLE "*** &LIB2..&FILE. DUPS ***"; PROC PRINT DATA=WORK.&FILE._CODU (obs=10); RUN;

/**********************************************************************

  • IDENTIFY DIFFERENCES BETWEEN THE 2 SORTED INPUT FILES
                                                                                                                                              • /

DATA WORK.&FILE._ANB

    WORK.&FILE._BNA;
 MERGE WORK.&FILE._BASE (IN=A %IF &EXCLUDEVARS1. GT %THEN %DO; DROP=&EXCLUDEVARS1. %END;)
       WORK.&FILE._COMP (IN=B %IF &EXCLUDEVARS2. GT %THEN %DO; DROP=&EXCLUDEVARS2. %END;);
 BY _ALL_;
 IF (A AND NOT B) THEN DO;
   OUTPUT WORK.&FILE._ANB;
 END;
 ELSE IF (B AND NOT A) THEN DO;
   OUTPUT WORK.&FILE._BNA;
 END;

RUN;

/**********************************************************************

  • IF A SORTVAR PARM WAS ENTERED IN ORDER TO PRINT IN AN ORDER THAT YOU
  • CHOOSE (RATHER THAN THE INTERNAL SAS VARIABLE ORDER) -- THEN SORT
  • BEFORE PRINTING. YES, THE OUTPUT FILE HAS THE SAME NAME AS THE INPUT
                                                                                                                                              • /

%IF &SORTVARS GT %THEN %DO; PROC SORT DATA=WORK.&FILE._ANB;

 BY &SORTVARS;

RUN; %END;

/**********************************************************************

  • IF THERE ARE ANY OBS IN THIS DATASET, PRINT THEM
                                                                                                                                              • /

PROC PRINT DATA=WORK.&FILE._ANB (obs=10); TITLE1 "************************************************************"; TITLE2 "*** RECS IN &LIB1..&FILE BUT NOT IN &LIB2..&FILE ***"; TITLE3 "************************************************************"; RUN;

/***********************************************************************

  • IF A SORTVAR PARM WAS ENTERED, THEN SORT BEFORE PRINTING
                                                                                                                                              • /

%IF &SORTVARS GT %THEN %DO; PROC SORT DATA=WORK.&FILE._BNA;

 BY &SORTVARS;

RUN; %END;

/***********************************************************************

  • IF THERE ARE ANY OBS IN THIS DATASET, PRINT THEM
                                                                                                                                              • /

PROC PRINT DATA=WORK.&FILE._BNA (obs=10); TITLE1 "************************************************************"; TITLE2 "*** RECS IN &LIB2..&FILE BUT NOT IN &LIB1..&FILE ***"; TITLE3 "************************************************************"; RUN;

%PUT "==== NOW ENDING COMPARE_TABLE_CONTENT MACRO EXECUTION FOR TABLE &FILE."; %MEND COMPARE_TABLE_CONTENT;

%*COMPARE_TABLE_CONTENT(LIB1=BASELIB,LIB2=COMPLIB,FILE=,SORTVARS=,EXCLUDEVARS=,DECIMAL_PLACES=2);

%COMPARE_LIB(BASELIB=,COMPLIB=,SORTVARS=,EXCLUDEVARS=,DECIMAL_PLACES=); </syntaxhighlight>