Desktop productivity for business analysts and programmers

Using Query builder to create percentile stats

Reply
Contributor
Posts: 72

Using Query builder to create percentile stats

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
SAS Employee
Posts: 149

Re: Using Query builder to create percentile stats

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. Smiley Happy
Frequent Contributor
Posts: 81

Re: Using Query builder to create percentile stats

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

.....Phil
Contributor
Posts: 72

Re: Using Query builder to create percentile stats

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.
Frequent Contributor
Posts: 81

Re: Using Query builder to create percentile stats

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
Contributor
Posts: 72

Re: Using Query builder to create percentile stats

I'll give it a shot and let you know how I do Smiley Happy.

Cheers!
Contributor
Posts: 72

Re: Using Query builder to create percentile stats

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

Re: Using Query builder to create percentile stats

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
Contributor
Posts: 72

Re: Using Query builder to create percentile stats

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.
Ask a Question
Discussion stats
  • 8 replies
  • 2936 views
  • 0 likes
  • 3 in conversation