topic altering over 400 tables clustered in DI (SPDS tables) in SAS Programming

altering over 400 tables clustered in DI (SPDS tables)

Mark7 — Thu, 20 Apr 2023 12:18:25 GMT

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?

Re: altering over 400 tables clustered in DI (SPDS tables)

PaigeMiller — Thu, 20 Apr 2023 12:48:20 GMT

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

Re: altering over 400 tables clustered in DI (SPDS tables)

Mark7 — Thu, 20 Apr 2023 13:05:12 GMT

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

%do i =1 %to 20;

Re: altering over 400 tables clustered in DI (SPDS tables)

Mark7 — Fri, 21 Apr 2023 06:07:25 GMT

Works great, thank you