DATA Step, Macro, Functions and more

Rename Column name by variablevalue

Reply
Contributor
Posts: 20

Rename Column name by variablevalue

[ Edited ]

Hi,

     I have a macro variable like below

%let studybegin_period =MDY(1,1,2015)

%let studyend_period =MDY(12,1,2015)

 

i have a dataset with column names like M1, M2...M12(for 12 months),i need to replace column names with values like

Jan2015,FEB2015....DEC2015.NOTE: the columns increase or decrese depending on the time period i have on the macro variables.

 

Please help me know how to go about it.Thanks!

PROC Star
Posts: 1,760

Re: Rename Column name by variablevalue

Like this?

 

%let studybegin_period =MDY(1,1,2015);
%let studyend_period =MDY(12,1,2015) ;

%let studybegin_period =MDY(12,1,2015);
%let studyend_period =MDY(1,1,2015) ;

data HAVE;
  length M1-M12 $1;
run;
         
data _null_;
  length STR $800;  
  do until(MTH=&studyend_period);        
    I+1;
    MTH=intnx('month', &studybegin_period ,ifn(&studybegin_period <&studyend_period, I-1, -I+1));
    STR=catt(STR,' M',I,'=',put(MTH,monyy7.));  
  end;               
  call symputx('rename', STR);
run;

proc datasets; 
  modify HAVE;
  rename &rename.;
quit;
     

NOTE: Renaming variable M1 to DEC2015.
NOTE: Renaming variable M2 to NOV2015.
NOTE: Renaming variable M3 to OCT2015.
NOTE: Renaming variable M4 to SEP2015.
NOTE: Renaming variable M5 to AUG2015.
NOTE: Renaming variable M6 to JUL2015.
NOTE: Renaming variable M7 to JUN2015.
NOTE: Renaming variable M8 to MAY2015.
NOTE: Renaming variable M9 to APR2015.
NOTE: Renaming variable M10 to MAR2015.
NOTE: Renaming variable M11 to FEB2015.
NOTE: Renaming variable M12 to JAN2015.

 

Super User
Posts: 10,023

Re: Rename Column name by variablevalue

data have;
array x{12} m1-m12 (12*1);
run;

%let start=01jan2015;
proc transpose data=have(obs=0) out=temp;
 var m:;
run;
data temp;
 set temp;
 retain n -1;
 n+1;
 name=put(intnx('month',"&start"d,n),monyy7.);
 run;
 data _null_;
  set temp end=last;
  if _n_=1 then 
  call execute('proc datasets library=work nolist nodetails; modify have; rename');
  call execute(catx('=',_name_,name));
  if last then call execute(';quit;');
run;
Ask a Question
Discussion stats
  • 2 replies
  • 287 views
  • 0 likes
  • 3 in conversation