topic Re: help with error in macro compare_all in SAS Programming

help with error in macro compare_all

lydiawawa — Tue, 15 Mar 2022 21:44:31 GMT

Hi,

I'm working with a macro downloaded from this site: Data Library Comparison Macro %COMPARE_ALL - SAS Support Communities

It is giving an error that I do not know how to fix .

The Macro parameters I used to initiate the macro:

%compare_all(base = vdata,
compare = orig,
outdoc = /data/output/compare.xlsx);

Error:

Full macro:

/*------------------------------------------------------------------*
| MACRO NAME  : compare_all
| SHORT DESC  : Creates summary comparison report of two libraries 
|               of data in Excel.
*------------------------------------------------------------------*
| CREATED BY  : Meyers, Jeffrey                 (01/20/2013 3:00)
*------------------------------------------------------------------*
| VERSION UPDATES:
| 1.4 - 03/02/2020
|    Corrected issue with grabbing number of changed values in dataset
        summary table.
| 1.3 - 02/25/2020
|    Changed how IDSUMTABLE works.  Will now work on any number >=0.
        Will produce a summary of data changes including up to
        IDSUMTABLE number of ID variables.
|    Documentation updated.
| 1.2 - 02/24/2020
|    Fixed an issue where labels were being mismatched in variable
        summary sheets.
| 1.1 - 02/18/2020
|    Made updates for when a dataset does not share any non-ID variables
        between base and compare libraries
|    Added DEBUG option
| 1.0 - 03/15/2019
|    Initial Release
*------------------------------------------------------------------*
| PURPOSE
|
| This macro compares the datasets of two libraries together to check
| for changes in variables, datasets, and values in datasets the two
| libraries share.  The macro outputs a report of three levels into
| an Excel file.
| The first report lists all the datasets of both libraries side by side
| to compare the number of variables, observations, last updated date,
| and a count of potential new observations, lost observations, changed
| variable values, and variable attribute changes.
|
| The second level of the report is a worksheet for each datasets that is
| shared between the two libraries.  The report lists every variable contained
| by either library's dataset and compares some of the basic attributes
| (label, format, length, and type), and has a count of how many data changes
| exist from the base data to the new data.
|
| The third level of the report summarizes the changes across all variables
| potentially within levels of the ID variables.  The summary allows the user
| to quickly see what the values of the variable changed to.
|
| The fourth level of the report is for any variable that has changed values
| between the libraries.  Each changed value is listed with the base value
| and the compare value along with the ID variables.  For numeric values
| a percent and absolute change are provided. 
|
| 1.0: REQUIRED PARAMETERS
| BASE = The LIBNAME of the library containing the base data to compare with the updated data.
|        NOTE: This is the libname and not a file path.  A library must be
|              pre-specified.
| COMPARE = The LIBNAME of the library containing the updated data to compare with the base data.
|        NOTE: This is the libname and not a file path.  A library must be
|              pre-specified.
| OUTDOC = The full file path and file name of an Microsoft Excel file to output the report to.
|          Filename should include the .xlsx tag.
| 2.0: Optional PARAMETERS
| CROSSTAB_THRESHOLD = Determines how many unique combinations can exist when doing a
|                      comparison of changed values and still display them all in a 
|                      cross tab summary in the level 3 summary table.  Default is 15.
|                      minimum is 1.
| SELECT = A space delimited list of dataset names to focus the macro on comparing. The
|          case of the names does not matter, and no quotations should be included.  All
|          other datasets within the libraries will be ignored.
| ID = One or more variables can be designated ID variables that will be used to compare
|      between the same datasets (example: patient ID).  Due to different datasets having
|      different structures this parameter is flexible.  Any number of ID variables can be
|      specified, but only those that are contained in a specific dataset will be used in the
|      comparison for that dataset.  ID variables are listed in the order of the list from left
|      to right.
|      Example: ID = ARM DCNTR_ID CYCLE
|        Dataset BASELINE contains ARM and DCNTR_ID, so those are used as ID variables to compare BASELINE
|            in the order of ARM -> DCNTR_ID
|        Dataset TREATMENT contains ARM, DCNTR_ID, and CYCLE, so all three are used as ID variables to compare
|            TREATMENT in the order of ARM -> DCNTR_ID -> CYCLE
|        Dataset PROTOCOL contains none of the three ID variables, so none are used to compare PROTOCOL
| IDSUMTABLE = Determines how many ID variables are used to summarize the data changes for each dataset.
|              Example: if IDSUMTABLE=2 and a dataset has two ID variables then the data changes will be
|              summarized by both ID variables.  If IDSUMTABLE=2 and a dataset only has 1 ID variable then
|              it will use that one ID variable to summarize.  If IDSUMTABLE=2 and a dataset has 3 ID variables
|              then it will only use the first two ID variables in the ID list.
|              Example 2: If IDSUMTABLE=0 then it will not use any ID variables to summarize and will summarize
|              instead across all observations.
|              Default is 0.
| OUTDOC = Specifies a file path and file name to save the Excel report to.
| DEBUG = Determines if NOTES are left on and if temporary datasets are deleted.  1 is (yes) and 0 is (no).
|         Default is 0.
*------------------------------------------------------------------*
| OPERATING SYSTEM COMPATIBILITY
| UNIX SAS v9.4   :   YES
| PC SAS v9.4     :   YES
*------------------------------------------------------------------*
| MACRO CALL
|
| %compare_all (
|            BASE=,
|            COMPARE=,
|            ID=,
|            SELECT=,
|            OUTDOC=
|          );
*------------------------------------------------------------------*
| REQUIRED PARAMETERS
|
| Name      : BASE
| Default   : 
| Type      : LIBNAME
| Purpose   : REFER TO REFERENCE SECTION
|
| Name      : COMPARE
| Default   :
| Type      : LIBNAME
| Purpose   : REFER TO REFERENCE SECTION
|
| Name      : OUTDOC
| Default   :
| Type      : File path and file name
| Purpose   : REFER TO REFERENCE SECTION
*------------------------------------------------------------------*
| EXAMPLES
|
| Example 1: Basic Example Call:
  libname basedata '/some/file/path';
  libname compdata '/another/file/path';
  %compare_all(base=basedata, compare=compdata, outdoc=/filepath/filename.xlsx);
|
| Example 2: Select Specific Datasets:
  libname basedata '/some/file/path';
  libname compdata '/another/file/path';
  %compare_all(base=basedata, compare=compdata, select=Data1 Data2 Data3, outdoc=/filepath/filename.xlsx);
|
| Example 3: Add ID Variables:
  libname basedata '/some/file/path';
  libname compdata '/another/file/path';
  %compare_all(base=basedata, compare=compdata, select=Data1 Data2 Data3, id=ARM PATID CYCLE EVAL_DT,
               outdoc=/filepath/filename.xlsx);
*------------------------------------------------------------------*
| This program is free software; you can redistribute it and/or
| modify it under the terms of the GNU General Public License as
| published by the Free Software Foundation; either version 2 of
| the License, or (at your option) any later version.
|
| This program is distributed in the hope that it will be useful,
| but WITHOUT ANY WARRANTY; without even the implied warranty of
| MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
| General Public License for more details.
*------------------------------------------------------------------*/

