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

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

1 ACCEPTED SOLUTION

Accepted Solutions
MikeZdeb
Rhodochrosite | Level 12

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

11 REPLIES 11
data_null__
Jade | Level 19

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
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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

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
Barite | Level 11

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

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
Calcite | Level 5

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
Rhodochrosite | Level 12

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
Obsidian | Level 7

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

SASKiwi
PROC Star

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
Obsidian | Level 7

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 11 replies
  • 3466 views
  • 8 likes
  • 9 in conversation