topic Re: Rename all variables in a dataset at once in SAS Programming

Rename all variables in a dataset at once

di_niu0 — Mon, 01 Mar 2021 16:05:50 GMT

Hello. I have a dataset with 10 variables (eg. client, price, volume, ship_date, purchase_date...). I would like to add v1 to all variables at once (eg. client_v1, price_v1, volume_v1, ship_date_v1, purchase_date_v1....). I am not sure how to do it. Thank you in advance!

Re: Rename all variables in a dataset at once

Reeza — Mon, 01 Mar 2021 16:32:12 GMT

Generate fake data to work with (sample)
Generate rename statement into a macro variable (look at WHERE for filter and CATT() for new variable name)
Apply using PROC DATASETS, which doesn't recreate the data set, just modifies the names.

/********************************************************************
Example : Rename variables based on suffix rather than prefix
********************************************************************/

data sample;
do i=10000 to 12000;
	start_date=i;
	middle_date=i+3;
	end_date=i+5;
	date_no_change=start_date;
	output;
end;
format start_date end_date middle_date date9.;
run;


proc sql noprint;
select catx("=", name, catt('DT_', tranwrd(upper(name), '_DATE', ' '))) 
into :rename_list
separated by " "
from sashelp.vcolumn
where libname='WORK'
and memname='SAMPLE'
and upper(trim(name)) like '%_DATE';
quit;


%put &rename_list;


proc datasets library=work nodetails nolist;
modify sample;
rename &rename_list;
run; quit;

proc print data=sample noobs;
run;

A little more specifically for your use case, your SQL would look something like this:


proc sql noprint;
select catx("=", name, catt(name, "_v1")) 
into :rename_list
separated by " "
from sashelp.vcolumn
where libname='WORK'
and memname='SAMPLE';
quit;


%put &rename_list;

@di_niu0 wrote:

Hello. I have a dataset with 10 variables (eg. client, price, volume, ship_date, purchase_date...). I would like to add v1 to all variables at once (eg. client_v1, price_v1, volume_v1, ship_date_v1, purchase_date_v1....). I am not sure how to do it. Thank you in advance!

Re: Rename all variables in a dataset at once

data_null__ — Mon, 01 Mar 2021 16:57:11 GMT

This macro will be helpful for this and other variable list manipulations.

%macro
   expand_varlist /*Returns an expanded variable list and optionally creates an indexed data set of variable names*/
      (
         data  = _LAST_,            /*[R]Input data*/
         var   = _ALL_,             /*[R]Variable List expanded*/
         where = 1,                 /*[R]Where clause to subset OUT=, useful for selecting by a name suffix e.g. where=_name_ like '%_Status'*/
         expr  = nliteral(&name),   /*[R]An expression that can be used to modify the names in the expanded list*/
         keep  = ,                  /*[O]Keep data set option for DATA=*/
         drop  = ,                  /*[O]Drop data set option for DATA=*/
         out   = ,                  /*[O]Output data indexed by _NAME_ and _INDEX_*/
         name  = _NAME_,            /*[R]Name of the variable name variable in the output data set*/
         label = _LABEL_,           /*[R]Name of the variable name label variable in the output data set*/
         index = _INDEX_,           /*[R]Name of the variable index variable in the output data set*/
         dlm   = ' '                /*[R]List delimiter*/
      );
   %local m i;
   %let i=&sysindex;
   %let m=&sysmacroname._&i;
   %do %while(%symexist(&m));
      %let i = %eval(&i + 1);
      %let m=&sysmacroname._&i;
      %end;
   %put NOTE: &=m is a unique symbol name;
   %local rc &m code1 code2 code3 code4;
   %if %superq(out) ne %then %let code3 = %str(data &out(index=(&index &name)); set &out; &index+1; run;);
   %else %do;
      %let out=%str(work._deleteme_);
      %let code3 = %str(proc delete data=work._deleteme_; run;);
      %end;
   %let code1 = %str(options notes=0; proc transpose name=&name label=&label data=&data(obs=0 keep=&keep drop=&drop) out=&out(where=(&where)); var &var; run;);
   %let code2 = %str(proc sql noprint; select &expr into :&m separated by &dlm from &out; quit;);
   %let code4 = %str(options notes=1;);
   %let rc=%sysfunc(dosubl(&code1 &code2 &code3 &code4));
&&&m.
   %mend expand_varlist;

/*create sample data*/
data class;
   set sashelp.class;
   run;
/*Generate rename at once list*/
%let rename=%expand_varlist(data=class,expr=catx('=',_name_,cats(_name_,'_V1')));
%put NOTE: &=rename;

/*Rename*/
proc datasets;
   modify class;
   rename &rename; 
   run;
   contents data=class varnum;
   run;
   quit;

Re: Rename all variables in a dataset at once

di_niu0 — Mon, 01 Mar 2021 17:39:58 GMT

Thank you very much!

Re: Rename all variables in a dataset at once

ehmsoleil — Thu, 04 Nov 2021 20:03:38 GMT

Hi! data_null has a good solution, but you can do it with less code. You can use the dictionary libname or proc contents to get the list of variables from the dataset you want to change.

/*option 1*/

proc sql noprint;
select distinct compbl(name||" = "||compress(name||"_v1")) into :re separated by ' '
from dictionary.columns
where libname = 'WORK' and memname = 'MYDATA';
quit;

/*option 2*/

proc contents data = mydata out = var (keep = name) noprint;

run;

proc sql noprint;
select distinct compbl(name||" = "||compress(name||"_v1")) into :re separated by ' '
from var;
quit;

/*last step after option 1 or 2*/

proc datasets lib = work nolist;
modify mydata;
rename &re.;
run;quit;