Go through all datasets in a directory and delete columns that contain a specific text

[Tom]

Do NOT use CALL EXECUTE to call macros that use generated SAS code to generate macro variables that they same macro then uses UNLESS you add %NRSTR() around the macro call.
Otherwise the timing of creating the macro variable and referencing it will be off. The SAS code the macro generates will be output when the CALL EXECUTE() runs but that SAS code will not execute until after the data step finishes.

So if you run the code once and created global macro variables DROPME and SQLOBS and

%let dropme=var1 var2;
%let sqlobs=2;

then run this data step:

data _null_;
  call execute('%drop(AAA)');
  call execute('%drop(BBB)');
run;

Then  no matter what variables are in AAA or BBB because SQLOBS is non zero NOW what will be pushed onto the stack to run will be SAS code like this:

proc sql .... select name into :dropme ....
where memname="AAA"....
quit;
data test;
  set temp.AAA;
  drop var1 var2;
run;
proc sql .... select name into :dropme ....
where memname="BBB"....
quit;
data test;
  set temp.BBB;
  drop var1 var2;
run;

Yet another reason NOT to use CALL EXECUTE().

If you must use it then add %NRSTR() like so:

  call execute('%nrstr(%drop)(AAA)');

Then what is pushed onto the stack to run after the data step is instead the actual call to the macro.

%drop(AAA)

Which will now work since the SQL code will run BEFORE the decision is mode for who to generate the DROP statement instead of after the DROP statement has already been generated.

[saslove]

Thanks for the tip. Could you guide me through how I would apply this step to all datasets in my directory. If I don't use call execute, what would I do here instead? 

%macro drop (dn);
%let dropme=;
proc sql noprint;
SELECT name INTO :dropMe SEPARATED BY ' '
FROM sashelp.vcolumn
WHERE libname = 'TEMP' AND
memname = upcase("&dn") AND
INDEX(upcase(NAME),'DTC')>0;
quit;

%put &sqlobs;
%put &dropme;

%if &sqlobs > 0 %then %do;
data test;
set temp.&dn;
drop &dropme;
run;
%end;

%mend drop;

%drop (bma);

proc contents data=temp._all_ noprint out=contents;
run;

data _null_;
set contents;
by memname ;
where index(upcase(name),'DTC') > 0;
call execute(cats('%drop(',memname,')'));
run;

[Tom]

If you keep the macro that way then you need to keep the FIRST.MEMNAME subset or else you will call the macro once for every variable you want to drop, but only the first call is needed since it will drop all of the variables for that table.

You didn't add the %NRSTR() to fix the timing issue with using CALL EXECUTE().

data _null_;
  set contents;
  by memname ;
  where index(upcase(name),'DTC') ;
  if first.memname;
  call execute(cats('%nrstr(%drop)(',memname,')'));
run;

[Patrick · Accepted solution]

@saslove 

Here an approach using call execute().

/* create sample data */
data 
  ds1(drop=varDTC01 DTC _dTc_xx)
  ds2(keep=name sex aaDtXc_06)
  ds3
  ;
  length varDTC01 DTC _dTc_xx aaDtXc_06 abc_dtc abcDtcX $1;
  if 0 then call missing(of _all_);
  set sashelp.class;
run;

/* define parameters */
%let libref=work;
%let col_pattern=DTC;

/* 
  use call execute to drop a list of columns dynamically populated into SAS variable DropList
*/
data _null_;
  set sashelp.vcolumn(keep=libname memname name where=(libname=%upcase("&libref") and find(name,"&col_pattern",'i')>0));
  by libname memname;
  length DropList $32767;
  retain DropList;
  DropList=catx(',',DropList,name);
  if last.memname then
    do;
      call execute(catx(' ', 'proc sql; alter table', cats(libname,'.',memname),'drop', DropList, ';quit;'));
      call missing(DropList);
    end;
run;

Fixed as per @FreelanceReinh's comment.

[FreelanceReinh]

Minor correction:

DropList=catx(',', DropList, name);

[saslove]

@Patrick 

Thank you. I was able to use this logic and apply my code to all datasets. It works great. Thanks very much for the assistance. 

Posting the code here for reference:

/*Dropping all date variables*/
/* define parameters */
%let libref=temp;
%let col_pattern=DTC;

/*
use call execute to drop a list of columns dynamically populated into SAS variable DropList
*/
data _null_;
set sashelp.vcolumn(keep=libname memname name where=(libname=%upcase("&libref") and find(name,"&col_pattern",'i')>0));
by libname memname;
length DropList $32767;
retain DropList;
DropList=catx(',',DropList,name);
if last.memname then
do;
call execute(catx(' ', 'proc sql; alter table', cats(libname,'.',memname),'drop', DropList, ';quit;'));
call missing(DropList);
end;
run;

[Patrick]

@saslove 

Below a slightly different coding approach but fully working code for the generated sample data.

/* create sample data */
data 
  ds1(drop=varDTC01 DTC _dTc_xx)
  ds2(keep=name sex aaDtXc_06)
  ds3
  ;
  length varDTC01 DTC _dTc_xx aaDtXc_06 abc_dtc abcDtcX $1;
  if 0 then call missing(of _all_);
  set sashelp.class;
run;

/* define parameters */
%let libref=work;
%let col_pattern=DTC;

/* 
  generate SAS code for dropping columns 
  write generated code to temporary file codegen 
*/
filename codegen temp;
data _null_;
/*  file print;*/
  file codegen;
  set sashelp.vcolumn(keep=libname memname name where=(libname=%upcase("&libref") and find(name,"&col_pattern",'i')>0));
  by libname memname;
  if first.memname then
    do;
      put 
        / 'proc sql;'
        / '  alter table ' libname +(-1) '.' memname
        / '    drop ' name
        @;
    end;
  else
    do;
      put ',' name @@;
    end;
  if last.memname then
    do;
      put
        / '  ;'
        / 'quit;'
        ;
    end;
run;

/* ensure file codegen gets create even if above step didn't select any rows */
data _null_;
  file codegen mod;
/*  put;*/
  stop;
run;

/* execute generated code */
%include codegen / source2;

On a side note: A data step with a keep statement recreates your tables and you would loose any indexes and constraints (if any). A SQL ALTER TABLE drops the columns from your source table and though will not change anything else. 

For testing: Uncomment FILE PRINT but comment FILE CODEGEN; and %INCLUDE .... This will print the generated code without executing it.