SAS : Dropping variables ending with a specific string

Suppose you need to drop all the variables ending with a specific string from your dataset. For example. you have a dataset named 'Have' and you want to remove all the variables ending with '_d' or '_D'.

Create a data set

data have;
set sashelp.class;
r_d = ranuni(9);
b_d = ranuni(10);
c_DD = ranuni(11);
run;

Now we have the following variables in our dataset named "have".

Age

Height

Name

Sex

Weight

b_d

c_DD

r_d

Solution

The purpose of the code is to remove variables from the "have" dataset that match the pattern "_D" at the end of their names.

proc contents data=have out=t(keep=name);
run;

proc sql;
select distinct name into : n1 separated by ' ' from t
where upcase(name) like '%_D';
quit;

%put &n1;

data fnl;
set have;
drop &n1;
run;

The code above performs the following steps:

Extracts variable names from the "have" dataset and stores them in a temporary dataset "t".
Selects distinct variable names that end with "_D" from "t" and stores them in the macro variable "n1".
Creates a new dataset "fnl" by copying data from "have" and drops the variables listed in the macro variable "n1" from the new dataset.

SAS Macro

The following SAS Macro automates all the steps performed in previous section. You just need to input your SAS dataset name along with library.

Assign library and dataset name

%let libname= work.have;

SAS Code : Dropping variables ending with '_d' or '_D'

*Extracting library name;
*Extracting dataset name;
*Upcase all macro variables to have consistency;

data _null_;
call symput ("library", put(upcase(substr("&libname",1,index("&libname",'.')-1)), $8.));
call symput ("datset", put(upcase(substr("&libname",index("&libname",'.')+1,length("&libname"))), $32.));
%put &library &datset;
run;

*Get variable list;
proc sql noprint;
select name into : var_list separated by " "
from dictionary.columns
where LIBNAME = "&library"
and MEMNAME = "&datset"
and upcase(substr(name,length(name)-1,2)) = '_D';
quit;

*Dropping all the variables ending with '_D';
data &libname.1;
set &libname. (drop=&var_list);
run;

About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

While I love having friends who agree, I only learn from those who don't
Let's Get Connected Email LinkedIn

Post Comment 7 Responses to "SAS : Dropping variables ending with a specific string"

UnknownNovember 24, 2018 at 8:54 AM
Can I use this by using like in where statement ?
UnknownJanuary 29, 2019 at 4:05 AM
but how to separate many strings from one observation and arranging under the same variable.
UnknownJanuary 30, 2019 at 1:45 AM
how to write a program to change DATASET-A to DATASET-B
DATASET -A

ID COMMENTS
1 Com1;com2;com3;com4;com5;
2 Com1;com2
3 Com1;com2;com3

DATASET-B

ID COMMENTS
1 Com1
1 Com2
1 Com3
1 Com4
1 Com5
2 Com1
2 Com2
3 Com1
3 Com2
3 Com3
UnknownJanuary 22, 2020 at 8:32 AM
Code for dropping variables ending with '_d' or '_D'

proc contents data=have out=t(keep=name);
run;

proc sql;
select distinct name into : n1 separated by ' ' from t
where upcase(name) like '%_D';
quit;

%put &n1;

data fnl;
set have;
drop &n1;
run;
ammuMay 13, 2020 at 11:01 PM
How to import all files with the name starting with Sta with extension CSV.
Note: All files are present in same folder.
ShirishaOctober 6, 2024 at 11:09 PM
without sql, this can be done I guessdata have;
set sashelp.class;
r_d = ranuni(9);
b_d = ranuni(10);
c_DD = ranuni(11);
run;

proc transpose data=have out=h1;
var _all_;
run;

data h2;
set h1;
where _NAME_ not like '%_d';
run;

proc transpose data=h2 out=h3(drop=_name_ _label_);
var _all_;
run ;