topic Re: Renaming all variables in a table in SAS Procedures

Renaming all variables in a table

loredana_cornea — Thu, 23 Oct 2014 13:00:59 GMT

I have a table with 68 variables named: var1 .... var68.

I need to rename them all with different names. Example: Id, Date_1, Date_2, Date_3, Profit, Costs, ...., Transportation.

I have come up with this code:

proc import datafile="C:\...\...ana_a.csv"

out=ana_a

dbms=csv

replace;

delimiter=";";

getnames=no;

run;

proc contents data=ana_a out=oldnames noprint;

run;

proc sql noprint;

select name into :old separated by ' '

from oldnames;

quit;

%let new=Id Date_1 Date_2 Date_3 Profit Costs .... Transportation;

proc datasets library=work nolist;

modify ana_a;

rename &new; /* I HAVE TRIED &new=&old */

run;

It is not working. What can I do?

I cannot use arrays for I have all types of variables: numeric, character and date.

I need a simple hint if you can help me

Re: Renaming all variables in a table

data_null__ — Thu, 23 Oct 2014 13:08:43 GMT

The syntax of the rename statement is oldname=newname

Only numbered variable lists can be renamed en masse

x1-x100=y1-y100.

You need to "join" your two lists so you can gen the proper syntax. Do you have any ideas about how to do that?

Re: Renaming all variables in a table

Kurt_Bremser — Thu, 23 Oct 2014 13:14:03 GMT

In the rename statement, you need a sequence of

oldname=newname

for all variables to be renamed.

Unless you have a RELIABLE source for BOTH the old and new variable names, you're better off by simply writing the 68 parts of the rename statement manually.

Re: Renaming all variables in a table

loredana_cornea — Thu, 23 Oct 2014 13:22:10 GMT

my mistake .... &old=&new (not that it makes a difference in my output)

Joining the values of the 2 macro variables into a single macro var shouldn't be that hard. But what do i do next?

Re: Renaming all variables in a table

RW9 — Thu, 23 Oct 2014 13:35:52 GMT

If its a fixed list then just do:

data _null_;

call execute('proc datasets library=work nolist;

modify ana_a; rename');

array col_name{68} $20. {"Id","Date_1","Date_2","Date_3","Profit Costs", .... "Transportation"};

do I=1 to 68;

call execute('COL'||strip(put(I,best.))||'='||strip(col_names{I}));

end;

call execute('; quit;');

run;

Re: Renaming all variables in a table

Loko — Thu, 23 Oct 2014 14:13:03 GMT

Hello,

you can also use macro if you like:

/*prepare some data*/

data a;
set sashelp.class;
run;

/*variables*/

%let new=Id Date_1 Date_2 Date_3 Profit ;
options mprint;
/*macro*/

%macro a (dbase,lib=work);

proc contents data=&lib..&dbase out=oldnames noprint;
run;

proc sql noprint;
select name into :old separated by ' '
from oldnames;
select count(*) into :novars
from oldnames;
quit;

proc datasets library=&lib nolist;
modify &dbase;
rename
%do i=1 %to &novars;
%scan(&old,&i)
%str(=)
%scan(&new,&i)
%end;

%str(;)
quit;

%mend a;

%a(a)

Re: Renaming all variables in a table

ballardw — Thu, 23 Oct 2014 14:44:18 GMT

Especially if you are going to do this in the future:

Your code

proc import datafile="C:\...\...ana_a.csv"

out=ana_a

dbms=csv

replace;

delimiter=";";

getnames=no;

run;

will generate a datastep to read the data file. The code will appear in the log. If running this in Base SAS then pressing the F4 key should bring that generated code into the editor. Assign the variables in the datastep code, adjust variable lengths and types, assign labels and save the program. Then when you have another data file of the same structure to read you just update the input file and output dataset name.

Re: Renaming all variables in a table

Scott_C_Moore — Thu, 23 Oct 2014 15:36:14 GMT

With a little modification, you can do it pretty closely to your original post - see edit below.

 proc import datafile="C:\...\...ana_a.csv"
     out=ana_a
     dbms=csv
     replace;
     delimiter=";";
     getnames=no;
run;

proc contents data=ana_a out=oldnames noprint;
run;

proc sql noprint;
  select name into :old1-:old68
  from oldnames;
quit;

%let new1=Id;
%let new2=Date_1;
%let new3= Date_2;
%let new4=Date_3;
%let new5=Profit;
%let new=Costs;
....
%let new68=Transportation;

%macro rename ();
proc datasets library=work nolist;                                                                                                      
  modify ana_a; 
%do i = 1 %to 68;
  
    rename &&old&i=&&new&i;

%end;                                /* I HAVE TRIED &new=&old */                                                                                                        
run;
quit;
%mend;

%rename;

Creating a data set of your new names in the correct sort order and reading it in the same way you did your old names would make it possible to assign the new values to macro variables using proc sql instead of the let statments.

Re: Renaming all variables in a table

MikeZdeb — Thu, 23 Oct 2014 19:22:34 GMT

Hi, here's another idea ...

* fake data set ... 7 variables, var1-var7;

data have;

array var(7);

run;

* new variable names

data new;

input nm :$30. @@;

datalines;

Id Date_1 Date_2 Date_3 Profit Costs Transportation

;

* make a macro variable (cheat with monotonic);

proc sql noprint;

select catt('var',monotonic(),"=",nm) into :rn separated by ' ' from new;

quit;

proc datasets library=work nolist;

modify have;

rename &rn;

quit;

In the above, macro variable RN is ...

var1=Id var2=Date_1 var3=Date_2 var4=Date_3 var5=Profit var6=Costs var7=Transportation

Re: Renaming all variables in a table

SASKiwi — Thu, 23 Oct 2014 22:08:36 GMT

I notice you use GETNAMES = NO in your CSV import. Does that mean there is no header row or simply that you don't want to use the CSV column headings?

Re: Renaming all variables in a table

loredana_cornea — Fri, 24 Oct 2014 08:32:27 GMT

the csv file is actually generated by another program so the first line contains the columns' names but they are really distorted and useless

Re: Renaming all variables in a table

loredana_cornea — Fri, 24 Oct 2014 08:40:10 GMT

Thank you all very much for taking your time to respond to my problem.

In the end i will be using MikeZdeb's idea... i will tell you how it works out. It was a pretty urgent matter so unfortunately i did it the old fashioned way: writing by hand in csv file. But i'm going to redone it the proper way so I can use the code in the future. Thank you ballardw for the hint. I will be needing it. Although I am a virgin in database updating but i'll manage