Rename all the variable in a library specifying mappin in csv

[Sir_Highbury]

Dear sas Experts,

My problem is the following one:

• I have a library with several tables;
• I have a csv file containing the mapping between the physical name of the variables in the library (extracted with the proc content) and the new names (in english) that should replace the variable;

I found several macro online but I would like to build up something easy that fits my basic requirements.

Could you please have a look to my code down here? How should enhance it in order to do the renaming in a whole library?

Attached you find also an example of my mapping file. I thank you in advance for your support.

/* import mapping file stored as CSV */

options validvarname=any; /* facilitate the import of variables */

proc import out=Perm.variables

      datafile = "W:\04-Programfiles\Documentation\Library_table_variables_for_sas.csv" dbms = csv replace;

      DATAROW=2;

      /*namerow=2;

      startobs=3;*/

      getnames=yes;

      delimiter="¦";

      guessingrows=1000;

/* original variable value list */

data oldvarlist;

set Perm.variables;

keep Variable_Name_Original

;run;

/* new variable value list */

data newvarlist;

set Perm.variables;

keep Variable_Name_English

;run;

%macro rename_2_english(oldvarlist, newvarlist);

  %let k=1;

  %let old = %scan(&oldvarlist, &k);

  %let new = %scan(&newvarlist, &k);

     %do %while(("&old" NE "") & ("&new" NE ""));

      rename &old = &new;

        %let k = %eval(&k + 1);

      %let old = %scan(&oldvarlist, &k);

      %let new = %scan(&newvarlist, &k);

  %end;

%mend;

%rename_2_english;

[Kurt_Bremser]

In order to further automate this, you can read the variable names (and dataset names) from dictionary.columns (or sashelp.vcolumn) and create a dataset containing the table and column names (old and new) for renaming. Then you can create the data steps from that dataset dynamically with call execute.

A place for slight improvement:

%macro rename_2_english(oldvarlist, newvarlist);
  %let i=%sysfunc(min(%sysfunc(countw(oldvarlist)),%sysfunc(countw(&newvarlist))));
  %do k = 1 %to &i;
    %let old = %scan(&oldvarlist, &k);
    %let new = %scan(&newvarlist, &k);
    rename &old = &new;
  %end;
%mend;

[Sir_Highbury]

Dear Kurt, thanks a lot for the suggestion, I will integrate it in the macro.

I found this macro online and now I am trying to adapt it to my needs.

It is clear to me clear how I shoul input the (new and old), that's why I wrote the following data steps:

data oldvarlist;

set Perm.variables;

keep Variable_Name_Original

;run;

/* new variable value list */

data newvarlist;

set Perm.variables;

keep Variable_Name_English

;run;

but how/where should I in the macro specify the library where the macro should be effective? My expectation is that in a certain library (e.g. "library1") all the variables that are listed om "old", after enabling the macro, are renamed to "new". Thanks again for your support.

[Kurt_Bremser]

Have a look at this:

%macro change_var_names(libname);

/* get all current column names for all datasets in library */
proc sql;
create table columns as
  select
    memname,
    name as Variable_Name_Original
  from dictionary.columns
  where libname = "&libname" and memtype = 'DATA'
  order by name
;
quit;

/* sort your dataset */
proc sort data=perm.variables;
by Variable_Name_Original;
run;

/* merge so that only columns actually present, and datasets containing such columns, are left */
data changeover;
merge
  columns (in=a)
  perm.variables (in=b)
;
by Variable_Name_Original;
if a and b;
run;

proc sort data=changeover;
by memname;
run;

/* dynamically create data step code for each dataset */
data _null_;
set changeover;
by memname;
if first.memname
then do;
  call execute("data &libname.."!!trim(memname)!!"; set &libname.."!!trim(memname)!!"; rename ");
end;
call execute(trim(Variable_Name_Original)!!'='!!trim(Variable_Name_English)!!' ');
if last.memname
then do;
  call execute('; run;');
end;
run;

%mend;

[data_null__]

@Kurt_Bremser why are you reading every record in ever file to change the meta data?  You might want to have a look a PROC DATASETS.

[Kurt_Bremser]