%macro compare_all(
    /*REQUIRED PARAMETERS*/
    base=,compare=,outdoc=,
    /*OPTIONAL PARAMETERS*/
    crosstab_threshold=15,select=,id=,idsumtable=0, debug=0);
    
    
    /**Save current options to reset after macro runs**/
    %local _mergenoby _notes _qlm _odspath _starttime _listing;
    %let _starttime=%sysfunc(time());
    %let _notes=%sysfunc(getoption(notes));
    %let _mergenoby=%sysfunc(getoption(mergenoby));
    %let _qlm=%sysfunc(getoption(quotelenmax)); 
    %let _odspath=&sysodspath;
    %if %sysevalf(%superq(_odspath)=,boolean) %then %let _odspath=WORK.TEMPLAT(UPDATE) SASHELP.TMPLMST (READ);
    /**Turn off warnings for merging without a by and long quote lengths**/
    /**Turn off notes**/
    options mergenoby=NOWARN nonotes noquotelenmax;
    %if &debug=1 %then %do; options notes; %end;
    ods path WORK.TEMPLAT(UPDATE) SASHELP.TMPLMST (READ);
    /**See if the listing output is turned on**/
    proc sql noprint;
        select 1 into :_listing separated by '' from sashelp.vdest where upcase(destination)='LISTING';
    quit;
    
    /**Process Error Handling**/
    %if &sysver < 9.4 %then %do;
        %put ERROR: SAS must be version 9.4 or later;
        %goto errhandl;
    %end;     
    %local i z nerror;
    %let nerror=0;
    

    /*Don't send anything to output window, results window, and set escape character*/
    ods select none;
    ods noresults escapechar='^';

    proc sql noprint;
        %local basepath comppath;
        select distinct path into :basepath separated by '' from sashelp.vlibnam where upcase(libname)=upcase("&base");
        select distinct path into :comppath separated by '' from sashelp.vlibnam where upcase(libname)=upcase("&compare");
    quit;
    
    %if %sysevalf(%superq(basepath)=,boolean) %then %do;
        /**Throw Error**/
        %put ERROR: (Global: BASE) Library does not exist;
        %let nerror=%eval(&nerror+1);
    %end; 
    %if %sysevalf(%superq(comppath)=,boolean) %then %do;
        /**Throw Error**/
        %put ERROR: (Global: COMP) Library does not exist;
        %let nerror=%eval(&nerror+1);
    %end; 

    /**Check for OUTDOC**/
    %if %sysevalf(%superq(outdoc)=,boolean) %then %do;
        /**Throw Error**/
        %put ERROR: (Global: OUTDOC) No output document specified;
        %let nerror=%eval(&nerror+1);
    %end;
    /*Global Model Numeric Variables*/
    %macro _gnumcheck(parm, min,require);
        /**Check if missing**/
        %if %sysevalf(%superq(&parm)^=,boolean) %then %do;
            %if %sysfunc(notdigit(%sysfunc(compress(%superq(&parm),-.)))) > 0 %then %do;
                /**Check if character value**/
                %put ERROR: (Global: %qupcase(&parm)) Must be numeric.  %qupcase(%superq(&parm)) is not valid.;
                %let nerror=%eval(&nerror+1);
            %end;
            %else %if %sysevalf(&min^=,boolean) %then %do;
                %if %superq(&parm) < &min %then %do;
                    /**Makes sure number is not less than the minimum**/
                    %put ERROR: (Global: %qupcase(&parm)) Must be greater than %superq(min). %qupcase(%superq(&parm)) is not valid.;
                    %let nerror=%eval(&nerror+1);
                %end;
            %end;
        %end;
        %else %if &require=1 %then %do;
            /**Throw Error**/
            %put ERROR: (Global: %qupcase(&parm)) Cannot be missing;
            %put ERROR: (Global: %qupcase(&parm)) Possible values are numeric values greater than or equal to %superq(min);
            %let nerror=%eval(&nerror+1);           
        %end;       
    %mend;  
    %_gnumcheck(crosstab_threshold,1,1)
    /*** If any errors exist, stop macro and send to end ***/
    %if &nerror > 0 %then %do;
        %put ERROR: &nerror pre-run errors listed;
        %put ERROR: Macro DATA_SPECS will cease;
        %goto errhandl;
    %end;    
    %local i;
    proc contents data=&base.._all_
        out=_basedict %if %sysevalf(%superq(select)^=,boolean) %then %do;
           (where=(upcase(memname) in(%do i = 1 %to %sysfunc(countw(%superq(select),%str( )));
                                          "%qupcase(%sysfunc(dequote(%qscan(%superq(select),&i,%str( )))))"
                                      %end;) ))                                            
        %end;
        noprint order=collate;
    proc contents data=&compare.._all_
        out=_compdict %if %sysevalf(%superq(select)^=,boolean) %then %do;
           (where=(upcase(memname) in(%do i = 1 %to %sysfunc(countw(%superq(select),%str( )));
                                          "%qupcase(%sysfunc(dequote(%qscan(%superq(select),&i,%str( )))))"
                                      %end;) ))
        %end;
        noprint order=collate;
    run;

    proc transpose data=_basedict (drop=libname) out=_basedict_ct prefix=base;
        by memname name;
        var label format length;
    proc transpose data=_basedict (drop=libname) out=_basedict_nt (rename=(base1=base2)) prefix=base;
        by memname name;
        var type length formatl formatd;
    proc transpose data=_compdict (drop=libname) out=_compdict_ct prefix=comp;
        by memname name;
        var label format length;
    proc transpose data=_compdict (drop=libname) out=_compdict_nt (rename=(comp1=comp2)) prefix=comp;
        by memname name;
        var type length formatl formatd;
    run;
    
    data _basedict_t;
        set _basedict_ct _basedict_nt;
    data _compdict_t;
        set _compdict_ct _compdict_nt;
    proc sort data=_basedict_t;
        by memname name _name_;
    proc sort data=_compdict_t;
        by memname name _name_;
    run;

    data _attr_compare;
        merge _basedict_t (in=a) _compdict_t (in=b);
        by memname name _name_;
        if a and b and (base1^=comp1 or base2^=comp2) then mismatch=1;
        else mismatch=0;
        
        if mismatch=1 then output;
    run;
    
    proc sql noprint;
        create table _var_obs_compare as
            select a.*, max(0,b.natt_diff) as natt_diff 'Variable~Attributes',
            . as lost_observations 'Lost~Observations', . as new_observations 'New~Observations', . as data_changes 'Data~Changes',
            '' as id_variables length=300 'ID Variables Used','' as null from 
            (select coalescec(a.memname,b.memname) as memname 'Dataset Name',
            base_nvars 'Number of~Variables',base_nobs 'Number of~Observations', base_updated format=mmddyy10. 'Last Updated',
            comp_nvars 'Number of~Variables',comp_nobs 'Number of~Observations', comp_updated format=mmddyy10. 'Last Updated'
            from (select memname,count(name) as base_nvars,max(nobs) as base_nobs, max(datepart(modate)) as base_updated
                    from _basedict group by memname) a full outer join
                 (select memname,count(name) as comp_nvars,max(nobs) as comp_nobs, max(datepart(modate)) as comp_updated
                    from _compdict group by memname) b
            on a.memname=b.memname
            group by calculated memname) as a left join
               (select memname, count(distinct name) as natt_diff from _attr_compare group by memname) b
               on a.memname=b.memname;
        create table _var_attr_compare as
            select Upcase(coalescec(a.memname,b.memname)) as memname 'Dataset Name',
            upcase(coalescec(a.name,b.name)) as name 'Variable Name',
            ifc(a.type=1,'Numeric','Character') as base_type 'Type', a.length as base_length 'Length', a.format as base_fmt 'Format',a.label as base_label 'Label',
            ifc(b.type=1,'Numeric','Character') as comp_type 'Type', b.length as comp_length 'Length', b.format as comp_fmt 'Format',b.label as comp_label 'Label',
            ifc(^missing(a.name) and missing(b.name),'Yes','No') as lost_variable 'Lost~Variable',
            ifc(missing(a.name) and ^missing(b.name),'Yes','No') as new_variable 'New~Variable',
            . as data_changes 'No. Data~Changes',
            '' as id_variables length=3 'ID Variables?','' as null
            from _basedict a full outer join _compdict b
            on a.memname=b.memname and upcase(a.name)=upcase(b.name)
            order by coalesce(a.varnum,b.varnum);
            
        %local dlist ndata;
        select distinct memname into :dlist separated by '|' from
            (select distinct coalescec(a.memname,b.memname) as memname from 
            _basedict a inner join _compdict as b
            on a.memname=b.memname);
        %let ndata=1;
        %let ndata=%sysfunc(countw(&dlist,|));
        quit;

    ods select none;
    ods noresults;
    %do i = 1 %to &ndata;
        %local datid datidc;
        /**Open up dataset to check for variables**/
         /*base dataset*/
         %let datid&i = %sysfunc(open(&base..%scan(&dlist,&i,|)));
         %let any_id&i=0;
         %do j = 1 %to %sysfunc(countw(&id,%str( )));
             %local id&i.&j;
             %let id&i.&j=%sysfunc(varnum(&&datid&i,%scan(%superq(id),&j,%str( ))));
         %end;
         /**Close dataset**/
         %local closedatid;
         %let closedatid=%sysfunc(close(&&datid&i));
         /*Compare data*/
         %let datidc&i = %sysfunc(open(&compare..%scan(&dlist,&i,|)));
         %do j = 1 %to %sysfunc(countw(&id,%str( )));
             %local idc&i.&j;
             %let idc&i.&j=%sysfunc(varnum(&&datidc&i,%scan(%superq(id),&j,%str( ))));
         %end;
         /**Close dataset**/
         %let closedatid=%sysfunc(close(&&datidc&i));
         /*Check if ID is in both datasets*/
         %do j = 1 %to %sysfunc(countw(&id,%str( )));
             %if &&id&i.&j > 0 and &&idc&i.&j > 0 %then %let any_id&i=1; 
         %end;
         proc sql noprint;
             %local vlist&i llist&i nvar&i;
             select name,label into :vlist&i separated by '|',:llist&i separated by '|'
                 from _basedict where upcase(name) in(select upcase(name) from _compdict where upcase(memname)=upcase("%scan(&dlist,&i,|)"))
                 and upcase(memname)=upcase("%scan(&dlist,&i,|)")
                 %if &&any_id&i=1 %then %do;
                     and upcase(name) ^in(
                        %do j = 1 %to %sysfunc(countw(&id,%str( )));
                            %if &&id&i.&j > 0 and &&idc&i.&j > 0 %then %do; "%qupcase(%scan(&id,&j,%str( )))" %end; 
                        %end;)
                 %end;;
             %let nvar&i=%sysfunc(countw(&&vlist&i,|));
         quit;
     
         %local idlist&i;
         %if &&any_id&i=1 %then %do;
            %let idlist&i=;
            %do j = 1 %to %sysfunc(countw(&id,%str( )));
                %if %sysevalf(%superq(id&i.&j)>0,boolean) and %sysevalf(%superq(idc&i.&j)>0,boolean) and %sysevalf(%superq(idlist&i)^=,boolean) %then %let idlist&i=&&idlist&i %scan(&id,&j,%str( ));
                %else %if %sysevalf(%superq(id&i.&j)>0,boolean) and %sysevalf(%superq(idc&i.&j)>0,boolean) %then %let idlist&i=%scan(&id,&j,%str( ));
            %end;
            proc sort data=&base..%scan(&dlist,&i,|) out=_base&i;
               by &&idlist&i;
            proc sort data=&compare..%scan(&dlist,&i,|) out=_compare&i;
               by &&idlist&i;
            run;
         %end;
         %else %do;
             data _base&i;
                 set &base..%scan(&dlist,&i,|);
             run;
             data _compare&i;
                 set &compare..%scan(&dlist,&i,|);
             run;
         %end;
         %local any_diff&i same_type&i removed&i added&i;
         %let any_diff&i=0;
         %let same_type&i=0;
         %let removed&i=0;
         %let added&i=0;
         %do j = 1 %to &&nvar&i;
             %local any_diff&i._&j same_type&i._&j removed&i._&j added&i._&j;
             %let any_diff&i._&j=0;
             %let same_type&i._&j=0;
             %let removed&i._&j=0;
             %let added&i._&j=0;
         %end;
         %if &&nvar&i>0 %then %do;     
         data _comparison&i;
             merge _base&i (in=b keep=%if &&any_id&i=1 %then %do; &&idlist&i %end;
                                      %do j = 1 %to &&nvar&i; %scan(&&vlist&i,&j,|) %end;
                                 rename=(%do j = 1 %to &&nvar&i; %scan(&&vlist&i,&j,|)=_bvar&j %end;))
                   _compare&i (in=c keep=%if &&any_id&i=1 %then %do; &&idlist&i %end;
                                         %do j = 1 %to &&nvar&i; %scan(&&vlist&i,&j,|) %end;
                                    rename=(%do j = 1 %to &&nvar&i; %scan(&&vlist&i,&j,|)=_cvar&j %end;)) end=last;
             %if &&any_id&i=1 %then %do;
                 by &&idlist&i;
             %end;
             array any_diff {&&nvar&i};
             array _any_diff {&&nvar&i} _temporary_;
             array same_type {&&nvar&i};
             retain same_type removed added;
             if _n_=1 then do;
                 removed=0;added=0;
             end;
             observation=_n_;
             if b and ^c then do;
                 _removed=1;
                 removed+1;
             end;
             else _removed=0;
             if c and ^b then do;
                 added+1;
                 _added=1;
             end;
             else _added=0;
             %do j = 1 %to &&nvar&i;
                 if _n_=1 then do;
                     if (vtype(_bvar&j) = vtype(_cvar&j)) then same_type(&j)=ifn((vtype(_bvar&j)='N'),1,2);
                     else same_type(&j)=0;
                 end;
                 if same_type(&j) and (b and c) then do;
                     any_diff(&j)=(_bvar&j^=_cvar&j);
                     _any_diff(&j)+(_bvar&j^=_cvar&j);
                 end;
             %end;

             if last then do;
                 do i = 1 to dim(any_diff);
                     call symputx("any_diff&i._"||strip(put(i,12.0)),_any_diff(i),'l');
                     call symputx("same_type&i._"||strip(put(i,12.0)),same_type(i),'l');
                     end;
                 call symputx("any_diff&i",sum(of _any_diff(*)),'l');
                 call symputx("same_type&i",dim(same_type)-sum(of same_type(*)),'l');
                 call symputx("removed&i",removed,'l');
                 call symputx("added&i",added,'l');
             end;
             drop i;
             null='';
         run;
             data _comparison&i._t;
                 set _comparison&i;
             
                 array any_diff {&&nvar&i};
                 array same_type {&&nvar&i};
                 length nvar 8. variable $60. _base _compare $300.;
                 %do j = 1 %to &&nvar&i;
                     nvar=&j;
                     variable="%scan(&&vlist&i,&j,|)";
                     if any_diff(&j)=1 then do;
                         _base=vvalue(_bvar&j);
                         _compare=vvalue(_cvar&j);
                         %if &&same_type&i._&j=1 %then %do;
                             if same_type(&j)=1 and ^missing(_cvar&j) and ^missing(_bvar&j) then do;
                                 _diff=_cvar&j-_bvar&j;
                             end;
                             else call missing(_diff);
                         %end;
                         %else %do;
                             call missing(_diff);
                         %end;
                         output;
                     end;
                 %end;
                 keep &&idlist&i nvar variable _base _compare _diff;
             run;
         
             proc sql;
                 create table _id_lvl_sum_&i as
                     select
                     %if %sysevalf(%superq(idlist&i)^=,boolean) %then %do j = 1 %to %sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( )))));
                         %scan(%superq(idlist&i),&j,%str( )),
                     %end;
                     sum(_added) as nadded,sum(_removed) as nremoved,
                     sum(nchanges) as nchanges
                     from (select *,sum(%do j=1 %to &&nvar&i;
                                            %if &j>1 %then %do; , %end; any_diff&j
                                        %end;) as nchanges from _comparison&i)
                     %if %sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( )))))>0  %then %do; 
                         group by %do j = 1 %to %sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( )))));
                                       %scan(%superq(idlist&i),1,%str( ))
                                  %end;
                     %end;;
                 create table _comparison&i._t_sum as
                     select 
                     %if %sysevalf(%superq(idlist&i)^=,boolean) %then %do j = 1 %to %sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( )))));
                         %scan(%superq(idlist&i),&j,%str( )),
                     %end;
                     nvar, variable, _base, _compare, count(*) as n, min(_diff) as min, max(_diff) as max
                     from _comparison&i._t
                     group by %if %sysevalf(%superq(idlist&i)^=,boolean) %then %do j = 1 %to %sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( )))));
                                  %scan(%superq(idlist&i),&j,%str( )),
                              %end;
                              nvar, variable, _base, _compare;
                 create table _comparison&i._t_sum_final as
                     select * from _comparison&i._t_sum
                     group by %if %sysevalf(%superq(idlist&i)^=,boolean) %then %do j = 1 %to %sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( )))));
                                  %scan(%superq(idlist&i),&j,%str( )),
                              %end;
                              nvar, variable having count(*) <= &crosstab_threshold
                     outer union corr
                     select 
                     %if %sysevalf(%superq(idlist&i)^=,boolean) %then %do j = 1 %to %sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( )))));
                         %scan(%superq(idlist&i),&j,%str( )),
                     %end;
                     nvar, variable, ifc(^missing(_base),'Non-Missing','Missing') as _base,
                     ifc(^missing(_compare),'Non-Missing','Missing') as _compare, sum(n) as n, min(min) as min, max(max) as max
                     from (select * from _comparison&i._t_sum
                     group by %if %sysevalf(%superq(idlist&i)^=,boolean) %then %do j = 1 %to %sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( )))));
                                    %scan(%superq(idlist&i),&j,%str( )),
                              %end;
                              nvar, variable having count(*) >&crosstab_threshold)
                              group by %if %sysevalf(%superq(idlist&i)^=,boolean) %then %do j = 1 %to %sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( )))));
                                           %scan(%superq(idlist&i),&j,%str( )),
                                       %end;
                                       nvar, variable, calculated _base, calculated _compare
                     order by %if %sysevalf(%superq(idlist&i)^=,boolean) %then %do j = 1 %to %sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( )))));
                                  %scan(%superq(idlist&i),&j,%str( )),
                              %end;
                              nvar, variable, _base, _compare;
             quit;
             data _id_lvl_sum_&i;
                 merge _id_lvl_sum_&i _comparison&i._t_sum_final;
                 %if %sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( )))))>0 %then %do;
                    by %do  j = 1 %to %sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( )))));
                          %scan(%superq(idlist&i),&j,%str( ))
                       %end;;
                 %end;
                 %else %do;
                     retain _nadded _nremoved _nchanges;
                     if _n_=1 then do;
                         _nadded=nadded;_nremoved=nremoved;_nchanges=nchanges;
                     end;
                     else do;
                         nadded=_nadded;nremoved=_nremoved;nchanges=_nchanges;
                     end;
                     drop _nadded _nremoved _nchanges;   
                 %end;
             run;
         %end;
   %end;

   proc sql;
       %do i = 1 %to &ndata;
           %if %sysevalf(%superq(removed&i)=,boolean) %then %let removed&i=.;
           %if %sysevalf(%superq(added&i)=,boolean) %then %let added&i=.;
           %if %sysevalf(%superq(any_diff&i)=,boolean) %then %let any_diff&i=.;
           %do j = 1 %to &&nvar&i;
               %if %sysevalf(%superq(any_diff&i._&j)=,boolean) %then %let any_diff&i._&j=0;
           %end;
       %end;
       UPDATE _var_obs_compare
           set lost_observations=case(memname)
             %do i = 1 %to &ndata;
                 when "%scan(&dlist,&i,|)" then &&removed&i
             %end;
             else . end,
             new_observations=case(memname)
             %do i = 1 %to &ndata;
                 when "%scan(&dlist,&i,|)" then &&added&i
             %end;
             else . end,  
             data_changes=case(memname)
             %do i = 1 %to &ndata;
                 when "%scan(&dlist,&i,|)" then &&any_diff&i
             %end;
             else . end,  
             id_variables=case(memname)
             %do i = 1 %to &ndata;
                 when "%scan(&dlist,&i,|)" then tranwrd("&&idlist&i",' ',', ')
             %end;
             else '' end;
       UPDATE _var_attr_compare
           set data_changes=case(memname)
                   %do i = 1 %to &ndata;
                       when "%scan(&dlist,&i,|)" then case (upcase(name))
                           %if &&nvar&i>0 %then %do j = 1 %to &&nvar&i;
                               %if %sysevalf(%superq(any_diff&i._&j)^=,boolean) %then %do;
                                    when upcase("%scan(&&vlist&i,&j,|)") then %superq(any_diff&i._&j)
                               %end;
                           %end;
                           %else %do;
                              when "" then .
                           %end;
                           else . end
                   %end;
                   else . end,
               id_variables=case 
                       when upcase(name) in("" %do j = 1 %to %sysfunc(countw(&id,%str( ))); "%qupcase(%scan(&id,&j,%str( )))" %end;) then 'Yes'
                       else 'No' end;           
   quit;
   
   %put COMPARE_ALL: Comparisons completed, runtime: %sysfunc(putn(%sysevalf(%sysfunc(TIME())-&_starttime.),mmss.));
     
   %if %sysevalf(%superq(outdoc)^=,boolean) %then
       %put COMPARE_ALL: Printing to &outdoc;
   %else %put COMPARE_ALL: Printing to &base._&compare._comparison.xlsx;
   
   %local nbsp;
   data _null_;
       call symput('nbsp','A0'x);
   run;
   ods select all;
   ods listing close;
   ods escapechar='^';
   ods excel
       %if %sysevalf(%superq(outdoc)^=,boolean) %then %do;
           file="&outdoc"
       %end;
       %else %do;
           file="&base._&compare._comparison.xlsx"
       %end;;
   %local topref;
   %let topref=%str(=HYPERLINK("#'Top Summary'!A4","Return to Top Summary"));
   ods excel options(sheet_name="Top Summary" frozen_Headers='5' frozen_rowheaders='1' flow='tables'
        absolute_column_width='36,13,10,12,13,10,12,9,12,12,9,25');
   %put COMPARE_ALL: Printing top summary, runtime: %sysfunc(putn(%sysevalf(%sysfunc(TIME())-&_starttime.),mmss.));
   proc report data=_var_obs_compare nowd split='~' missing 
       style(header)={just=l fontweight=bold bordertopwidth=3pt bordertopcolor=black fontfamily='Arial'
          borderleftwidth=3pt borderleftcolor=black borderrightwidth=3pt borderrightcolor=black borderbottomwidth=3pt borderbottomcolor=black
          fontsize=10pt background=white color=black}
       style(column)={bordercolor=black fontsize=10pt just=c fontfamily='Arial'}
       style(lines)={color=black fontweight=bold fontsize=10pt bordertopwidth=3pt bordertopcolor=black
          borderleftwidth=3pt borderleftcolor=black borderrightwidth=3pt borderrightcolor=black borderbottomwidth=3pt borderbottomcolor=black}
       style(report)={bordertopwidth=3pt bordertopcolor=black
          borderleftwidth=3pt borderleftcolor=black borderrightwidth=3pt borderrightcolor=black borderbottomwidth=3pt borderbottomcolor=black};
       
       columns ("Base Library (%qupcase(&base)): &basepath"
                 ("Compare Library (%qupcase(&compare)): &comppath"
                   (%if %sysevalf(%superq(select)^=,boolean) %then %do;
                        "Summary of Selected Datasets Compared (Option SELECT is in Effect: Other Datasets Could Exist in Library)"
                    %end;
                    %else %do;
                        "Summary of Datasets Compared"
                    %end;
             memname
             null=n6, (memname=mem2 memname=mem3)
             null=n2, (base_updated base_nvars base_nobs)
             null=n3, (comp_updated comp_nvars comp_nobs)
             null=n4, (natt_diff lost_observations new_observations data_changes)
             null=n5, (id_variables id_variables=n1))))
             _null_;
       define n1 / display noprint;
       define n2 / across "Base" style={background=lightblue};
       define n3 / across "Compare" style={background=lightgray};
       define n4 / across "Any Differences" style={background=lightred};
       define n6 / across "A0"x style={borderbottomstyle=none borderbottomcolor=white};
       define n5 / across "A0"x style={borderbottomstyle=none borderbottomcolor=white background=lightgreen};
       define memname / display noprint;
       define mem3 / display noprint;
       define mem2 / display 
           style(column)={borderleftwidth=3pt borderleftcolor=black borderrightwidth=3pt borderrightcolor=black just=l}
           style={width=100% just=l} 
           style(header)={just=c bordertopstyle=none bordertopcolor=white};
       define base_updated / display style={borderleftwidth=3pt borderleftcolor=black width=100%} center style(header)={background=lightblue};
       define base_nvars / display style(header)={background=lightblue};
       define base_nobs / display style(header)={background=lightblue};
       define comp_nvars / display style(header)={background=lightgray};
       define comp_nobs / display style(header)={background=lightgray};
       define comp_updated / display style={borderleftwidth=3pt borderleftcolor=black width=100%} center style(header)={background=lightgray};
       
       define natt_diff / display style={borderleftwidth=3pt borderleftcolor=black width=100%} center style(header)={background=lightred};
       define lost_observations / display style(header)={background=lightred};
       define new_observations / display style(header)={background=lightred};
       define data_changes / display style(header)={background=lightred};
       define id_variables / display style={borderleftwidth=3pt borderleftcolor=black width=100% bordertopstyle=none bordertopcolor=white} left
           style(header)={background=lightgreen} style(column)={just=l};
       define _null_ / computed noprint;
       
       compute _null_;
            if _c5_^=_c8_ then do;
                call define('_c5_','style/merge','style={background=lightorange}');
                call define('_c8_','style/merge','style={background=lightorange}');
            end;
            if _c6_^=_c9_ then do;
                call define('_c6_','style/merge','style={background=lightorange}');
                call define('_c9_','style/merge','style={background=lightorange}');
            end;
            if _c10_>0 then call define('_c10_','style/merge','style={background=lightorange}');
            if _c11_>0 then call define('_c11_','style/merge','style={background=lightorange}');
            if _c12_>0 then call define('_c12_','style/merge','style={background=lightorange}');
            if _c13_>0 then call define('_c13_','style/merge','style={background=lightorange}');
            urlstring="#'"||substr('Summary-'||strip(memname),1,28)||"'!A4";
            if ^missing(_c4_) and ^missing(_c7_) then call define('_c2_','url',urlstring);
       endcomp;
   run;

   %do i = 1 %to &ndata;
       %put COMPARE_ALL: Printing dataset (%scan(&dlist,&i,|)) summary, runtime: %sysfunc(putn(%sysevalf(%sysfunc(TIME())-&_starttime.),mmss.));
       ods excel options(sheet_name="Summary-%scan(&dlist,&i,|)" frozen_Headers='5' frozen_rowheaders='1' 
           absolute_column_width='29,13,12,55,17,10,12,55,17,10,9,12,12');
       %local datref&i;
       %if %length(Summary-%scan(&dlist,&i,|))>28 %then 
           %let datref&i==HYPERLINK("#'%substr(Summary-%scan(&dlist,&i,|),1,28)'!A4","Dataset Name: %scan(&dlist,&i,|)");
       %else %let datref&i==HYPERLINK("#'Summary-%scan(&dlist,&i,|)'!A4","Dataset Name: %scan(&dlist,&i,|)");
       proc report data=_var_attr_compare nowd split='~' missing 
           style(header)={just=l fontweight=bold bordertopwidth=3pt bordertopcolor=black fontfamily='Arial'
               borderleftwidth=3pt borderleftcolor=black borderrightwidth=3pt borderrightcolor=black borderbottomwidth=3pt borderbottomcolor=black
               fontsize=10pt background=white color=black}
           style(column)={bordercolor=black fontsize=10pt just=c fontfamily='Arial' width=100%}
           style(lines)={color=black fontweight=bold fontsize=10pt bordertopwidth=3pt bordertopcolor=black
               borderleftwidth=3pt borderleftcolor=black borderrightwidth=3pt borderrightcolor=black borderbottomwidth=3pt borderbottomcolor=black}
           style(report)={bordertopwidth=3pt bordertopcolor=black
                borderleftwidth=3pt borderleftcolor=black borderrightwidth=3pt borderrightcolor=black borderbottomwidth=3pt borderbottomcolor=black};
       
           columns 
                 ("Dataset Name: %scan(&dlist,&i,|)"
                   ("ID Variables: &&idlist&i"
                       ("%superq(topref)"
               name=nm
               null=n1, (name id_variables)
               null=n2, (base_type base_label base_fmt base_length)
               null=n3, (comp_type comp_label comp_fmt comp_length)
               null=n4, (lost_variable new_variable data_changes)
               )));

           define n1 / across "A0"x;
           define n2 / across "Base" style={background=lightblue};
           define n3 / across "Compare" style={background=lightgray};
           define n4 / across "Any Differences" style={background=lightred};

           define nm / display noprint;
           define name / display style(header)={just=c bordertopstyle=none bordertopcolor=white} style={width=100%}
               style(column)={borderleftwidth=3pt borderleftcolor=black borderrightwidth=3pt borderrightcolor=black just=l}
                left;
           define base_type / display style={borderleftwidth=3pt borderleftcolor=black width=100%} center style(header)={background=lightblue};
           define base_label / display left style={just=l} style(header)={background=lightblue};
           define base_length / display left style={just=l} style(header)={background=lightblue};
           define comp_length / display left style={just=l} style(header)={background=lightgray};
           define comp_label / display left style={just=l} style(header)={background=lightgray};
           define base_fmt / display left style={just=l} style(header)={background=lightblue};
           define comp_fmt / display left style={just=l} style(header)={background=lightgray};
           define lost_variable / display style(header)={background=lightred};
           define new_variable / display style(header)={background=lightred};
           define data_changes / display style(header)={background=lightred};
           define comp_type / display style={borderleftwidth=3pt borderleftcolor=black width=100%} center style(header)={background=lightgray};
           define lost_variable / display style={borderleftwidth=3pt borderleftcolor=black width=100%} center style(header)={background=lightred};
           
           define id_variables / display style={borderleftwidth=3pt borderleftcolor=black width=100%} style(header)={background=lightgreen} center;

           compute data_changes;
               if _c13_='Yes' then do;
                   call define('_c2_','style/merge','style={background=lightorange}');
                   call define('_c13_','style/merge','style={background=lightorange}');
               end;
               if _c12_='Yes' then do;
                   call define('_c2_','style/merge','style={background=lightred}');
                   call define('_c12_','style/merge','style={background=lightred}');
               end;
               if _c14_>0 then do;
                   call define('_c2_','style/merge','style={background=lightred}');
                   call define('_c14_','style/merge','style={background=lightred}');
               end;
               if _c3_='Yes' then do;
                   call define('_c2_','style/merge','style={background=lightgreen}');
                   call define('_c3_','style/merge','style={background=lightgreen}');
               end;
               if _c4_^=_c8_ and _c13_='No' then do;
                   call define('_c4_','style/merge','style={background=lightorange}');
                   call define('_c8_','style/merge','style={background=lightorange}');
               end;
               if _c5_^=_c9_ and _c13_='No' then do;
                   call define('_c5_','style/merge','style={background=lightorange}');
                   call define('_c9_','style/merge','style={background=lightorange}');
               end;
               if _c6_^=_c10_ and _c13_='No' then do;
                   call define('_c6_','style/merge','style={background=lightorange}');
                   call define('_c10_','style/merge','style={background=lightorange}');
               end;
               if _c7_^=_c11_ and _c13_='No' then do;
                   call define('_c7_','style/merge','style={background=lightorange}');
                   call define('_c11_','style/merge','style={background=lightorange}');
               end;
               length tempname $32.;
               if _c14_>0 then do;
                   if substr("%scan(&dlist,&i,|)-"||strip(_c2_),1,28)=substr("%scan(&dlist,&i,|)-"||strip(tempname),1,28) then do;
                       if tempnamen=. then tempnamen=2;
                       else tempnamen+1;
                       urlstring="#'"||substr("%scan(&dlist,&i,|)-"||strip(_c2_),1,28)||' '||strip(put(tempnamen,12.0))||"'!A4";
                   end;
                   else do;
                       urlstring="#'"||substr("%scan(&dlist,&i,|)-"||strip(_c2_),1,28)||"'!A4";
                       tempnamen=.;
                   end;
                   call define('_c2_','url',urlstring);
               end;
               tempname=_c2_;
           endcomp;
           where memname="%scan(&dlist,&i,|)";
       run;
       
       %put COMPARE_ALL: Printing dataset (%scan(&dlist,&i,|)) data changes summary, runtime: %sysfunc(putn(%sysevalf(%sysfunc(TIME())-&_starttime.),mmss.));
       ods excel options(sheet_name="ID Summary-%scan(&dlist,&i,|)" frozen_Headers='5' frozen_rowheaders="%sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( )))))"
           %if %sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( )))))>0  %then %do;
               absolute_column_width="%sysfunc(repeat(%str(31,),%sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( )))))-1))13,12,13,19,31,31,13,13,13"
           %end;
           %else %do;
               absolute_column_width='13,12,13,19,31,31,13,13,13'
           %end;);
       proc report data=_id_lvl_sum_&i nowd split='~' missing spanrows
           style(header)={just=l fontweight=bold bordertopwidth=3pt bordertopcolor=black fontfamily='Arial'
               borderleftwidth=3pt borderleftcolor=black borderrightwidth=3pt borderrightcolor=black borderbottomwidth=3pt borderbottomcolor=black
               fontsize=10pt background=white color=black}
           style(column)={bordercolor=black fontsize=10pt just=c fontfamily='Arial' width=100% vjust=top}
           style(lines)={color=black fontweight=bold fontsize=10pt bordertopwidth=3pt bordertopcolor=black
               borderleftwidth=3pt borderleftcolor=black borderrightwidth=3pt borderrightcolor=black borderbottomwidth=3pt borderbottomcolor=black}
           style(report)={bordertopwidth=3pt bordertopcolor=black
                borderleftwidth=3pt borderleftcolor=black borderrightwidth=3pt borderrightcolor=black borderbottomwidth=3pt borderbottomcolor=black};
           
           columns 
                 ("Summary of %scan(&dlist,&i,|) Summary of Data Changes"
                   ("All Available ID Variables: &&idlist&i"
                     %if %sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( )))))=0 %then %do;
                         ("Note: No ID variables used in summary"
                     %end;
                     %else %if %sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( )))))=%sysfunc(countw(%superq(idlist&i),%str( ))) %then %do;  
                         ("Note: All ID variable(s) are used in summary"
                     %end;
                     %else %do;
                         ("Note: Only first %sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( ))))) ID variable(s) is used in summary"
                     %end;
                       ("%superq(topref)"
               %if %sysevalf(%superq(idlist&i)^=,boolean) %then %do j = 1 %to %sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( )))));
                   %scan(%superq(idlist&i),&j,%str( ))
               %end;
               nremoved nadded nchanges nvar variable _base _compare n min max))));

           %if %sysevalf(%superq(idlist&i)^=,boolean) %then %do j = 1 %to %sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( )))));
               define %scan(%superq(idlist&i),&j,%str( )) / order style(header)={background=lightgreen};
           %end;
           define nadded / order 'New~Observations' center style(header)={background=lightred};
           define nremoved / order 'Lost~Observations' center style(header)={background=lightred};
           define nchanges / order 'N~Data Changes' center style(header)={background=lightred};
           define nvar / order noprint;
           define variable / order 'Variable' left style(header)={background=lightred};
           define _base / display 'Base' left style(header)={background=lightblue};
           define _compare / display 'Compare' left style(header)={background=lightgray};
           define n / display 'N' center style(header)={background=lightred};
           define min / display 'Mininum' center style(header)={background=lightred};
           define max / display 'Maximum' center style(header)={background=lightred};

           compute max;
              %if %sysfunc(min(&idsumtable,%sysfunc(countw(%superq(idlist&i),%str( )))))>0 %then %do;
                  if ^missing(%scan(%superq(idlist&i),1,%str( ))) then call define(_row_,'style/merge','style={bordertopwidth=3pt bordertopstyle=solid bordertopcolor=black}');
              %end;
              if ^missing(variable) or nchanges=0 then shade+1;
              if mod(shade,2) then do;
                  call define('variable','style/merge','style={background=greyef}');
                  call define('n','style/merge','style={background=greyef}');
                  call define('_base','style/merge','style={background=greyef}');
                  call define('_compare','style/merge','style={background=greyef}');
                  call define('min','style/merge','style={background=greyef}');
                  call define('max','style/merge','style={background=greyef}');
              end;
           endcomp;                  
       run;
       
       %if &&any_diff&i>0 %then %do j = 1 %to &&nvar&i;
           %if %superq(any_diff&i._&j) > 0 %then %do;
           %put COMPARE_ALL: Printing dataset (%scan(&dlist,&i,|)) variable (%scan(&&vlist&i,&j,|)) specific changes, runtime: %sysfunc(putn(%sysevalf(%sysfunc(TIME())-&_starttime.),mmss.));
               ods excel options(sheet_name="%scan(&dlist,&i,|)-%scan(&&vlist&i,&j,|)" frozen_Headers='5' frozen_rowheaders='1'
                   %if &&any_id&i=1 %then %do;
                       absolute_column_width="%sysfunc(repeat(%str(31,),%sysfunc(countw(%superq(idlist&i),%str( )))-1))15,31,31,15,15"
                   %end;
                   %else %do;
                       absolute_column_width="15,31,31,15,15"
                   %end;);
       proc report data=_comparison&i nowd split='~' missing
           style(header)={just=l fontweight=bold bordertopwidth=3pt bordertopcolor=black fontfamily='Arial'
               borderleftwidth=3pt borderleftcolor=black borderrightwidth=3pt borderrightcolor=black borderbottomwidth=3pt borderbottomcolor=black
               fontsize=10pt background=white color=black}
           style(column)={bordercolor=black fontsize=10pt just=c fontfamily='Arial' width=100%}
           style(lines)={color=black fontweight=bold fontsize=10pt bordertopwidth=3pt bordertopcolor=black
               borderleftwidth=3pt borderleftcolor=black borderrightwidth=3pt borderrightcolor=black borderbottomwidth=3pt borderbottomcolor=black}
           style(report)={bordertopwidth=3pt bordertopcolor=black
                borderleftwidth=3pt borderleftcolor=black borderrightwidth=3pt borderrightcolor=black borderbottomwidth=3pt borderbottomcolor=black};
       
           columns 
                 ("%superq(datref&i)"
                   ("Variable Name (label): %scan(&&vlist&i,&j,|,m) (%scan(%superq(llist&i),&j,|,m))"
                       ("%superq(topref)"
                  observation=obs
                  %if %sysfunc(countw(&&idlist&i,%str( ))) > 0 %then %do;
                      null=n1, (&&idlist&i)
                  %end;
                observation
               _bvar&j _cvar&j
               abs_change pct_change)));
           %do k = 1 %to %sysfunc(countw(&&idlist&i,%str( )));
               define %scan(&&idlist&i,&k,%str( )) / display 
                   style(column)={%if &k=i %then %do; borderleftwidth=3pt borderleftcolor=black %end; just=l}
                   style={width=100%} style(header)={just=c bordertopstyle=none bordertopcolor=white background=lightgreen} left; 
           %end;

           define obs / display noprint;
           %if %sysfunc(countw(&&idlist&i,%str( ))) > 0 %then %do;
               define n1 / across "ID Variables" style(header)={background=lightgreen};
           %end;
           define observation / display 'Observation' style={borderleftwidth=3pt borderleftcolor=black width=100%} center;
           define _bvar&j / display style={borderleftwidth=3pt borderleftcolor=black width=100%} center 'Base' style(header)={background=lightblue};
           define _cvar&j / display 'Compare' style(header)={background=lightgrey};
           define abs_change / computed 'Absolute~Change' style={borderleftwidth=3pt borderleftcolor=black width=100%} center style(header)={background=lightred};
           define pct_change / computed 'Percent~Change' format=12.1 style(header)={background=lightred};

           compute pct_Change;
               if vtype(_bvar&j)='N' and vtype(_cvar&j)='N' and ^missing(_bvar&j) and ^missing(_cvar&j) then do;
                   abs_change=_cvar&j-_bvar&j;
                   if _bvar&j>0 then pct_change=100*abs_change/_bvar&j;
               end;
           endcomp;
           where any_diff&j=1;
       run;

           %end;
       %end;
    %end;
    options notes;
    ods excel close;
  
    %errhandl:
    %if &_listing=1 %then %do;
        ods Listing;
    %end;
    %else %do;
        ods listing close;
    %end;
    ods select all;
    ods results;
    options nonotes;
    /**Delete temporary datasets**/
    proc datasets nolist nodetails;
        %if &debug=0 %then %do;
        delete %do i = 1 %to &ndata; _base&i _compare&i _comparison&i  _comparison&i._t _id_lvl_sum_&i _comparison&i._t_sum _comparison&i._t_sum_final %end;
            _var_attr_compare _var_obs_compare _compdict _compdict_ct _compdict_nt _compdict_t
            _basedict _basedict_ct _basedict_nt _basedict_t  _attr_compare  
            ;
        %end;
    quit; 
    /**Reload previous Options**/ 
    ods path &_odspath;
    options mergenoby=&_mergenoby &_notes &_qlm;
    %put COMPARE_ALL has finished processing, runtime: %sysfunc(putn(%sysevalf(%sysfunc(TIME())-&_starttime.),mmss.));    
