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;
Click image to register for webinar
