topic Re: SAS PROGRAMMING in Statistical Procedures

Percentiles with PROC UNIVARIATE

molla — Fri, 09 Jul 2021 17:23:07 GMT

While doing percentile calculation using proc univariate, I could not get the expected output,pls help me for the same.

PROC UNIVARIATE DATA=DATA_SET EXCLNPWGCT;
VAR VAR1;WEIGHT VAR1;
OUTPUT OUT=OUTDATASET PCTLPRE=P_PCTLPTS=12.5 25 27.5 50 62.5 75 87.5 100;
RUN;

the following are the values in the var1 column of the data_set table after excluding the zero and negative values:

19967.95
19271.69
16525.2
6885.5
3442.75

percentile
12.5
25
37.5
50
62.5
75
87.5
100

expected o/p
5164.125
6885.5
11705.35
16525.2
17898.44412
19271.68824
19619.81912
19967.95

output coming
6885.5
16525.2
16525.2
19271.68824
19271.68824
19967.95
19967.95
19967.95

Appreciate your help.Thanks

Re: SAS PROGRAMMING

ballardw — Fri, 09 Jul 2021 20:36:33 GMT

The option PCTLDEF= sets which of five different methods are used to calculate percentiles. Read the documentation for the different meanings. Or just try PCTLDEF= 1 (through 4, 5 is the default method and you don't like that) and see if any of the results match expectations.

The presence of a WEIGHT variable significantly affects the results. I have to say that seeing the same variable on a VAR and WEIGHT statement makes me queasy.

Re: Percentiles with PROC UNIVARIATE

molla — Fri, 09 Jul 2021 17:25:02 GMT

to remove the zero and negative values I used weight option ,by trying PCTLDEF option also am not getting the required output

Re: SAS PROGRAMMING

PaigeMiller — Fri, 09 Jul 2021 11:11:50 GMT

@molla wrote:
to remove the zero and negative values I used weight option ,by trying PCTLDEF option also am not getting the required output

Be specific: which PCTLDEF options did you try?

Re: Percentiles with PROC UNIVARIATE

molla — Fri, 09 Jul 2021 17:24:20 GMT

I tried PCTLDEF = 1

Re: Percentiles with PROC UNIVARIATE

molla — Fri, 09 Jul 2021 17:23:50 GMT

I have tried with other options as well but not getting the exact output

Re: SAS PROGRAMMING

PaigeMiller — Fri, 09 Jul 2021 14:44:22 GMT

Try PCTLDEF = 2 and PCTLDEF=3 and PCTLDEF=4

Re: SAS PROGRAMMING

Tom — Fri, 09 Jul 2021 14:48:53 GMT

How do you expect it to assign distinct values for the 8 different precentiles when your input only has 5 different values?

Re: SAS PROGRAMMING

ballardw — Fri, 09 Jul 2021 16:50:40 GMT

Here is an example of what sort of thing might happen when you have "weights" based on the variable you are requesting percentiles about. This uses the same input data set with and without weights.

Please look at the result and see if one of them comes closer to what you expect.

data example;
  do x= 1 to 25;
  output;
  end;
run;

title "with weight";
proc univariate data=example EXCLNPWGT;
 VAR x;
 WEIGHT x;
 OUTPUT OUT=OUTDATASET1 PCTLPRE=P_ PCTLPTS=12.5 25 27.5 50 62.5 75 87.5 100;
RUN;

title "without weight";
proc univariate data=example EXCLNPWGT;
 VAR x;
 OUTPUT OUT=OUTDATASET2 PCTLPRE=P_ PCTLPTS=12.5 25 27.5 50 62.5 75 87.5 100;
RUN;
title;

This is admittedly moderately "nice" data but the purpose is to illustrate that weights and percentiles are a somewhat complex issue and using the same value for the weight is problematic at best.

Re: Percentiles with PROC UNIVARIATE

Kurt_Bremser — Fri, 09 Jul 2021 17:26:33 GMT

I edited your meaningless subject line. Please use a more descriptive one next time.

Re: Percentiles with PROC UNIVARIATE

Rick_SAS — Fri, 09 Jul 2021 20:33:18 GMT

The percentiles you list are Definition #7 in the Hyndman and Fan (1997) lists of nine definitions for sample quantiles. This is the default in R. SAS does not natively support Definition #7, but you can use a formula to obtain it. The article "Sample quantiles: A comparison of 9 definitions" provides the details and SAS code to compute all nine definitions.

The article contains the function definitions. Here's how you get your results:

proc iml;
/* get module from program at https://blogs.sas.com/content/iml/files/2017/05/QntlDef.txt */
load module=(SampQuantile);
use Q; read all var "x"; close;       
prob = {12.5,25,37.5,50,62.5,75,87.5,100} / 100;
def = 7;
quantile = SampQuantile(x, prob, def);
print def prob quantile;

Be sure the read the end of my article to understand that these definitions are all estimates for the underlying quantiles of the data-generating distribution. None is "more correct" than any other. As the samples size gets larger, the definitions all give similar estimates.

Re: Percentiles with PROC UNIVARIATE

molla — Mon, 12 Jul 2021 07:19:23 GMT

Hi,
Its some what complex,not able to understand can we do the same in proc freq or proc mean?
If yes pls let me know how to do it

Re: SAS PROGRAMMING

molla — Mon, 12 Jul 2021 07:20:07 GMT

tried bt not getting the accurate results

Re: Percentiles with PROC UNIVARIATE

Rick_SAS — Mon, 12 Jul 2021 10:05:08 GMT

OK, here's a simpler suggestion: use the default estimates from PROC UNIVARIATE or another SAS procedure.

SAS procedures provide five ways to estimate quantiles. There are other estimates, as discussed in my article. But all of these are valid estimates and, in practice, there is rarely a reason to prefer one estimate over the other.

Re: Percentiles with PROC UNIVARIATE

molla — Mon, 12 Jul 2021 13:45:45 GMT

By using the five ways of sas to estimate quantiles am not getting the accurate output,not able to understand how to implement the way which u have explained

Re: Percentiles with PROC UNIVARIATE

Rick_SAS — Mon, 12 Jul 2021 14:33:12 GMT

Let me say it again: quantiles are statistics, which means that they estimate underlying parameters in the population (in this case, the quantiles of the population). The values you say are "correct" are merely the sample quantiles that R computes by default. There are many definitions of sample quantiles. None are more correct than the others.

The following SAS/IML program simplifies the program in my blog post and computes only the TYPE=7 definition, which is the default in R. You can run this program to obtain the sample quantiles that you want.

/* Compute the sample quantiles that R computes by default */
proc iml;
/* Define function that returns the TYPE=7 sample quantiles. For more info, see
   https://blogs.sas.com/content/iml/2017/05/24/definitions-sample-quantiles.html
*/
start GetRQuantiles(y, probs);
   x = colvec(y);
   call sort(x);
   N = nrow(x);       /* assume all values are nonmissing */
   
   p = colvec(probs);
   m = 1-p;
   j = floor(N*p + m);
   g = N*p + m - j;

   q = j(nrow(p), 1, x[N]);    /* if p=1, x[N]=return max(x) */
   idx = loc(p < 1);
   if ncol(idx) >0 then do;
      j = j[idx]; g = g[idx];
      q[idx] = (1-g)#x[j] + g#x[j+1];
   end;   
   return q;
finish;

use Q; read all var "x"; close;       /* read sample into x */
p = {12.5, 25, 37.5, 50, 62.5, 75, 87.5, 100} / 100;  /* define probabilities */
q = GetRQuantiles(x, p);  /* sample quantiles */
print p q;

Re: Percentiles with PROC UNIVARIATE

molla — Mon, 12 Jul 2021 19:26:20 GMT

I tried using the above code
but not able to understand how can I use the below coding in sas

use Q; read all var "x"; close; /* read sample into x */
p = {12.5, 25, 37.5, 50, 62.5, 75, 87.5, 100} / 100; /* define probabilities */
q = GetRQuantiles(x, p); /* sample quantiles */
print p q;

Re: Percentiles with PROC UNIVARIATE

molla — Mon, 12 Jul 2021 19:28:39 GMT

that coding part is in R as per my understanding,how can we use the same in SAS,
Is there any other way in computing the quantiles other than this to get the accurate results

Re: Percentiles with PROC UNIVARIATE

Tom — Mon, 12 Jul 2021 19:42:35 GMT

So did you create a dataset named Q with a variable named X for the IML code to read int?

data q;
  input x;
cards;
19967.95
19271.69
16525.2
6885.5
3442.75
;



proc iml;
/* Define function that returns the TYPE=7 sample quantiles. For more info, see   https://blogs.sas.com/content/iml/2017/05/24/definitions-sample-quantiles.html*/
start GetRQuantiles(y, probs);
   x = colvec(y);
   call sort(x);
   N = nrow(x);       /* assume all values are nonmissing */
   
   p = colvec(probs);
   m = 1-p;
   j = floor(N*p + m);
   g = N*p + m - j;

   q = j(nrow(p), 1, x[N]);    /* if p=1, x[N]=return max(x) */
   idx = loc(p < 1);
   if ncol(idx) >0 then do;
      j = j[idx]; g = g[idx];
      q[idx] = (1-g)#x[j] + g#x[j+1];
   end;   
   return q;
finish;

use q; read all var "x"; close;       /* read sample into x */
p = {12.5, 25, 37.5, 50, 62.5, 75, 87.5, 100} / 100;  /* define probabilities */
q = GetRQuantiles(x, p);  /* sample quantiles */
print p q;

quit;

Output

Re: Percentiles with PROC UNIVARIATE

Rick_SAS — Mon, 12 Jul 2021 19:44:21 GMT

I am trying to complete a project, so I will let others help you. Good luck!