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
Create a data set
data have;Assign library and filename
set sashelp.class;
r_d = ranuni(9);
b_d = ranuni(10);
c_DD = ranuni(11);
run;
%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 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 has worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and Human Resource.
Can I use this by using like in where statement ?
ReplyDeletebut how to separate many strings from one observation and arranging under the same variable.
ReplyDeletehow to write a program to change DATASET-A to DATASET-B
ReplyDeleteDATASET -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
This comment has been removed by the author.
DeleteTry this:
Deletedata 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;
Code for dropping variables ending with '_d' or '_D'
ReplyDeleteproc 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;
How to import all files with the name starting with Sta with extension CSV.
ReplyDeleteNote: All files are present in same folder.