altering over 400 tables clustered in DI (SPDS tables)

[Mark7]

Hi,

I have over 400 tables named similair E_PROD_2002_01, E_PROD_2002_02, .... E_PROD_2002_20, etc.  I need to alter a column in every table, to change length char from 10 to 15. I don't want to run every single code:

 proc sql;
alter table dds.E_PROD_2002_01
modify name char(15);
quit;

Is there an easier way?

[PaigeMiller · Accepted solution]

A macro loop would work here. Since you state there are 400 such tables, but only show 20 table names, and I do not know what the full 400 data set names would look like, I provide code for those 20 tables. (Macro will not work for 400 without modification)

UNTESTED CODE

%macro dothis;
    %do i =1 to 20;
        proc sql;
            alter table dds.E_PROD_2002_%sysfunc(putn(&i,z2.))
            modify name char(15);
        quit;
    %end;
%mend;
%dothis

[Mark7]

Thank you! It works! i had to add %to

%do i =1  %to 20;

[Mark7]

Works great, thank you