%MACRO EZCOMPAR(LIB1=, LIB2=, DROP1=, DROP2=, FILE=, SORTVARS=); ***********************************************************************; * E Z C O M P A R ; * MACRO PARMS: ; * LIB1 = LIBNAME OF A SASLIB ; * LIB2 = LIBNAME OF ANOTHER SASLIB; ; * FILE = FILENAME OF SAS FILE THATS IN BOTH SASLIBS ; * SORTVARS = BY-VARS FOR PRINTING OUTPUT REPORTS ; * ; ***********************************************************************; %PUT '== STARTING EZCOMPAR MACRO EXECUTION'; ***********************************************************************; * ; * 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*/ ***********************************************************************; * 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 X&I=%SYSFUNC(VARNAME(&DSID,&I)); %END; %LET RC=%SYSFUNC(CLOSE(&DSID)); /* CLOSE DS */ ***********************************************************************; * SORT ON ALL VARS (_ALL_ DOES THIS WITHOUT HAVING TO NAME EACH ONE) ***********************************************************************; PROC SORT DATA=&LIB1..&FILE OUT=WORK.LIB1FILE(LABEL="&LIB1..&FILE", DROP = &DROP1); BY _ALL_; * BY &SORTVARS; 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; ***********************************************************************; DATA WORK.OKRECS WORK.DUPS; SET WORK.LIB1FILE; BY _ALL_; * BY &SORTVARS; IF FIRST.&&X&CNT AND LAST.&&X&CNT THEN OUTPUT WORK.OKRECS; ELSE OUTPUT WORK.DUPS; RUN; ***********************************************************************; * PRINT DUPS IF THEY EXIST; ***********************************************************************; TITLE "*** &LIB1..&FILE DUPS ***"; PROC PRINT DATA=WORK.DUPS; RUN; ***********************************************************************; * ; * N O W----D O----L I B 2----F I L E ; * ; ***********************************************************************; %LET DSID=%SYSFUNC(OPEN(&LIB2..&FILE)); /* OPEN DS */ %LET CNT=%SYSFUNC(ATTRN(&DSID,NVARS)); /* # OF VARS IN THE DATASET */ ***********************************************************************; * CREATE A DIFF MACRO VAR FOR EACH VAR IN DATASET ; ***********************************************************************; %DO I = 1 %TO &CNT; %LET X&I=%SYSFUNC(VARNAME(&DSID,&I)); %END; %LET RC=%SYSFUNC(CLOSE(&DSID)); /* CLOSE DS USING SCL FUNCTION */ ***********************************************************************; * SORT ON ALL VARS ; ***********************************************************************; PROC SORT DATA=&LIB2..&FILE OUT=WORK.LIB2FILE(LABEL="&LIB2..&FILE" DROP=&DROP2); BY _ALL_; * BY &SORTVARS; RUN; ***********************************************************************; * FIND ANY DUPS ; ***********************************************************************; DATA WORK.OKRECS WORK.DUPS; SET WORK.LIB2FILE; BY _ALL_; * BY &SORTVARS; IF FIRST.&&X&CNT AND LAST.&&X&CNT THEN OUTPUT WORK.OKRECS; ELSE OUTPUT WORK.DUPS; RUN; ***********************************************************************; * PRINT DUPS ; ***********************************************************************; TITLE "*** &LIB2..&FILE DUPS ***"; PROC PRINT DATA=WORK.DUPS; RUN; ***********************************************************************; * IDENTIFY DIFFERENCES BETWEEN THE 2 SORTED INPUT FILES; ***********************************************************************; proc sort data=LIB1FILE; BY &SORTVARS; run; proc sort data=LIB2FILE; BY &SORTVARS; run; DATA WORK.A_NOT_B WORK.B_NOT_A; MERGE WORK.LIB1FILE(IN=A) WORK.LIB2FILE(IN=B); *BY _ALL_; BY &SORTVARS; IF (A AND NOT B) THEN OUTPUT A_NOT_B; ELSE IF (B AND NOT A) THEN OUTPUT B_NOT_A; 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 INUPT; ***********************************************************************; %IF &SORTVARS GT %THEN %DO; PROC SORT DATA=WORK.A_NOT_B; BY &SORTVARS; RUN; %END; ***********************************************************************; * IF THERE ARE ANY OBS IN THIS DATASET, PRINT THEM ; ***********************************************************************; PROC PRINT DATA=WORK.A_NOT_B; 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.B_NOT_A; BY &SORTVARS; RUN; %END; ***********************************************************************; * IF THERE ARE ANY OBS IN THIS DATASET, PRINT THEM ; ***********************************************************************; PROC PRINT DATA=WORK.B_NOT_A; TITLE1 "************************************************************"; TITLE2 "*** RECS IN &LIB2..&FILE BUT NOT IN &LIB1..&FILE"; TITLE3 "************************************************************"; RUN; TITLE1 "************************************************************"; TITLE2 "*** PROC CONTENTS OF BOTH DATASETS"; TITLE3 "************************************************************"; PROC CONTENTS DATA=&LIB1..&FILE POSITION; PROC CONTENTS DATA=&LIB2..&FILE POSITION; %PUT '==== NOW ENDING EZCOMPAR MACRO EXECUTION'; %MEND EZCOMPAR;