Yeah, would be much bettter performancewise.

[Sir_Highbury]

Dear Kurt,

the code looks gorgeus but I still do not get the modification. Let me explain:

I set preliminary: let libname=DC;

then I used the code you provided. I checked the macro step by step:

1. the data set Column is correctly generated;

3. the data set Changeover is correctly generated;

If I run the whole macro including the last part (/* dynamically create data step code for each dataset */) I did not get any error message in the Log but in the tables stored in the directory DC all the variables still have the original name.

Kannst du bitte mir weiter helfen?

Best regards,

Fabio

PS Frohe Ostern!

[ballardw]

Here is some code I have laying around that renames variable in an existing data set.

data _null_;
   set work.rename end=LastName;
   if _n_ = 1 then do;
      Call execute ("Proc datasets library=&OutDataLib nodetails nolist;");
      Call execute ("modify &RenamedData;");
      Call execute ("rename ")  ;
   end;
   Call execute(catx(' ',name,' = ',newname)) ;
   if LastName then do;
      Call execute (";") ;
      Call execute ("quit;");
   end;
run;

The set named work.rename has two variable name, the current variable name, and newname, the new name I want.

the library to work in is in a macro variable OutDataLib and the data set name in that library is RenamedData.

This would be easy to exend to multiple data sets, especially if all of the sets have the same rename criteria.

[Kurt_Bremser · Accepted solution]

I did some small changes (upcase()), and set up a a test scenario:

data test.class;
set sashelp.class;
run;

data work.variables;
length
  Variable_Name_Original $20
  Variable_Name_English $20
;
input
  Variable_Name_Original
  Variable_Name_English
;
cards;
Age Age_In_Years
;
run;

%macro change_var_names(libname);
%let libname=test;
/* get all current column names for all datasets in library */
proc sql;
create table columns as
  select
    memname,
    name as Variable_Name_Original
  from dictionary.columns
  where upcase(libname) = upcase("&libname") and memtype = 'DATA'
  order by name
;
quit;

/* sort your dataset */
proc sort data=work.variables;
by Variable_Name_Original;
run;

/* merge so that only columns actually present, and datasets containing such columns, are left */
data changeover;
merge
  columns (in=a)
  work.variables (in=b)
;
by Variable_Name_Original;
if a and b;
run;

proc sort data=changeover;
by memname;
run;

/* dynamically create data step code for each dataset */
data _null_;
set changeover;
by memname;
if first.memname
then do;
  call execute("data &libname.."!!trim(memname)!!"; set &libname.."!!trim(memname)!!"; rename ");
end;
call execute(trim(Variable_Name_Original)!!'='!!trim(Variable_Name_English)!!' ');
if last.memname
then do;
  call execute('; run;');
end;
run;

%mend;

proc datasets library=test;contents data=test.class;quit;

%change_var_names(TEST);

proc datasets library=test;contents data=test.class;quit;

The (partial) results from the DATASET procedures looks like this:

before:

                                             Alphabetic List of Variables and Attributes
 
                                                    #    Variable    Type    Len

                                                    3    Age         Num       8
                                                    4    Height      Num       8
                                                    1    Name        Char      8
                                                    2    Sex         Char      1
                                                    5    Weight      Num       8

after:

                                             Alphabetic List of Variables and Attributes
 
                                                  #    Variable        Type    Len

                                                  3    Age_In_Years    Num       8
                                                  4    Height          Num       8
                                                  1    Name            Char      8
                                                  2    Sex             Char      1
                                                  5    Weight          Num       8

will now prepare a variant using DATASETS instead of DATA steps.

[Sir_Highbury]

thanks a lot!

[Kurt_Bremser]

I now changed the data _null_ to use PROC datasets, final version is

data _null_;
set changeover;
by memname;
if first.memname
then do;
  call execute("proc datasets library=&libname nodetails nolist;");
  call execute("modify "!!trim(memname)!!"; rename ");
end;
call execute(trim(Variable_Name_Original)!!'='!!trim(Variable_Name_English)!!' ');
if last.memname
then do;
  call execute('; quit;');
end;
run;

This leads to the same results in my test scenario.