Need help in Proc Sql

[Aman4SAS]

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

[RW9 · Accepted solution]

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.

[Aman4SAS]

Thanks