Wildcard to delete variable with common suffix

[BayzidurRahman]

Is there any way to delete multiple variables that ends with a common suffix using a wildcard?
For example, I have variables: a_suf, b_suf...z_suf. I tried to drop them using drop :_suf; but it didn't work.

[ballardw]

If the variables are in adjacent positions in your data set you may be able to use the -- list builder. Run Proc Contents using the VARNUM option to display the variables in column order.

But you may need to show the code of what you attempted. SAS doesn't normally "delete" variables. It can DROP them from reading a data set or on writing to an output data set.

[Kurt_Bremser]

SELECT the variable names INTO a macro variable from DICTIONARY.COLUMNS (SEPARATED BY " ")  in PROC SQL, and use the macro variable in a DROP statement or DROP= dataset option.

proc sql noprint;
select name into :droplist separated by " "
from dictionary.columns
where libname = "WORK" and memname = "HAVE" and scan(upcase(name),-1,'_') = "SUF";
quit;

data want;
set have;
drop &droplist.;
run;

[yabwon]

You could use BasePlus package %getVars() macro:

Code:

data test;
a=1;
b=1;
z=1;
a_suf=1;
b_suf=1;
z_suf=1;
;
run;

%put %getVars(test, pattern = _suf$); /* regular expression for sufix "_suf" */

data test2;
  set test( drop=%getVars(test, pattern = _suf$) );
run;

Log:

1    data test;
2    a=1;
3    b=1;
4    z=1;
5    a_suf=1;
6    b_suf=1;
7    z_suf=1;
8    ;
9    run;

NOTE: The data set WORK.TEST has 1 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              393.90k
      OS Memory           23804.00k

10
11   %put %getVars(test, pattern = _suf$);
a_suf b_suf z_suf
12
13   data test2;
14     set test( drop=%getVars(test, pattern = _suf$) );
15   run;

NOTE: There were 1 observations read from the data set WORK.TEST.
NOTE: The data set WORK.TEST2 has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.01 seconds
      memory              568.00k
      OS Memory           23804.00k

Bart

Enable SAS Packages Framework and load BasePlus package:

filename packages "/your/path/to/packages";
%include packages(SPFinit.sas);

%loadPackage(BasePlus)

(details under The User section of readme.md)

[BayzidurRahman]

Thanks all of you for suggesting the codes and Macro. It seems that SAS doesn't allow the use of wildcard for suffix which is a shame. Other packages, such as Stata allows wildcard in both side of a keyword. For example, drop *_suf would delete all the variables that ends with _suf and drop pref_* will delete all the variables that starts with pref_. Is it worth requesting SAS to add this simple feature in its future release?

[yabwon]

Prefix dropping works pretty well:

Code:

data test;
a=1;
b=1;
z=1;
a_suf=1;
b_suf=1;
z_suf=1;
;
run;

data test2;
  set test( drop=a: b:);
run;

Log:

1    data test;
2    a=1;
3    b=1;
4    z=1;
5    a_suf=1;
6    b_suf=1;
7    z_suf=1;
8    ;
9    run;

NOTE: The data set WORK.TEST has 1 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.01 seconds
      memory              393.93k
      OS Memory           23804.00k

10
11   data test2;
12     set test( drop=a: b:);
13   run;

NOTE: There were 1 observations read from the data set WORK.TEST.
NOTE: The data set WORK.TEST2 has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              555.34k
      OS Memory           23804.00k

Bart

[Kurt_Bremser]

---

@BayzidurRahman wrote:

Is it worth requesting SAS to add this simple feature in its future release?

---

No. The colon on its own or as a prefix is already used in the SAS language (e.g. colon modifier in INPUT).

Experienced SAS coders always use prefixes, as they are aware of the limitation.

[yabwon]

One more approach with Proc Transpose trick:

Code:

data test;
a=1;
b=1;
z=1;
a_suf=1;
b_suf=1;
z_suf=1;
;
run;


proc transpose data=test(obs=0) out=temp(keep=_name_);
  var _all_;
run;

filename f TEMP;
data _null_;
  file f;
  put "data test2; set test( drop=";

  do until (EOF);
    set temp end=EOF;
    where _NAME_ like '%\_suf' ESCAPE "\";
    put _NAME_;
  end;

  put "); run;";
stop;
run;
%include f / source2;

Log:

1    data test;
2    a=1;
3    b=1;
4    z=1;
5    a_suf=1;
6    b_suf=1;
7    z_suf=1;
8    ;
9    run;

NOTE: The data set WORK.TEST has 1 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              391.65k
      OS Memory           23804.00k

10
11
12   proc transpose data=test(obs=0) out=temp(keep=_name_);
13     var _all_;
14   run;

NOTE: There were 0 observations read from the data set WORK.TEST.
NOTE: The data set WORK.TEMP has 6 observations and 1 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.01 seconds
      memory              2448.75k
      OS Memory           25856.00k

15
16   filename f TEMP;
17   data _null_;
18     file f;
19     put "data test2; set test( drop=";
20
21     do until (EOF);
22       set temp end=EOF;
23       where _NAME_ like '%\_suf' ESCAPE "\";
24       put _NAME_;
25     end;
26
27     put "); run;";
28   stop;
29   run;

NOTE: The file F is:
      Filename=*************************\#LN00610,
      RECFM=V,LRECL=32767,File Size (bytes)=0,

NOTE: 5 records were written to the file F.
      The minimum record length was 5.
      The maximum record length was 27.
NOTE: There were 3 observations read from the data set WORK.TEMP.
      WHERE _NAME_ like '%\_suf' escape '\';
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              494.25k
      OS Memory           23804.00k

30   %include f / source2;
NOTE: %INCLUDE (level 1) file F is file 
*************************\#LN00610.
31  +data test2; set test( drop=
32  +a_suf
33  +b_suf
34  +z_suf
35  +); run;

NOTE: There were 1 observations read from the data set WORK.TEST.
NOTE: The data set WORK.TEST2 has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              555.21k
      OS Memory           23804.00k

NOTE: %INCLUDE (level 1) ending

Bart