topic Re: how change column name using macro in SAS Programming

how change column name using macro

saeidrasti — Thu, 21 Jul 2022 17:48:06 GMT

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;

Re: how change column name using macro

Reeza — Thu, 21 Jul 2022 19:18:44 GMT

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;

Re: how change column name using macro

Tom — Thu, 21 Jul 2022 19:11:55 GMT

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:

Re: how change column name using macro

andreas_lds — Fri, 22 Jul 2022 08:23:38 GMT

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.

Re: how change column name using macro

PaigeMiller — Fri, 22 Jul 2022 10:34:39 GMT

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

Re: how change column name using macro

saeidrasti — Mon, 25 Jul 2022 18:01:51 GMT

Thank you so much Reeza, it worked.