DATA Step, Macro, Functions and more

rename many columns with underscore prefix dynamically

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 111
Accepted Solution

rename many columns with underscore prefix dynamically

Hello,

 

I get a dataset with 36 columns names  _1601 , _1602, _1603 ....._1911 and _1912

that corresponds to year month ("_" appears because of proc import csv file in which columns are named  is 1601 1602...)

I would like to rename them as 1601, 1602...1912 or replace "_" by "month"

Thanks a lot for your help

 

Kind  Regards,

 

Nasser

 


Accepted Solutions
Solution
‎11-15-2017 03:26 AM
Super User
Super User
Posts: 7,860

Re: rename many columns with underscore prefix dynamically

Posted in reply to Nasser_alfea

Get the list of names into data.  You could use PROC CONTENTS or query SAS's metadata tables.

Store the list of oldname=newname pairs into a macro variable.

Use the list in a RENAME statement or option

 

Here is one way using PROC TRANSPOSE to get the names.

proc transpose data=have(obs=0)  out=names ;
  var _: ;
run;
proc sql noprint ;
  select catx('=',_name_,tranwrd(_name_,'_','month')
    into :renames separated by ' '
  from names
  ;
quit;
proc datasets libname=work nolist;
  modify have ;
    rename &renames ;
  run;
quit;

View solution in original post


All Replies
Respected Advisor
Posts: 2,661

Re: rename many columns with underscore prefix dynamically

[ Edited ]
Posted in reply to Nasser_alfea

A better idea is to not do this, and instead use the method in Maxim 19 of Maxims of Maximally Efficient SAS Programmers

 

PROC TRANSPOSE should get you there.

--
Paige Miller
Super User
Posts: 13,084

Re: rename many columns with underscore prefix dynamically

Posted in reply to PaigeMiller

I agree with @PaigeMiller and would go one step further to make the "month" variable actually have a DATE value.

 

data example;
   x= '1602';
   y=input(x,yymmn4.);
   Put 'Y as date9. ' y= date9. +1 'Y as yymon. ' y= yymon. 'y as yyQ. ' y=yyq.;
run;

With a date value you can request summaries on that month value that would group by a specified format. So you could run proc means to sum by Year, quarter of the year, or month of the year.

 

If you wanted to create a summary or analysis for a specific interval you could use   '01Feb2016'd le month le '01Aug2017'd to select records in that interval for example.

Also your graphs with dates tend to display  better as graph that goes from 1606 to 1702 (assuming June 2016 to Feb 2017) has a very irregular boundary at the year boundary.

Respected Advisor
Posts: 2,661

Re: rename many columns with underscore prefix dynamically


ballardw wrote:

I agree with @PaigeMiller and would go one step further to make the "month" variable actually have a DATE value.

 


While I certainly would have done this if I was programming it myself, I am glad you mentioned it here, as I clearly forgot to mention it.

--
Paige Miller
Trusted Advisor
Posts: 1,826

Re: rename many columns with underscore prefix dynamically

Posted in reply to Nasser_alfea

1601 1602 are non valis sas names.

A valis sas name shouls start with either an alphabetic character or _ or some other few special characters.

 

See details in next link:

https://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000998953.htm

 

 

Solution
‎11-15-2017 03:26 AM
Super User
Super User
Posts: 7,860

Re: rename many columns with underscore prefix dynamically

Posted in reply to Nasser_alfea

Get the list of names into data.  You could use PROC CONTENTS or query SAS's metadata tables.

Store the list of oldname=newname pairs into a macro variable.

Use the list in a RENAME statement or option

 

Here is one way using PROC TRANSPOSE to get the names.

proc transpose data=have(obs=0)  out=names ;
  var _: ;
run;
proc sql noprint ;
  select catx('=',_name_,tranwrd(_name_,'_','month')
    into :renames separated by ' '
  from names
  ;
quit;
proc datasets libname=work nolist;
  modify have ;
    rename &renames ;
  run;
quit;
Frequent Contributor
Posts: 111

Re: rename many columns with underscore prefix dynamically

Thanks a lot Tom ! it works exactly as intended

thanks to others users for your responses

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 218 views
  • 3 likes
  • 5 in conversation