topic Re: Using Query builder to create percentile stats in SAS Enterprise Guide

Using Query builder to create percentile stats

TheChadd — Fri, 19 Feb 2010 22:48:48 GMT

I have a simple table of two columns: Week and Value (numeric, continuous). Ultimately I want to plot the 99.8th percentile of the Value by Workweek. Using the Query Builder on the table, I tried using PCTL function: (PCTL(99.8,Value). However, it will not support grouping for this function in the interface. I have also tried doing this manually:
PROC SQL;
CREATE TABLE SASUSER.temp1 AS
SELECT t1.Week,
(PCTL(99.8,t1.'OQM-FT1 Hrs'n)) FORMAT=BEST7.2 AS '99.8th percentile'n
FROM SASUSER.Table t1
GROUP BY t1.Week;
QUIT;
I get the Warning in the log:
"WARNING: A GROUP BY clause has been transformed into an ORDER BY clause because neither the SELECT clause nor the optional HAVING clause of the associated table-expression referenced a summary function."

So this just returns the Value, instead of the 99.8th percentile of the Value by Week.

Any ideas??

Cheers!
Chadd

Re: Using Query builder to create percentile stats

RichardH_sas — Sun, 21 Feb 2010 03:12:53 GMT

I've always used the Summary Statistics task to do percentiles. I'm not 100% sure that the query builder can even do percentiles... but I'm happy to be proven wrong. 🙂

Re: Using Query builder to create percentile stats

prholland — Mon, 22 Feb 2010 11:56:13 GMT

I've not been able to get PCTL() to work in PROC SQL. It seems to require multiple arguments to work correctly. 😞

.....Phil

Re: Using Query builder to create percentile stats

TheChadd — Mon, 22 Feb 2010 15:51:39 GMT

I just want a data table with the 99.8th percentile by week to show up on my process flow. Can Summay Stats do that? When I played around with it, it would output the summary table graphically, but not as a usable table in my process flow.

Re: Using Query builder to create percentile stats

prholland — Mon, 22 Feb 2010 16:12:26 GMT

You should be able to save the results data from Summary Stats in a SAS data set, which you'll be able to use elsewhere.

........Phil

Re: Using Query builder to create percentile stats

TheChadd — Mon, 22 Feb 2010 16:16:44 GMT

I'll give it a shot and let you know how I do :).

Cheers!

Re: Using Query builder to create percentile stats

TheChadd — Mon, 22 Feb 2010 20:12:44 GMT

Well how about that. Neither Summary Statistics nor Summary Tables let me define a percentile. Am I missing something here?

Re: Using Query builder to create percentile stats

prholland — Mon, 22 Feb 2010 20:57:54 GMT

You can get 99.8% percentiles from the Tables > "Basic confidence intervals" in Distribution Analysis (PROC UNIVARIATE). You can then "Save output statistics to a data set".

.........Phil

Re: Using Query builder to create percentile stats

TheChadd — Mon, 22 Feb 2010 21:11:53 GMT

Looks like it only spits out the 99.8 LCL and UCL for the mean. I gave up and just coded in a proc univariate and merged it with the other output table.
proc univariate data=sasuser.table noprint;
var Values;
by Week;
output out=pctl pctlpts=99.8 pctlpre=Values;

If I have to manually code any more items in this project, I'll probably drop EG altogether and use plain 'ol SAS.