delete col if exist

[Ronein]

Hello

I want to delete (DROP) columns if they exist.

For example: I want to drop columns make and model if they exist.

data cars;
set sashelp.cars;
drop make;
Run;

[andreas_lds]

Do you want to remove the column from an existing dataset or do you want to clone a dataset skipping one variable?

[Ronein]

In existing data set delete the col if it exist

[Kurt_Bremser]

Query DICTIONARY.COLUMNS with a suitable WHERE clause in a SELECT INTO to create the variable list which you can then use in a DROP statement or DROP= dataset option.

[Kathryn_SAS]

Here is a macro example that allows you to check if a variable exists in a data set:

https://support.sas.com/kb/25/082.html

If the variable does not exist, the value of &val is 0. Otherwise, the value is the variable's position in the data set.

[Quentin · Accepted solution]

If you set system option drkrocond=nowarn, you can list the variables that don't exist on the DROP statement, and they will be ignored.

1    option dkrocond=nowarn ;
2
3    data cars ;
4      set sashelp.cars ;
5      drop make notthere;
6    run ;

NOTE: There were 428 observations read from the data set SASHELP.CARS.
NOTE: The data set WORK.CARS has 428 observations and 14 variables.

7
8    option dkrocond=warn

[yabwon]

@Quentin 

macro version, no need for options change in main session thanks to DoSubL() 🙂

%macro dropVariableIfExistQ(
  ds /* name of data set with variables to drop */
, vars /* space separated list of variables to drop */
, out=work.want /* name of result data set */
);
  %local rc;
  %let rc = %sysfunc(doSubL(%str(
    options nomprint nosymbolgen nomlogic msglevel=N nosource ps=min notes;
    option dkrocond=nowarn;

    data &out.;
      set &ds.;
      drop
        &vars.
      ;
      run;
  )));
%mend dropVariableIfExistQ;

options mprint;
%dropVariableIfExistQ(sashelp.cars, make model)

%put ##%dropVariableIfExistQ(sashelp.cars, NOmake NOmodel, out=work.want2)##;

Bart

[yabwon]

@Quentin , expanding your idea a little bit:

%macro bpUTiL_cleanDropKeep(
  /* idea by Quentin McMullen */
  ds /* name of data set with variables to drop */
, drop= /* space separated list of variables to drop */
, keep= /* space separated list of variables to keep */
, out=work.want /* name of result data set */
);
  %local rc;
  %put NOTE-;
  %let rc = %sysfunc(doSubL(%nrstr(
    options nomprint nosymbolgen nomlogic msglevel=N nosource ps=min notes;
    option dkrocond=nowarn;
    data &out.;
      set &ds.;
      %sysfunc(ifc(%sysevalf(%superq(drop) NE ,boolean)=1, %str(drop &drop.), %str( ), ));
      %sysfunc(ifc(%sysevalf(%superq(keep) NE ,boolean)=1, %str(keep &keep.), %str( ), ));
      run;
  )));
  %put NOTE-;
%mend bpUTiL_cleanDropKeep;

options mprint;
%bpUTiL_cleanDropKeep(sashelp.cars, drop=make model)

%put ##%bpUTiL_cleanDropKeep(sashelp.cars, keep=NOmake NOmodel, out=work.want2)##;

I think I'll add it to bpUTiL package 🙂

[EDIT:] I've added Rename to have it all:

%macro bpUTiL_cleanDropKeep(
  /* idea by Quentin McMullen */
  ds /* name of data set with variables to drop */
, drop= /* space separated list of variables to drop */
, keep= /* space separated list of variables to keep */
, rename= /* list of variables pairs to rename */
, out=work.want /* name of result data set */
);
  %local rc;
  %put NOTE-;
  %let rc = %sysfunc(doSubL(%nrstr(
    options nomprint nosymbolgen nomlogic msglevel=N nosource ps=min notes;
    option dkrocond=nowarn;
    data &out.;
      set &ds.;
      %sysfunc(ifc(%sysevalf(%superq(drop) NE ,boolean)=1, %str(drop &drop.), , ));
      %sysfunc(ifc(%sysevalf(%superq(keep) NE ,boolean)=1, %str(keep &keep.), , ));
      %sysfunc(ifc(%sysevalf(%superq(rename) NE ,boolean)=1, %str(rename &rename.), , ));
      run;
  )));
  %put NOTE-;
%mend bpUTiL_cleanDropKeep;

options mprint;
%bpUTiL_cleanDropKeep(sashelp.cars, drop=make model)

%put ##%bpUTiL_cleanDropKeep(sashelp.cars, keep=NOmake NOmodel, out=work.want2)##;

%bpUTiL_cleanDropKip(sashelp.cars,keep=make ABC, rename=ABC=EFG make=make_make_make)

Bart

[Quentin]

Nice, @yabwon .  

I actually picked up the idea of using DKROCOND that way from SAS guru Ed Heaton, who wrote about it here:

https://support.sas.com/resources/papers/proceedings/proceedings/sugi28/067-28.pdf

As mentioned in the paper, he was a fan of putting drop=_:  in every DATA step he wrote.

[yabwon]

Small macro, just for fun;

%macro dropVariableIfExist(
  ds /* name of data set with variables to drop */
, vars /* space separated list of variables to drop */
, out=work.want /* name of result data set */
);
  %local sentinel rc;
  %let sentinel=_sentinel_%sysfunc(datetime(),b8601dt15.)_;
  %let rc = %sysfunc(doSubL(%str(
    options nomprint nosymbolgen nomlogic msglevel=N nosource ps=min nonotes;
    data _null_;
      &sentinel._1_=.;
      if 0 then set &ds.;
      &sentinel._2_=" ";

      array _numeric_(&sentinel._I_) _numeric_;
      array _character_(&sentinel._I_) _character_;

      length &sentinel._3_ $ 32767;
      &sentinel._3_=" ";

      length &sentinel._K_ $ 32;
      &sentinel._K_ = "*";
      declare hash &sentinel._H_();
      &sentinel._H_.defineKey("&sentinel._K_");
      &sentinel._H_.defineDone();

      do _N_ = 1 by 1 while(&sentinel._K_ NE " ");
        &sentinel._K_ = upcase(scan(symget("vars"),_N_," "));
        &sentinel._H_.REPLACE(KEY:&sentinel._K_
                             ,DATA:&sentinel._K_
                             );
      end;

      do over _numeric_;
        if 0=&sentinel._H_.CHECK(KEY:upcase(strip(vname(_numeric_)))) then 
          &sentinel._3_=catX(" ", &sentinel._3_, vname(_numeric_)); 
      end;
      do over _character_;
        if 0=&sentinel._H_.CHECK(KEY:upcase(strip(vname(_character_)))) then 
          &sentinel._3_=catX(" ", &sentinel._3_, vname(_character_)); 
      end;

      
      if &sentinel._3_ NE " " then
        do;
          put "INFO: The following variables: " &sentinel._3_ "will be dropped.";
          call execute("options notes source; data " !! symget("out") !! ";");
          call execute(" set "  !! symget("ds")  !! ";");
          call execute(" drop ");
          call execute(&sentinel._3_);
          call execute("; run;");
        end;
      else put "INFO: No variables to drop.";
      /* put _ALL_; */
      stop;
    run;
  )));
%mend dropVariableIfExist;

options mprint;
%dropVariableIfExist(sashelp.cars, make model)

%put ##%dropVariableIfExist(sashelp.cars, NOmake NOmodel, out=work.want2)##;

It runs as pure macrocode 🙂

Bart