Rename one dataset with another

Accepted Solution Solved
Reply
Contributor
Posts: 73
Accepted Solution

Rename one dataset with another

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.


Accepted Solutions
Solution
‎06-18-2017 11:51 PM
PROC Star
Posts: 307

Re: Rename one dataset with another

Posted in reply to apolitical

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;

View solution in original post


All Replies
Solution
‎06-18-2017 11:51 PM
PROC Star
Posts: 307

Re: Rename one dataset with another

Posted in reply to apolitical

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;
Contributor
Posts: 73

Re: Rename one dataset with another

Posted in reply to collinelliot
the simplest solution that meets my need. thank you.
Super User
Posts: 19,817

Re: Rename one dataset with another

Posted in reply to apolitical

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:

 

https://gist.github.com/statgeek/82d9f2854edc01560e0f

Contributor
Posts: 73

Re: Rename one dataset with another

PROC SQL with insert is what I have in mind. I can only make a text string with all variable names separated by a space, something like
proc sql;
select name into: vlist separated by ' '
from dictionary.columns
where memname=upcase("dataset1") ;
quit;

So I now have a string "name age sex", This will sound really dumb, but how do I use a loop to grab each of those words and put that into the rename statement, say under a data step?
Super User
Posts: 19,817

Re: Rename one dataset with another

Posted in reply to apolitical

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. 

Super User
Posts: 10,035

Re: Rename one dataset with another

Posted in reply to apolitical
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;
Respected Advisor
Posts: 4,173

Re: Rename one dataset with another

[ Edited ]
Posted in reply to apolitical

@apolitical

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;

 

Contributor
Posts: 73

Re: Rename one dataset with another

your option 2 of constructing a list of "oldvar=newvar" texts was what i originally had in mind but could not do, thanks.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 272 views
  • 3 likes
  • 5 in conversation