Renaming all variables in a table

[loredana_cornea]

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

[data_null__]

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?

[loredana_cornea]

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?

[RW9]

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;

[Kurt_Bremser]

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.

[Loko]

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)

[ballardw]

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.

[Scott_C_Moore]

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.

[MikeZdeb · Accepted solution]

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

[loredana_cornea]

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

[SASKiwi]

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?

[loredana_cornea]

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