Say I have two dataset with the same number of variables, but one of them has correct variable names while the other has auto generated names. Say dataset1 has name, age, sex, and data2 has var1, var2, var3, which I want to rename to be like dataset1. What is a macro that can automate the renaming? I would like them to align by variable order from left to right, rather than by alphabetical order.
Thanks.
Are the orders of the variables the same in each data set? And are the types the same?
That is, is name and var1 both the first variable, etc., and the same type (num or char?).
If so, I'd do this:
proc sql;
CREATE TABLE template LIKE data_set_with_var_names;
CREATE TABLE want AS
SELECT * FROM template
UNION ALL
SELECT * FROM data_set_with_generic_var_names;
quit;
Are the orders of the variables the same in each data set? And are the types the same?
That is, is name and var1 both the first variable, etc., and the same type (num or char?).
If so, I'd do this:
proc sql;
CREATE TABLE template LIKE data_set_with_var_names;
CREATE TABLE want AS
SELECT * FROM template
UNION ALL
SELECT * FROM data_set_with_generic_var_names;
quit;
I would consider SQL insert which operates on the order of the variables.
You could select no records from first dataset but it would keep the names and the second would provide the data.
The alternative is to dynamically generate a rename statement using SASHELP.VCOLUMN
Here's an example:
See the example above that essentially does that in the next two steps.
I think you need a different approach though, select the names from the sashelp datasets for each of your datasets.
Merge them by variable number - this is on the SASHELP dataset.
Then dynamically create a rename statement similar to the example.
I don't have time to write the code here, but I know for a fact that I've written this exact algorithm on this forum within the last year.
And/or on StackOverflow.
data have1;
set sashelp.class;
keep name age sex;
run;
data have2;
var1='xx';var2='x';var3=43;
run;
proc transpose data=have1(obs=0) out=temp1;
var _all_;
run;
proc transpose data=have2(obs=0) out=temp2;
var _all_;
run;
data temp;
merge temp1(rename=(_name_=name)) temp2;
run;
data _null_;
set temp end=last;
if _n_=1 then
call execute('proc datasets library=work nolist nodetails;modify have2;rename ');
call execute(catx('=',_name_,name));
if last then call execute(';quit;');
run;
Below two options how you could approach this.
The first option is what @collinelliot already proposed and what I'd consider the most straightforward approach with the least coding. This option fully replaces the variable attributes of the 2nd data set (not only the names but also the formats, informats, length etc.)
The second option has the advantage that it only changes variable names without processing the data and without re-creating the table. This option is preferrable if you're dealing with a big table or if you have indices and the like defined on the table.
This option only renames the variables; all other variable attributes remain unchanged.
data class_vars;
set sashelp.class;
var1=name;
var2=sex;
var3=age;
var4=height;
var5=weight;
drop name sex age height weight;
run;
/* option 1: append data sets using variable definitions from first ds in list */
/* - use 'where 0' clause in case first ds only used for variable mapping */
proc sql;
create table want as
select *
from sashelp.class
/* where 0*/
union all
select *
from class_vars
;
quit;
/* option 2: create rename string, then apply on dataset */
/* - this approach doesn't recreate the dataset and though doesn't process the rows */
proc sql noprint;
select cats(b.name,'=',a.name) into :rename_list separated by ' '
from
dictionary.columns a
inner join
dictionary.columns b
on
a.libname='SASHELP' and a.memname='CLASS'
and b.libname='WORK' and b.memname='CLASS_VARS'
and a.varnum=b.varnum
;
quit;
%put &=rename_list;
proc datasets lib=work nolist;
modify class_vars;
rename &rename_list;
run;
quit;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.