topic Need help in Proc Sql in SAS Programming

Need help in Proc Sql

Aman4SAS — Mon, 17 Nov 2014 10:00:09 GMT

data test;

input x;

datalines;

23434

675

345

355

234

654

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

Re: Need help in Proc Sql

RW9 — Mon, 17 Nov 2014 10:15:20 GMT

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.

Re: Need help in Proc Sql

Aman4SAS — Mon, 17 Nov 2014 10:27:22 GMT

Thanks