%mend;

The segment that caused error:

   proc sql;
       %do i = 1 %to &ndata;
           %if %sysevalf(%superq(removed&i)=,boolean) %then %let removed&i=.;
           %if %sysevalf(%superq(added&i)=,boolean) %then %let added&i=.;
           %if %sysevalf(%superq(any_diff&i)=,boolean) %then %let any_diff&i=.;
           %do j = 1 %to &&nvar&i;
               %if %sysevalf(%superq(any_diff&i._&j)=,boolean) %then %let any_diff&i._&j=0;
           %end;
       %end;
       UPDATE _var_obs_compare
           set lost_observations=case(memname)
             %do i = 1 %to &ndata;
                 when "%scan(&dlist,&i,|)" then &&removed&i
             %end;
             else . end,
             new_observations=case(memname)
             %do i = 1 %to &ndata;
                 when "%scan(&dlist,&i,|)" then &&added&i
             %end;
             else . end,  
             data_changes=case(memname)
             %do i = 1 %to &ndata;
                 when "%scan(&dlist,&i,|)" then &&any_diff&i
             %end;
             else . end,  
             id_variables=case(memname)
             %do i = 1 %to &ndata;
                 when "%scan(&dlist,&i,|)" then tranwrd("&&idlist&i",' ',', ')
             %end;
             else '' end;
       UPDATE _var_attr_compare
           set data_changes=case(memname)
                   %do i = 1 %to &ndata;
                       when "%scan(&dlist,&i,|)" then case (upcase(name))
                           %if &&nvar&i>0 %then %do j = 1 %to &&nvar&i;
                               %if %sysevalf(%superq(any_diff&i._&j)^=,boolean) %then %do;
                                    when upcase("%scan(&&vlist&i,&j,|)") then %superq(any_diff&i._&j)
                               %end;
                           %end;
                           %else %do;
                              when "" then .
                           %end;
                           else . end
                   %end;
                   else . end,
               id_variables=case 
                       when upcase(name) in("" %do j = 1 %to %sysfunc(countw(&id,%str( ))); "%qupcase(%scan(&id,&j,%str( )))" %end;) then 'Yes'
                       else 'No' end;           
   quit;

