BookmarkSubscribeRSS Feed
forumsguy
Fluorite | Level 6

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;

1 REPLY 1
Peter_C
Rhodochrosite | Level 12

Have a look at the OUT2= option of PROC CONTENTS

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 862 views
  • 3 likes
  • 2 in conversation