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
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;
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.
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.
@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.
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
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;
Thanks a lot Tom ! it works exactly as intended
thanks to others users for your responses
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.