BookmarkSubscribeRSS Feed
BayzidurRahman
Obsidian | Level 7

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.

7 REPLIES 7
ballardw
Super User

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
Super User

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
Onyx | Level 15

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)

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



BayzidurRahman
Obsidian | Level 7

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
Onyx | Level 15

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

 

 

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Kurt_Bremser
Super User

@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
Onyx | Level 15

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

 

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 686 views
  • 2 likes
  • 4 in conversation