BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
apolitical
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
collinelliot
Barite | Level 11

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

8 REPLIES 8
collinelliot
Barite | Level 11

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;
apolitical
Obsidian | Level 7
the simplest solution that meets my need. thank you.
Reeza
Super User

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

apolitical
Obsidian | Level 7
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?
Reeza
Super User

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. 

Ksharp
Super User
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;
Patrick
Opal | Level 21

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

 

apolitical
Obsidian | Level 7
your option 2 of constructing a list of "oldvar=newvar" texts was what i originally had in mind but could not do, thanks.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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