data test;
input x;
datalines;
43
42
55
63
23434
34
56
65
675
345
355
234
654
35
34
565
;
run;
proc sql;
select distinct count(*) as count from test having (x < (mean(x)+(.20*std(x))) and x > (mean(x)-(.20*std(x))));
quit;
i m getting result 16, i want to remove outlier by this code.
Please help ..
Thanks in advance
Hi,
Break your SQL down a bit so that you can see what is happening:
proc sql;
create table INTER as
select *
from (select X,
mean(X) + (.20 * std(X)) as HI,
mean(X) - (.20 * std(X)) as LO
from TEST)
where LO < X < HI;
quit;
With the above code its quite simple to copy the sub-query out into another proc sql clause and see the data, i.e. see what hi/lo is actually set to. You can then easily predict what goes into the where clause. As for the having statement, I have to admit to avoiding using that as much as possible. I prefer setting the data and passing that in rather than this statement as having just doesn't seem to do what you think it should do 95% of the time.
Hi,
Break your SQL down a bit so that you can see what is happening:
proc sql;
create table INTER as
select *
from (select X,
mean(X) + (.20 * std(X)) as HI,
mean(X) - (.20 * std(X)) as LO
from TEST)
where LO < X < HI;
quit;
With the above code its quite simple to copy the sub-query out into another proc sql clause and see the data, i.e. see what hi/lo is actually set to. You can then easily predict what goes into the where clause. As for the having statement, I have to admit to avoiding using that as much as possible. I prefer setting the data and passing that in rather than this statement as having just doesn't seem to do what you think it should do 95% of the time.
Thanks
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.