topic Re: Winsorize a lot of variables in SAS Programming

Winsorize a lot of variables

daradanye — Tue, 22 Jan 2019 03:25:15 GMT

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!

Re: Winsorize a lot of variables

Reeza — Tue, 22 Jan 2019 03:39:42 GMT

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!

Re: Winsorize a lot of variables

Ksharp — Tue, 22 Jan 2019 12:44:48 GMT

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;