DATA Step, Macro, Functions and more

Need help in Proc Sql

Accepted Solution Solved
Reply
Super Contributor
Posts: 265
Accepted Solution

Need help in Proc Sql

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


Accepted Solutions
Solution
‎11-17-2014 05:15 AM
Super User
Super User
Posts: 7,430

Re: Need help in Proc Sql

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.

View solution in original post


All Replies
Solution
‎11-17-2014 05:15 AM
Super User
Super User
Posts: 7,430

Re: Need help in Proc Sql

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.

Super Contributor
Posts: 265

Re: Need help in Proc Sql

Thanks

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 163 views
  • 0 likes
  • 2 in conversation