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;
Assign library and filename
%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;
Related Posts
About Author:

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

5 Responses to "SAS : Dropping variables ending with a specific string"
  1. Can I use this by using like in where statement ?

    ReplyDelete
  2. but how to separate many strings from one observation and arranging under the same variable.

    ReplyDelete
  3. 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

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Try this:

      data A;
      input ID COMMENTS $30. ;
      Datalines4;
      1 Com1;com2;com3;com4;com5
      2 Com1;com2
      3 Com1;com2;com3
      ;;;;

      data x(drop = i y comments);
      set a;
      y= countw(comments);
      do i=1 to y;
      id= id;
      comments_new = scan(comments,i);
      output;
      end;

      run;

      Delete

Next → ← Prev
Love this Post? Spread the Word!
Share