In this article we have seen 3 different ways to rename a sas dataset but they aren’t efficient when it comes to bulk renaming SAS Data sets.
Here you’ll learn how you can rename all the SAS datasets from a library ones for all without creating multiple copies of datasets. It means without making data copy you can just rename all the dataset names in one go!
Usually when it comes to renaming, you should have some pattern. You can’t rename datasets arbitrarily without any pattern using this method.
Let’s assume you want to add the prefix “new_” to all the dataset names. Here is the overview of what you want to achieve.
You have 5 data sets mentioned in the below table and you want new names with prefix “new_” for all the datasets in the work library.
| WORK library Datasets | → | New Dataset Names |
|---|---|---|
| work.Oslo | → | work.new_Oslo |
| work.Landon | → | work.new_Landon |
| work.Mumbai | → | work.new_Mumbai |
| work.NewYork | → | work.new_NewYork |
| work.LosAngeles | → | work.new_LosAngeles |
List Dataset Names from a SAS Library
Before you rename all the datasets from the desired SAS library you MUST first go through the list of the datasets which are available under that library. You can do this using the proc contents procedure.
/*create a few datasets in work library for the demo */
data work.Oslo work.Landon work.Mumbai work.NewYork work.LosAngeles;
input var1 var2 var3;
datalines;
1 4 76
2 3 49
2 3 85
4 5 88
2 2 90
;
run;
/* list data sets from WORK library */
proc datasets library=work memtype=data;
run;
Now you know that you only have 5 datasets in the work library and you want their name changed to its new name with the prefix “new_”.
Syntax:
Here is the syntax to change dataset names in bulk using CHANGE statement in proc datasets procedure.
proc datasets lib=libref;
change original_dataset1 = new_dataset1
original_dataset2 = new_dataset2
original_dataset3 = new_dataset3
;
run;
If you have 100 or thousands of datasets in the library then it’s not efficient to write names for all the datasets after the CHANGE statement.
To solve this problem you can create a new SAS macro variable that holds all the text after the CHANGE statement for all the datasets.
Define prefix to append to each dataset names
You can easily do this using dictionary.tables and cats function in proc sql procedure.
Here is the code:
/*define prefix to append to each dataset names*/
proc sql noprint;
select cats(memname, '=', 'new_', memname)
into :list
separated by ' '
from dictionary.tables
where libname = 'WORK';
quit;
%put list:--> &list;
Now with using this macro variable (&LIST) you can run proc datasets procedure with the CHANGE statement.
/*add prefix to each variable in dataset*/
proc datasets library=work;
change &list.;
quit;
You can verify using below code to see new dataset names.
/* view list */
proc datasets lib=WORK memtype=DATA;
run;
Please NOTE:
This method will work perfectly fine unless and until you have less number of data sets in the given library so that SAS macro variable can hold all the data dataset names. You should be aware that the SAS macro variable can hold up to a maximum 65,534 characters.
It means this method won’t work if you have a long list of datasets in the library. Only top datasets will be renamed that fall under 65, 534 characters.
There is another method to use to tackle this problem.
Solution: Rename all datasets in the SAS library
Now you know the limitation to use the previously mentioned method using sas macro variable.
In this method instead of creating macro variable, you can create a temporary sas dataset that holds a list of dataset names that comes after the CHANGE statement.
Here also you are going to use dictionary.tables and concatenation function in proc sql procedure to create a new dataset called as work.control.
/* create a new dataset work.control to hold new dataset names */
proc sql;
create table work.control as
/* replace 'new_' below with your prefixe*/
select memname, catt('new_', memname) as newname
from dictionary.tables
where libname='WORK';
quit;
/* view dataset */
proc print data=control;
run;
Now using the above dataset you can create a sas statement that renames all the datasets from the work library and executes it using the “call execute” function.
/* rename all the dataset names */
data _null_;
set work.control end=lastone;
if _n_=1 then
call execute('proc datasets library=work;');
call execute ('change '||memname||'='||newname||';');
if lastone then
call execute('quit;');
run;
CAUTION: If the length of the prefix plus original dataset name exceeds 32 characters then you have a problem. The SAS dataset name can’t be more than 32 characters. Hence make sure the newname of the dataset should not cross 32 characters.
Add Suffix and Rename all the Data sets in SAS
The process is exactly same as explained above for adding prefix. the only difference is adjusting variable places while using concatenation in the proc sql.
Here is the entire code:
/*define suffix to append to each dataset names*/
proc sql noprint;
select cats(memname, '=', memname, '_new')
into :list
separated by ' '
from dictionary.tables
where libname = 'WORK';
quit;
%put list:--> &list;
/* rename dataset names */
proc datasets lib=work;
change &list ;
run;
Alternate Method
You can use alternative yet more effective way to add suffix and rename all dataset names using call execute function.
/* create a new dataset work.control to hold new dataset names */
proc sql;
create table work.control as
/* replace 'new_' below with your prefixe*/
select memname, catt(memname, '_new') as newname
from dictionary.tables
where libname='WORK';
quit;
/* view dataset */
proc print data=control;
run;
/* rename all the dataset names */
data _null_;
set work.control end=lastone;
if _n_=1 then
call execute('proc datasets library=work;');
call execute ('change '||memname||'='||newname||';');
if lastone then
call execute('quit;');
run;
/* view list */
proc datasets lib=WORK memtype=DATA;
run;
Did you know how to rename variables in SAS?
FAQ
Yes. You can rename all the data sets within the library. Usually it’s based on some pattern such as adding prefix or suffix to dataset names.
You can use PROC DATASETS procedure with CHANGE statement.
When you add prefix to dataset names it means you’re renaming the dataset. You can add prefix or suffix to the dataset names. The PROC DATASETS procedure and CHANGE statement can be used rename the dataset names.