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;
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.