Hi there,
I am working on a macro, which is used to drop all variables with suffix "_dup". Now the problem is, how to put all variable names into this macro, since I have over 400 variables in this data set? Any ideas?
Thanks in advance!
I would have named the variables dup_XXXXX and then used
drop dup_: ;
But otherwise:
I have changed a piece of my code ..... to get the variable names in a dataset and the create another dataset changed accordingly.
* need to get a list of all variables and generate this code accordingly ;
proc contents data=DataSetName out=DatasetContents;
run;
data _null_ ; * No SAS data set is created ;
set DatasetContents end=lastobs;
file 'W:\SAS_Temp\AutoCode1.txt' ; * Output Text File ;
if _n_ =1 then do;
* for the start of the code ... ;
put 'data NewDataSetName ; ';
put ' set DataSetName; ';
put ' drop ';
end;
* check if the name contains _dup ;
if index(upcase(name),"_DUP")>0 then put ' name ' ;
if lastobs then do;
put ' ; ' ; * last variable ;
put 'run ; ' ;
end;
run ;
* run the code we just wrote ;
%include 'W:\SAS_Temp\AutoCode1.txt' ;
You can hit the dictionary or SASHELP tables that list the metadata for each table.
The comparisons are case sensitive, and you may want to browse through SASHELP.VColumns so you understand what it's doing.
proc sql noprint;
select memname into :var_list separated by " "
from sashelp.vcolumn where libname="WORK" and memname="MY_TABLE" and upcase(name) like %_DUP" ;
quit;
You're missing a double-quote there, Reeza, the sql command should end with
... and memname="MY_TABLE" and upcase(name) like "%_DUP" ;
Yes it should
proc sql noprint;
select memname into :var_list separated by " "
from sashelp.vcolumn where libname="WORK" and memname="MY_TABLE" and upcase(name) like "%_DUP" ;
quit;
Reeza,can you please explain why select memname and not select name ?
I believe it was a typo. It should be 'name'. In this case, 'memname' addresses name of the table, while 'name' contains the names of variables (columns).
Haikuo
I'll say it wasn't a typo
libname="WORK" finds all datasets in the proper directory in the dictionary table
memname="MY_TABLE" refers to the proper SAS data set in the dictionary table
upcase(name) like "%_DUP" refers to the column names in WORK.MY_TABLE and finds those that end with _DUP
There are two ways to get the list of variables in a dataset. Use PROC CONTENTS and output to dataset. Or query the SAS metadata DICTIONARY.COLUMNS (also available as the view SASHELP.VCOLUMN). Once you pick one of those for the source of the variable list then the easiest way to generate a DROP statement like that is to put the variable names into a macro variable using PROC SQL.
proc sql noprint ;
%let droplist=;
select name
into :droplist separated by ' '
from dictionary.columns
where libname='MYLIB'
and memname='MYDS'
and upcase(name) like '%^_DUP' escape '^'
;
quit;
If your macro is copying the data then you can use this list in DROP statement or DROP= dataset option.
data want;
set mylib.myds (drop=&droplist) ;
run;
Why do you use the ^ character in your where statement?
The LIKE operator uses underscore to match any single character. So like '%_DUP' would match 'A_DUP' and 'ABDUP'.
By escaping it you can search for the underscore character itself.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.