Hi,
I am trying to winsorize a lot of variables. I searched and found there are so many macros out there. The macro I am currently using is this one: http://www.wrds.us/index.php/repository/view/sas_winsorize_macro
However, I have more than 50 variables to be winsorized. And the names of the 50 variables are not quite regular. I tried to quote the macro above in the following way. But it did not work out.
%winsor(dsetin=in, dsetout=out, byvar=none, vars=atg--umh, type=winsor, pctl=1 99)
I will appreciate it very much someone can help me out here.
Thanks!
You need to list the variables out in that macro instead of using the shortcut reference. Since it does what you want I suggest building a macro variable using SQL and passing that to the macro. Assuming your variables are ordered, which would need to be the case for your code to have worked, and that the first variable is 3 and the last is 103 then something like this would work.
proc sql noprint;
select name into :var_list separated by " "
from sashelp.vcolumn
where upper(libname) = 'WORK' and upper(memname) = 'IN'
and varnum between 3 and 103;
quit;
Then you can call the macro using:
%winsor(dsetin=in, dsetout=out, byvar=none, vars=&var_list, type=winsor, pctl=1 99)
@daradanye wrote:
Hi,
I am trying to winsorize a lot of variables. I searched and found there are so many macros out there. The macro I am currently using is this one: http://www.wrds.us/index.php/repository/view/sas_winsorize_macro
However, I have more than 50 variables to be winsorized. And the names of the 50 variables are not quite regular. I tried to quote the macro above in the following way. But it did not work out.
%winsor(dsetin=in, dsetout=out, byvar=none, vars=atg--umh, type=winsor, pctl=1 99)
I will appreciate it very much someone can help me out here.
Thanks!
You need to list the variables out in that macro instead of using the shortcut reference. Since it does what you want I suggest building a macro variable using SQL and passing that to the macro. Assuming your variables are ordered, which would need to be the case for your code to have worked, and that the first variable is 3 and the last is 103 then something like this would work.
proc sql noprint;
select name into :var_list separated by " "
from sashelp.vcolumn
where upper(libname) = 'WORK' and upper(memname) = 'IN'
and varnum between 3 and 103;
quit;
Then you can call the macro using:
%winsor(dsetin=in, dsetout=out, byvar=none, vars=&var_list, type=winsor, pctl=1 99)
@daradanye wrote:
Hi,
I am trying to winsorize a lot of variables. I searched and found there are so many macros out there. The macro I am currently using is this one: http://www.wrds.us/index.php/repository/view/sas_winsorize_macro
However, I have more than 50 variables to be winsorized. And the names of the 50 variables are not quite regular. I tried to quote the macro above in the following way. But it did not work out.
%winsor(dsetin=in, dsetout=out, byvar=none, vars=atg--umh, type=winsor, pctl=1 99)
I will appreciate it very much someone can help me out here.
Thanks!
If you have SAS/IML . That would be easy .
data have;
do i=1 to 100;
a=ceil(ranuni(1)*100);
b=ceil(ranuni(2)*100);
output;
end;
drop i;
run;
%let low=0.05 ;
%let high=0.95 ;
proc iml;
use have;
read all var _num_ into x[c=vname];
close have;
call qntl(q,x,{&low ,&high});
do i=1 to ncol(x);
x[loc(x[,i]<q[1,i]),i]=q[1,i];
x[loc(x[,i]>q[2,i]),i]=q[2,i];
end;
create want from x[c=vname];
append from x;
close want;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.