Re: help with error in macro compare_all

Tom — Tue, 15 Mar 2022 21:59:58 GMT

Try running it with MLOGIC and SYMBOLGEN options turned off and just the MPRINT option turned on.

Then the SAS log will be easier to read and tell exactly what code it was the macro generated that triggered the error from SAS.

If I had to guess a cause without actually trying to figure out that long macro I guess that

1) Your datasets are using non standard variable names and a macro from 10 years ago did not expect that to happen.

2) one of the datasets either has no variables or perhaps has no numeric or character variables and the macro was not expecting that situation.

Re: help with error in macro compare_all

lydiawawa — Tue, 15 Mar 2022 22:10:49 GMT

Hi after turning off the options, here is the log output:

Re: help with error in macro compare_all

lydiawawa — Tue, 15 Mar 2022 22:12:19 GMT

I can rule out #2 , both datasets have numeric and character variables.

Re: help with error in macro compare_all

Tom — Tue, 15 Mar 2022 22:16:43 GMT

So it is not generating valid SAS code.

Not sure what it is trying to do. See if you can find the section where it is trying to generate that SQL UPDATE statement and figure out why it is not generating a valid CASE clause.

It really looks like it skipped something, like the name of the dataset.

I would check the steps above the one that is failing. It looks like it is trying to use UPDATE to store the resutls of something. Most likely the results did not get created properly. The failure did not cause a SAS error, but it threw off the macro so that it started generating invalid code.

