DATA Step, Macro, Functions and more

compare indexes of 2 tables

Reply
Frequent Contributor
Posts: 85

compare indexes of 2 tables

Hi all, we have a macro to create physical tables from DDLs which takes care of creation of tables and if sometimes change request comes to add columns or change formats it takes care of everything and produce compare result output ... now my problem is with indexes ..suppose i have table with 2 indexes ..next time request comes to add one more index on same table then my macro will do so but it will not track the changes made in indexes... Please see below a piece of macro code for comparison... Any help is really appreciated...

%macro compare();

proc contents data=&libref..&tbname out=test noprint;

run;

proc sort data=test;

by varnum;

quit;

data test2(keep=format formatl formatd sasformat sascolumntype sascolumnlength name label);

          set test;

                    if type=2 then do;

                              if substr(format,1,1) = "$" then do;

                                        if formatl = 0 then do;

                                                  sasformat=compress(format!!".");

                                        end;

                                        if formatl ne 0 then do;

                                                  sasformat=compress(format!!formatl!!".");

                                        end;

                              end;

                              sascolumntype="C";

                    end;

                    else do;

                              if format eq "DDMMYY" or (format eq "DATETIME" and formatd = 0) then do;

                                        sasformat=compress(format!!formatl!!".");

                              end;

                              else do;

                                        if formatl = 0 and formatd = 0 then do;

                                                  sasformat=compress(format!!".");

                                        end;

                                        else do;

                                                  sasformat=compress(format!!formatl!!"."!!formatd);

                                        end;

                              end;

                              sascolumntype="N";

                    end;

                    sascolumnlength=length;

run;

proc sql noprint;

create table comp as select sascolumnlength,sascolumntype,sasformat,colname,label from create_tb;

quit;

proc sql noprint;

          create table compared_tables as select ds1.name as colname,ds2.colname as comp_colname,

                    ds1.label,ds2.label as comp_label,ds1.sascolumnlength,ds2.sascolumnlength as comp_length,

                    ds1.sascolumntype,ds2.sascolumntype as comp_sascolumntype,ds1.sasformat,ds2.sasformat as comp_sasformat

                    from test2 ds1 full join comp ds2 on

                    ds1.name=ds2.colname;

quit;

%mend;

Valued Guide
Posts: 2,175

compare indexes of 2 tables

Have a look at the OUT2= option of PROC CONTENTS

Ask a Question
Discussion stats
  • 1 reply
  • 131 views
  • 3 likes
  • 2 in conversation