topic Re: rename many columns with underscore prefix dynamically in SAS Programming

rename many columns with underscore prefix dynamically

Nasser_DRMCP — Tue, 14 Nov 2017 17:28:35 GMT

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

Re: rename many columns with underscore prefix dynamically

PaigeMiller — Tue, 14 Nov 2017 20:12:48 GMT

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.

Re: rename many columns with underscore prefix dynamically

Shmuel — Tue, 14 Nov 2017 20:08:12 GMT

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

Re: rename many columns with underscore prefix dynamically

Tom — Tue, 14 Nov 2017 21:40:50 GMT

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;

Re: rename many columns with underscore prefix dynamically

ballardw — Tue, 14 Nov 2017 22:35:33 GMT

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.

Re: rename many columns with underscore prefix dynamically

Nasser_DRMCP — Wed, 15 Nov 2017 08:26:00 GMT

Thanks a lot Tom ! it works exactly as intended

thanks to others users for your responses

Re: rename many columns with underscore prefix dynamically

PaigeMiller — Wed, 15 Nov 2017 13:37:28 GMT

@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.