How to write a stand-alone script to remove indexes from archived datasets in SAS?

I want to create a stand-alone SAS-script which loops through all folders on archive disk and removes the indexes of each dataset. If needed I can have the script omit index removing on certain folders or datasets.

I have tried writing the script using macros but id didn't work

Seems simple enough. Just use the INDEX DELETE statement of PROC DATASETS.

You can use the DICTIONARY.INDEXES view to find the member names that have indexes to drive the code generation.

Let's make an example. First let's make a dataset with some indexes.

data class (index=(age sex both=(age sex)));
 set sashelp.class;
run;

Now get the list of all of the dataset in the library that have indexes.

proc sql;
create table indexes as 
select distinct
   libname 
  ,nliteral(memname) as memname length=51
from dictionary.indexes 
where libname='WORK'
order by 1,2
;
quit;

Now use that list to generate PROC DATASETS code and use %INCLUDE to run it.

filename code temp;
data _null_;
  file code;
  set indexes;
  by libname memname ;
  if first.libname then put 'proc datasets nolist lib=' libname ';' ;
  put '  modify ' memname ';'
    / '    index delete _all_;'
    / '  run;'
  ;
  if last.libname then put 'quit;' ;
run;
%include code / source2;

Results:

520  %include code / source2;
NOTE: %INCLUDE (level 1) file CODE is (system-specific pathname).
521 +proc datasets nolist lib=WORK ;
522 +  modify CLASS ;
523 +    index delete _all_;
NOTE: All indexes defined on WORK.CLASS.DATA have been deleted.
524 +  run;

NOTE: MODIFY was successful for WORK.CLASS.DATA.
525 +quit;

NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

To do it for a single directory just make a libref pointing to that directory and replace 'WORK' with the name of your libref.

To do it for a LOT of directories you could make a separated libref for each one and the modify the step that reads from DICTIONARY.INDEXES to select ALL of the librefs you have defined.

The code generation step does not need to change as it is driven by the contents of the dataset with the list of members with indexes.

For the case of the schema, embodied in a library, having foreign keys, the following example demonstrates the use of meta information DICTIONARY.REFERENTIAL_CONSTRAINTS and DICTIONARY.INDEXES in removing all indices from all data sets in the library.

proc sql;
create table work.one ( one_id num primary key);
create table work.two ( two_id num primary key
, one_id num references work.one 
);
quit;
data ones;
do one_id = 1 to 10 ;
output;
end;
run;
data twos;
do two_id = 1 to 100;
one_id = rand('integer',1,10);
output;
end;
run;
proc append base=one data=ones;
proc append base=two data=twos;
run;
* obtain names of foreign keys and data set they belong to;
proc sql;
create table fks as   
select * 
from dictionary.referential_constraints
where libname = 'WORK'
order by memname
;
* remove all foreign keys first;
data _null_;
if _n_ = 1 then 
call execute ('proc datasets nolist;');
do until (last.memname);
set fks;
where libname = 'WORK' ; * extra robust surety;
by memname;
if first.memname then 
call execute ('modify ' || trim(memname) || '; ic delete ');
call execute (trim(constraint_name));
end;
call execute (';run;');
run;
quit;
* obtain names of indices on data sets;
proc sql;
create table idxs as
select * 
from dictionary.indexes
where libname = 'WORK'
order by memname
;
* remove all indices from indexed data sets;
data _null_;
if _n_ = 1 then 
call execute ('proc datasets nolist;');
do until (last.memname);
set idxs;
where libname = 'WORK' ; * extra robust surety;
by memname;
if first.memname then 
call execute ('modify ' || trim(memname) || '; ic delete _all_;');
end;
run;
quit;