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!
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;
/********************************************************************
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!
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;
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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.