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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

10 REPLIES 10
Kurt_Bremser
Super User

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
Quartz | Level 8

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
Super User

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__
Jade | Level 19

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

Sir_Highbury
Quartz | Level 8

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
Super User

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
Super User

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.

Kurt_Bremser
Super User

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.

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
  • 10 replies
  • 2156 views
  • 2 likes
  • 4 in conversation