Using Query builder to create percentile stats

[TheChadd]

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

[RichardH_sas]

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. 🙂

[prholland]

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

.....Phil

[TheChadd]

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.

[prholland]

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

[TheChadd]

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

Cheers!

[TheChadd]

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

[prholland]

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

[TheChadd]

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.