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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.