topic Re: Go through all datasets in a directory and delete columns that contain a specific text in SAS Programming

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

saslove — Fri, 10 May 2019 18:42:58 GMT

I have about 50 datasets in my directory. This varies by study, so I need to apply the change I am asking to all the datasets in the directory.

I want to delete columns that contain a string with "DTC" in it. The number of columns that contain this string could also vary. It's not consistent. There could be text before and after it, but what I want is all columns that contain "DTC" to be deleted.

So, it is a two step process:

1. In each dataset, extract all "DTC" containing columns. (I know I can use index or prxmatch)

2. Delete it from all datasets in the directory (this holds on to the previous list of drop variables - so unable to achieve this).

How do I go about this? Please let me know the best way.

Thank you!

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

Tom — Fri, 10 May 2019 19:02:37 GMT

It is not clear what you are trying to do:

Are you asking to drop variables whose NAME contain the string 'DTC'?

Or set the value of variables to missing where the variable value contains string 'DTC'?

Or drop variables where at least one value contains the string 'DTC'?

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

Reeza — Fri, 10 May 2019 19:27:34 GMT

SASHELP.VCOLUMN has the list of data sets in your library and all the variable names and types.

You can use that to pull the table names in automatically.

It's not clear if you're dropping columns or rows.

If rows, you likely want to use FIND() to find the substr

_CHARACTER_ will refer to all character variables.

If it's columns you can filter than via a SQL query and then use PROC DATASETS to drop the columns - that's the most efficient way IMO.

Get it working for one data set first and then you can generalize.

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

saslove — Fri, 10 May 2019 19:39:46 GMT

Thanks Reeza. I tried a similar approach. The problem happening is that when I use it in a macro loop to perform the operation for all datasets in the directory, the "&DROPME" list holds on to the previous dataset's variables that were executed.

proc sql noprint;
SELECT name INTO :dropMe SEPARATED BY ' '
FROM sashelp.vcolumn
WHERE libname = 'TEMP' AND
memname = 'TOX' AND
INDEX(NAME,'DTC')>0;
quit;

data test;
set temp.tox;
drop &dropme;
run;

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

Tom — Fri, 10 May 2019 19:45:02 GMT

When the SQL query does not produce any observations the target of the INTO clause is not modified. Set if empty BEFORE the query. Or check the automatic macro variable SQLOBS to see if any were found.

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

Reeza — Fri, 10 May 2019 20:48:29 GMT

1. set Dropme to missing before theSQL query - if you don't reset it, it keeps historical values. Good practice anytime.

2. You may not have any variables with DTC - you may also need to account for case, right now a variable named random_dtc would not be found.

3. You can use %IF/%THEN to conditionally execute the drop. I would advise using PROC DATASETS its faster and you'll be doing this over several datasets so it's worth the time.

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

saslove — Fri, 10 May 2019 20:58:45 GMT

Thanks Tom and Reeza.

This is the approach I am using in the macro.

How would I do the same with proc datasets.

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

%mend drop;

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

data _null_;
set contents;
by memname ;
if first.memname;
call execute(cats('%drop(',memname,')'));
run;

Thanks,

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

Tom — Fri, 10 May 2019 21:40:29 GMT

Nothing in that code is dropping any columns from any datasets.

First thing to do is make sure you have a copy of the original datasets before you start dropping columns.

Second thing to do is make sure the first thing actually worked.

To solve this problem I would just take the output of PROC CONTENTS and use it to generate the PROC DATASETS code.

I like to generate code to a file so that I can open the file and look at it and test it before making the final version that actually runs it.

Much easier to debug that either macros or CALL EXECUTE steps.

So use something like this:

proc contents data=temp._all_ noprint out=contents;
run;
 
filename code temp;
data _null_;
  set contents;
  by memname ;
  where index(upcase(name),'DTC');
  file code;
  if first.memname then put 'modify ' memname ';' ;
  put 'drop ' name ';';
  if last.memname then put 'run;';
run;

proc dataset nolist lib=temp;
%include code / source2;
quit;

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

Astounding — Fri, 10 May 2019 21:51:15 GMT

Is this the actual code, or does it just resemble the code? Issues that stand out:

You reference a macro variable in single quotes: '&dn' That can't work. You need double quotes: "&dn"
Variable names in sashelp.vcolumn are not necessarily in upper case. Switch to: index(upcase(name), 'DTC') > 0
Your final macro is incomplete. Once the corrections are made, it would find a list of variable names, but wouldn't drop anything. At the end of the macro (after the final QUIT statement, you need to add something like:

%if &sqlobs > 0 %then %do;

DATA step that drops variables goes here

%end;

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

saslove — Fri, 10 May 2019 21:55:52 GMT

I tried it the way you suggested, but I get this error

The DROP and KEEP statements are not supported in procedure steps in this release of the
SAS System. Therefore, these statements are ignored.

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

saslove — Fri, 10 May 2019 22:03:39 GMT

That was only an example of what I am trying to achieve and not the actual macro.

I tried this way, but it still holds on to the pre-resolved macro.

%macro drop (&dn);
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;

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

%mend drop;

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

data _null_;
set contents;
by memname ;
if first.memname;
call execute(cats('%drop(',memname,')'));
run;

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

Astounding — Fri, 10 May 2019 22:09:56 GMT

OK, let's make an earlier modification. Now, you are using:

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

 

data _null_;
set contents;
by memname ;
if first.memname;
call execute(cats('%drop(',memname,')'));
run;

Let's subset earlier, so the "drop" logic encounters data sets that must contain at least one DTC variable:

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

 

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

That can only help, even after the issues are diagnosed and resolved. And it can clarify what the issues are.

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

saslove — Fri, 10 May 2019 22:15:05 GMT

@Astounding It's still holding to the earlier datasets DTC variables. The DTC variable name changes with each dataset.

%macro drop (dn);
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;

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

%mend drop;

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

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

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

Astounding — Fri, 10 May 2019 22:29:32 GMT

OK, time to add some temporary diagnostics, then. Inside the macro definition:

%let dropme=;

This should be the first statement within the macro.

%put &sqlobs;
%put &dropme;

This goes right after the SQL code, before checking %if &sqlobs > 0.

Also, my recollection is that MEMNAME is all caps within sashelp.vcolumn. But if I'm wrong about that, the comparison should be:

upcase(memname) = upcase("&dn") AND

If this doesn't do it, sorry to say I'm done for the evening. I'm heading out the door. Hope this gives you enough to go on.

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

saslove — Fri, 10 May 2019 22:31:59 GMT

The macro works fine if I try it on individual datasets.

It gives me exactly what I need when I do a manual run on datasets

%drop (bma); >> works great

%drop (tox); >> works great

It's only when I try the data _null_ and apply at once to all datasets, it breaks. It says no drop variables found.

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

Tom — Fri, 10 May 2019 23:09:54 GMT

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.

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

saslove — Fri, 10 May 2019 23:08:02 GMT

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;

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

Tom — Sat, 11 May 2019 16:14:23 GMT

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;

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

Patrick — Sat, 11 May 2019 03:48:09 GMT

@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.

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

Patrick — Sun, 12 May 2019 02:10:55 GMT

@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.