Help using Base SAS procedures

Renaming all variables in a table

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

Renaming all variables in a table

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 Smiley Surprisedld 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


Accepted Solutions
Solution
‎10-23-2014 03:22 PM
Valued Guide
Posts: 765

Re: Renaming all variables in a table

Posted in reply to loredana_cornea

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,799

Re: Renaming all variables in a table

Posted in reply to loredana_cornea

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?

Contributor
Posts: 31

Re: Renaming all variables in a table

Posted in reply to data_null__

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?

Super User
Super User
Posts: 7,942

Re: Renaming all variables in a table

Posted in reply to loredana_cornea

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;

Super User
Posts: 7,762

Re: Renaming all variables in a table

Posted in reply to loredana_cornea

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 308

Re: Renaming all variables in a table

Posted in reply to loredana_cornea

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 Smiley Surprisedld 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(Smiley Wink
quit;

%mend a;


%a(a)

Super User
Posts: 11,343

Re: Renaming all variables in a table

Posted in reply to loredana_cornea

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.

Occasional Contributor
Posts: 12

Re: Renaming all variables in a table

Posted in reply to loredana_cornea

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.

Solution
‎10-23-2014 03:22 PM
Valued Guide
Posts: 765

Re: Renaming all variables in a table

Posted in reply to loredana_cornea

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

Contributor
Posts: 31

Re: Renaming all variables in a table

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 Smiley Wink but i'll manage

Super User
Posts: 3,250

Re: Renaming all variables in a table

Posted in reply to loredana_cornea

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?

Contributor
Posts: 31

Re: Renaming all variables in a table

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 877 views
  • 8 likes
  • 9 in conversation