BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Nasser_DRMCP
Lapis Lazuli | Level 10

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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

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.

PaigeMiller
Diamond | Level 26

@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
Shmuel
Garnet | Level 18

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

 

 

Tom
Super User Tom
Super User

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;
Nasser_DRMCP
Lapis Lazuli | Level 10

Thanks a lot Tom ! it works exactly as intended

thanks to others users for your responses

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

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
  • 6 replies
  • 2285 views
  • 3 likes
  • 5 in conversation