I have one dataset including two(x y) or more variables. I want to do some transformation (for examle, power transformation) on my data and get a new dataset including x, y, trans_x and trans_y. Because I probably have more than two variables in my dataset, I wrote one macro.
%macro data_trans(inputdata, outputdata, varlist, trans_factor);
data &outputdata;
set &inputdata;
num=countw("&varlist");
do i=1 to num;
'tran_'||left(scan(&varlist,i))= (scan(&varlist,i))**&trans_factor; /* create one new variable name and do data transformation*/
end;
drop num i;
run;
%mend;
or
%macro data_trans(inputdata, outputdata, varlist, trans_factor);
data &outputdata;
set &inputdata;
array arr &varlist;
do over arr;
'trans_'||arr=arr**&trans_factor; /* create one new variable name and do data transformation*/
end;
run;
%mend;
Either of them didn't work. Something wrong was with "||".
Anyone could help me out with it? Greatly appreciated.
You need to use the macro statements to generate the variable names. Your code is trying to use DATA statements.
For example you want the macro to generate the text TRAN_VAR1 so that it can be used on the left side of an assignment statement.
%macro data_trans(inputdata, outputdata, varlist, trans_factor);
data &outputdata;
set &inputdata;
%do i=1 %to %sysfunc(countw(&varlist));
tran_%scan(&varlist,&i) = %scan(&varlist,&i)**&trans_factor;
%end;
run;
%mend;
You need to use the macro statements to generate the variable names. Your code is trying to use DATA statements.
For example you want the macro to generate the text TRAN_VAR1 so that it can be used on the left side of an assignment statement.
%macro data_trans(inputdata, outputdata, varlist, trans_factor);
data &outputdata;
set &inputdata;
%do i=1 %to %sysfunc(countw(&varlist));
tran_%scan(&varlist,&i) = %scan(&varlist,&i)**&trans_factor;
%end;
run;
%mend;
assuming you want to transform all variables except variable 'aa':
data have;
input x y z aa;
cards;
1 2 2 20
3 4 3 30
;
%let trans_factor=2;
proc sql noprint;
select catt(name,',',name,'**',&trans_factor, ' as',catt(' trans_',name)) into: names separated by ','
from dictionary.columns
where libname='WORK' and memname='HAVE' and upcase(name) ne 'AA';
quit;
%put &names;
proc sql;
create table want as select &names
from have;
quit;
proc print;run;
Obs x trans_x y trans_y z trans_z
1 1 1 2 4 2 4
2 3 9 4 16 3 9
Linlin
Thanks, all guys!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.