Help with renaming Variables based on table values

[cypher85]

I need to rename a variable based on a table value found within another dataset. So for instance I have two datasets. One dataset contains variables named variable1 and variable2. Now, in dataset2 I have variables that are named with values found in variable1 (For instance, if variable1 in Dataset 1 has values of A,B, and C, and variable2 has values of 1,2,3 and the variable names of variables found in dataset2 are A,B,C. I would want to rename the variables to 1,2,and 3 respectively) , and I wish to rename these variables to the value found in variable2. Essentially what I need is, when a variable name matches a particular value then I need to rename that variable to another value. Hopefully someone can help me out! Thanks alot

[Linlin]

sas variable names can't start with numbers.

[cypher85]

Thank you for your response. However, those were just examples, and aren't my actual variable names. Essentially what I need to do is rename variables based on values found in another data set. So if I were to modify my original question so that variable names don't begin with numbers, it would be:

I need to rename a variable based on a table value found within another dataset. So for instance I have two datasets. One dataset contains variables named variable1 and variable2. Now, in dataset2 I have variables that are named with values found in variable1 (For instance, if variable1 in Dataset 1 has values of A,B, and C, and variable2 has values of E,F,G and the variable names of variables found in dataset2 are A,B,C. I would want to rename the variables to E,F,and G respectively) , and I wish to rename these variables to the value found in variable2. Essentially what I need is, when a variable name matches a particular value then I need to rename that variable to another value.

[data_null__]

Make example data sets as post them here, as SAS code not attachments.

[cypher85]

Here is something that I quickly just wrote to create datasets a and b,

data a;

mapped_name = "b10";

if mapped_name = "b10" then online_name = "FirstDose";

run;

data b;

b10 = 3;

run;

Now what I want to do, is to be able to change the variable name (b10) in dataset B to Firstdose (the value of online_name in dataset A). So I want to update the name in Dataset B so that when the variable name matches the value of Mapped_name in dataset A then the variable in Dataset B is renamed with the value found in online_name in Dataset A. This is a on a very small scale using one set of variables, but the data sets I'm actually working with have thousands of variables and need to have the variable names renamed based on the values in another dataset. I hope this is making sense, I think I am having difficulty articulating what exactly it is I am trying to do.

[Tom]

Sounds like you have a mapping file that list original name an new name.

data renames ;
  input ( original newname) (:$32.);
cards;
b10 FirstDose
run;
data b;
b10 = 3;
run;
proc sql noprint ;
  select catx('=',original,newname)
    into :rename separated by ' '
  from dictionary.columns d
     , renames r
  where d.libname='WORK'
    and d.memname='B'
    and upcase(d.name) = upcase(r.original)
  ;
quit;
proc datasets nolist lib=work;
  modify b ;
  rename &rename ;
run;
quit;

[Haikuo]

Like it.

Especially the proc datasets part of the code. Before seeing Tom's code, I would have chosen data step, and then Tom's code flashed back my memory that 'Proc datasets; modify;' only does the rename by processing the descriptor part of the data, therefore, very efficient!

Thanks for sharing, Tom.

Haikuo

[Linlin]

Hi Tom,

How can you come up with so nice,short code? look at my code:

data renames ;

  input ( original newname) (:$32.);

cards;

b10 FirstDose

b11 SecondDose

b12 third

run;

data b;

b10 =3;

b11=8;

b13=9;

run;

proc sql noprint;

  select quote(trim(name)) into

     :names separated by ','

         from dictionary.columns

                  where libname='WORK' AND memname='B';

create table temp as

    select * from renames

             where original in (&names);

  select catx('=',original,newname)

    into :rename separated by ' '

         from temp;

quit;

%put &rename;

proc datasets nolist lib=work;

  modify b ;

    rename &rename;

  run;

quit;

[Tom]

You just need to recognize where you can combine steps.  So rather than pulling a list of variable names and then in a separate step checking if they are in the rename list you can combine that into one step.  Similarly with the output to a macro variable.

[Linlin]

Thank you Tom!  - Linlin

[Haikuo]

One thing to keep in mind that data step variable name length limit is 32 character, while the character variable value could up to 32k in length.

Regards,

Haikuo

[Ksharp]

My code is shorter and better than Tom's .

data renames ;
  input ( original newname) (:$32.);
cards;
b10 FirstDose
run;

data b;
b10 = 3;
run;
data _null_;
 set renames end=last;
 if _n_ eq 1 then call execute('proc datasets nolist lib=work; modify b;rename');
 call execute(catx('=',original,newname));
 if last then call execute(';quit;');
run;

Ksharp

[Haikuo]

This is also great! Still, Tom's code is less harsher on my brain

:smileysilly:

Edit: Ksharp, your code only works when 'rename' is sort of subset to 'b', otherwise, there will be error message:

data renames ;

  input ( original newname) (:$32.);

cards;

b10 FirstDose

a10 adfadsf

;

run;

data b;

b10 = 3;

output;

a11=4;

output;

run;

data _null_;

set renames end=last;

if _n_ eq 1 then call execute('proc datasets nolist lib=work; modify b;rename');

call execute(catx('=',original,newname));

if last then call execute(';quit;');

run;

[Ksharp]

It doesn't matter about ERROR message.

If proc datasets can't find a variable name , he will keep it and continue to rename the following variable name.

So proc datasets will rename all the variables in datasets A which also be included in B.

data renames ;
  input ( original newname) (:$32.);
cards;
b10 FirstDose
a10 adfadsf
a9 adfadsf
;
run;

 

data b;
b10 = 3;
output;
a11=4;
output;
a10=0;
output;
run;

data _null_;
set renames end=last;
if _n_ eq 1 then call execute('proc datasets nolist lib=work; modify b;rename');
call execute(catx('=',original,newname));
if last then call execute(';quit;');
run;

Ksharp

[cypher85]

Thank you all very much! This has been a big help. However, I am a fairly big sas noob. And Tom's code works great for the example. Though I am having difficulty adapting it to my actual datasets. My two datasets are named list (mapping file that list original name and new name, This corresponds to the dataset "renames" in Tom's code, and my variable names in "list" are "mapped_name" and "online_name", which correspond to "original" and "newname" in Tom's code respectively), and list2 (Dataset which corresponds to dataset "B" in Tom's code). Now "list2" has over 1000 variables that need to be renamed based on the values found in "list". I know this is such a noob thing to ask, but how would I modify Tom's code to change:

a) all of the variables at once

and

B) only one variable if I want or need to

And again thank you all so much for your help.