topic how to remove outliers in each sub group? in SAS Programming

how to remove outliers in each sub group?

JKCho — Sun, 03 Apr 2022 16:33:50 GMT

I obtained the data like the above. What I am trying to do is to remove the top/bottom 1 percentile for each quarter in each year.

I have tried to find in this community but could not find any about removing outliers in sub-categories. I guess I can specify a variable to be removed (gaap and non-g in this case) and can also specify subgroups (QTR and year).

Thank you for your help!! 🙂

Re: how to remove outliers in each sub group?

PaigeMiller — Mon, 04 Apr 2022 12:19:39 GMT

To identify the TOP and BOTTOM 1-percentile of each year and quarter, use PROC RANK.

proc rank data=have out=ranked groups=100;
    by year qtr;
    var gaap non_g;
    ranks r_gaap r_non_g;
run;

This creates 100 groups in variables r_gaap and r_non_g in data set RANKED, each with numbers 0 to 99 within each YEAR and QTR combination, where 0 is the 1-st percentile. From there, a simple data step with a WHERE statement will remove the bottom 1-percentile and top 1-percentile within each year and qtr.

Better yet (in my opinion) instead of removing observations: turn the lowest 1-percentile and highest 1-percentile into missing values, as removing observations will be problems if for an observation gaap is lowest 1-percentile but non_g is not lowest 1-percentile.

Re: how to remove outliers in each sub group?

JKCho — Mon, 04 Apr 2022 23:52:59 GMT

oh, this way is gorgeous!
I just come up with
data=one set=have;
if year=2000; qtr=1;
run;

and making something like the above 80 times... you saved me! thank you 🙂

Re: how to remove outliers in each sub group?

PaigeMiller — Tue, 05 Apr 2022 09:50:27 GMT

@JKCho wrote:
oh, this way is gorgeous!
I just come up with
data=one set=have;
if year=2000; qtr=1;
run;

and making something like the above 80 times... you saved me! thank you 🙂

@JKCho that's ridiculous to do it 80 times. One DATA step does it for all combinations of YEAR and QTR.