Re: help with error in macro compare_all

ballardw — Tue, 15 Mar 2022 23:15:18 GMT

Strongly suspect that your "error" is not the first problem. You get messages about a Do loop that will not execute because you are going from 1 to 0 (the macro language doesn't like that). So everything inside that loop isn't happening and any code that requires something from that loop will likely fail. That 0 end likely means something much earlier is missing.

You do not show your actual call to the macro so it isn't even possible for us to see a possible obvious problem.

Re: help with error in macro compare_all

SASKiwi — Tue, 15 Mar 2022 23:41:12 GMT

The problem is your NDATA macro variable resolves to 0 which results in invalid %DO loops. NDATA is defined in this statement:

%let ndata=%sysfunc(countw(&dlist,|));

That suggests the macro DLIST is not populated as NDATA is the number of words in DLIST.

DLIST appears to be a list of tables to be processed. So as others have said, please post the code calling the macro as that is the most likely cause of the errors.

Edit: DLIST is derived from the temporary SAS datasets _basedict and _compdict. Check that these datasets are created and populated.

Re: help with error in macro compare_all

lydiawawa — Wed, 16 Mar 2022 00:03:42 GMT

Hi, this is the code I used to call the macro

%compare_all(base = vdata,
compare = orig,
outdoc = /data/output/compare.xlsx);

There are only one dataset in each library respectively, vdata and orig. I don't how would that leads to the error.

Re: help with error in macro compare_all

lydiawawa — Wed, 16 Mar 2022 00:22:47 GMT

Could it have something to do with the proc contents command section? dlist is imported from _basedict

    proc contents data=&base.._all_
        out=_basedict %if %sysevalf(%superq(select)^=,boolean) %then %do;
           (where=(upcase(memname) in(%do i = 1 %to %sysfunc(countw(%superq(select),%str( )));
                                          "%qupcase(%sysfunc(dequote(%qscan(%superq(select),&i,%str( )))))"
                                      %end;) ))                                            
        %end;
        noprint order=collate;
    proc contents data=&compare.._all_
        out=_compdict %if %sysevalf(%superq(select)^=,boolean) %then %do;
           (where=(upcase(memname) in(%do i = 1 %to %sysfunc(countw(%superq(select),%str( )));
                                          "%qupcase(%sysfunc(dequote(%qscan(%superq(select),&i,%str( )))))"
                                      %end;) ))
        %end;
        noprint order=collate;
    run;

Re: help with error in macro compare_all

lydiawawa — Wed, 16 Mar 2022 02:06:33 GMT

I think the problem is the datasets compared in the libraries has to have the same name.

Re: help with error in macro compare_all

SASKiwi — Wed, 16 Mar 2022 02:45:33 GMT

I think you are correct. You can easily test that by renaming the dataset names to match.