how change column name using macro

[saeidrasti]

I have a table and I want to change the name of some columns using macro. The initial names are Bucket1-Bucket96 and I want to change them to time intervals. for example change Bucket 1 to 00:00:00-00:14:59, Bucket2 to 00:15:00-00:29:59 and so on. this is my code t1 is the current table I have 

I appreciate if you can help me

data column_name;

data column_name;
	length myVar $ 1000;
	*myVar='';
    time='00:00:00't; 
	i=0;
	do hour = 0 to 23;
		hours=intnx('HOUR',time,hour,'b');
	  	do minutes = 0 to 45 by 15; 
			i=i+1;
	 		begin=intnx('MINUTE',hours,minutes,'b');
			begin_char=put(begin,tod8.);
	    	end=intnx('MINUTE',begin,14,'e');
			end_char=put( end,tod8.);
			myVar=catx(' ',myVar,' ',cats('BUCKET',i,'=',cats('"',catx('-',begin_char, end_char),'"n')));
		end;
	end;
/*  call symput('rename_list',myVar);*/
run;
%Put &myVar;

data t2;
  set work.t1;
  rename &myVar;
run; 

[Reeza · Accepted solution]

Those will not be valid variable names and I'd highly recommend against using those names. 

Instead you can create labels for your data set so the labels will show with the time intervals. 

data column_name;
	length myVar $ 32000;
    time='00:00:00't; 
    i=0;
	do time='00:00:00't to '23:59:00't by 900; /* 900 seconds is 15 minutes and SAS stores time in seconds*/	
	i+1;
			begin_char=put(time,tod8.);
	    	end=intnx('MINUTE',time,14,'e');
			end_char=put( end,tod8.);
			myVar=catx(' ',myVar,' ',cats('BUCKET',i,'=',cats('"',catx('-',begin_char, end_char)), '"'));
		end;
  call symput('label_list',myVar);
run;

%put &label_list;

data have;
array buckets(96) bucket1-bucket96;
do i=1 to 96;
buckets(i) = rand('bernoulli', 0.4);
end;
run;

proc datasets library=work;
modify have;
label &label_list;
run;

proc contents data=have;run;

proc print data=have label;
var bucket1-bucket5 bucket90-bucket96;
run;

If you really want those names, you can change the solution above to your original approach and change the LABEL to RENAME in proc datasets. I REALLY REALLY do not recommend this. 

data column_name;
	length myVar $ 32000;
    time='00:00:00't; 
    i=0;
	do time='00:00:00't to '23:59:00't by 900;	
	i+1;
			begin_char=put(time,tod8.);
	    	end=intnx('MINUTE',time,14,'e');
			end_char=put( end,tod8.);
			myVar=catx(' ',myVar,' ',cats('BUCKET',i,'=',cats('"',catx('-',begin_char, end_char)), '"n'));
		end;
  call symput('label_list',myVar);
run;

%put &label_list;

data have;
array buckets(96) bucket1-bucket96;
do i=1 to 96;
buckets(i) = rand('bernoulli', 0.4);
end;
run;

proc datasets library=work;
modify have;
rename &label_list;
run;

proc contents data=have;run;

[saeidrasti]

Thank you so much Reeza, it worked.

[Tom]

To use names like that you have to do two things. First set the VALIDVARNAME option to ANY.  Second use NAME LITERALS.

I would suggest it is much better to just attach LABELs to the variables instead.

data column_names;
  length bucket hour minute time 8 name $32 label $256 ;
  format time tod8.;
  do hour=0 to 23;
   do minute=0 to 45 by 15;
     bucket+1;
     time=hms(hour,minute,0);
     name = cats('BUCKET',bucket);
     label=catx('-',put(time,tod8.),put(time+'00:14:59't,tod8.));
     output;
   end;
  end;
run;

proc sql noprint;
  select catx('=',name,nliteral(label))
       , catx('=',name,quote(trim(label)))
    into :rename separated by ' '
       , :label separated by ' '
  from column_names
  ;
quit;

data have;
 length bucket1-bucket96 8;
run;

proc datasets nolist lib=WORK ;
  modify HAVE ;
   * rename &rename ;
   label &label ;
  run;
quit;

proc print data=have;
run;
proc print data=have label;
run;

Result:

[andreas_lds]

Depending on what you have to do with the data in subsequent steps, it could be a good idea to transpose the data into a longer format, so that you have one Bucket variable holding the values that you want as variable names now.

[PaigeMiller]

---

@andreas_lds wrote:

Depending on what you have to do with the data in subsequent steps, it could be a good idea to transpose the data into a longer format, so that you have one Bucket variable holding the values that you want as variable names now.

---

This is a great idea. @saeidrasti would be wise to follow this suggestion, which eliminates this renaming issue, and has additional benefit in simpler programming in subsequent steps.