topic Re: Percentile calculation for a table in SAS Procedures

Percentile calculation for a table

varun1220 — Wed, 04 Nov 2015 00:33:36 GMT

I have a table of 6 columns and 8760 rows filled with numerical values. I need to find 99 percentiles for each row which has 6 values.

Below is the gist of the table:

Load_04	Load_05	Load_06	Load_07	Load_08	Load_09
2433.896	2323.323	2720.163	2708.686	2742.605	2925.509
2381.1	2299.548	2649.212	2576.808	2638.918	2717.989
2421.829	2376.882	2595.548	2474.217	2604.292	2749.657
2485.082	2487.375	2595.651	2434.356	2579.163	2728.158
2559.993	2573.468	2607.336	2459.65	2602.624	2795.224

I want to calculate all the percentiles for each row for combined six values.

Ex: For first row, I want the 99 percentiles of the values

2433.896

2323.323

2720.163

2708.686

2742.605

2925.509

together.

Similarly for each row.

I have tried proc univariate for this purpose, the result is not for every row but for every column. Hence I tried transposing the table but SAS can't take 8760 variables at a time.

Kindly suggest a solution.

Thanks a lot in advance.

Varun.

Re: Percentile calculation for a table

Jagadishkatam — Tue, 03 Nov 2015 00:59:47 GMT

i believe we need to transpose data to columns and then use proc means to calculate the percentile

data have;
input Load_04 	Load_05 	Load_06 	Load_07 	Load_08 	Load_09;
cards;
2433.896 	2323.323 	2720.163 	2708.686 	2742.605 	2925.509
2381.1 	2299.548 	2649.212 	2576.808 	     2638.918 	    2717.989
2421.829 	2376.882 	2595.548 	2474.217 	2604.292 	2749.657
2485.082 	2487.375 	2595.651 	2434.356 	2579.163 	2728.158
2559.993 	2573.468 	2607.336 	2459.65 	2602.624 	2795.224
;

proc transpose data=have out=want;
var  Load_04 	Load_05 	Load_06 	Load_07 	Load_08 	Load_09;
run;

proc means data= want ;
var  col1-col5;
output out=p99 p99=/autoname;
run;

Thanks,

Jag

Re: Percentile calculation for a table

stat_sas — Tue, 03 Nov 2015 01:55:30 GMT

Use pctl function to get percentile for each row:

data want(drop=i);
set have;
array load(*) load:;
do i=1 to dim(load);
p_99=pctl(99,of load(*));
end;
run;

Re: Percentile calculation for a table

Reeza — Tue, 03 Nov 2015 15:25:59 GMT

The 99th percentile will either be the maximum or the second largest value, when you have only 6 values. Look at using the largest or max function as it may be more efficient than the percentile function.

Re: Percentile calculation for a table

Rick_SAS — Tue, 03 Nov 2015 17:47:49 GMT

As Reeza pointed out, this exercise doesn't make sense. In general, you need more than 100 values before the 99th percentile becomes meaningful. It looks like your data does not contain duplicate or rounded values, so the only relevant percentiles are 16.6th, 33.3th,...,and 87.6th. If you want the max of each row, that is easy to do with the DATA step and an array.

Re: Percentile calculation for a table

varun1220 — Tue, 03 Nov 2015 23:40:53 GMT

I need to calculate all the 99 percentiles for my assignment as I'm performing probablistic forecasting.

Re: Percentile calculation for a table

varun1220 — Tue, 03 Nov 2015 23:45:40 GMT

Thank you for the asnwer. This seems very helpful.

Could you please tell me what does the function

array load(*)load:

do?

What does load(*) stand for?

Kindly reply as per your convenience.

Re: Percentile calculation for a table

varun1220 — Wed, 04 Nov 2015 00:09:22 GMT

Thank you Jagdish. This is a helpful answer.

Varun.

Re: Percentile calculation for a table

stat_sas — Wed, 04 Nov 2015 03:29:18 GMT

array load(*) load:;

load is name of array and (*) indicates the dimension of array, represents number of elements in the array which is 6 in this case. If we indicate dimension of the array using (*) then SAS determines demension of the array by counting number of elements.

load: represents 6 elements in the array (load1 load2 load3 load4 load5 load6)

array load(*) load:;

can also be written as

array load(6) load1 load2 load3 load4 load5 load6;

Re: Percentile calculation for a table

Reeza — Wed, 04 Nov 2015 16:33:35 GMT

Sometimes its good to think about why you do what you're doing. Would your answer be different in this case if you used the 95th percentile? Or 90th percentile? Most likely not because you only have 6 variables so they would all end up being the same number.

Re: Percentile calculation for a table

varun1220 — Mon, 09 Nov 2015 21:33:03 GMT

Sincere apologies for a delayed reply.

I was implemented this scenario in excel, the values weren't similar. I almost got 99 different values when I used the PERCENTILE.INC function over the 6 variables I mentioned earlier in the post.

Another point, would the logic be same if I want to get 100 percentiles of 20 variables instead of 6? 6 was just an example.

Thank you,

Varun.

Re: Percentile calculation for a table

varun1220 — Mon, 09 Nov 2015 21:34:11 GMT

Great. Thank you so much.

Re: Percentile calculation for a table

Reeza — Mon, 09 Nov 2015 21:48:15 GMT

100 percentiles is the maximum.

Percentiles have various definitions on how to calculate them, mostly around how to handle ties.

SAS supports 5 definitions through proc univariate and I don't think any align to Excel definitions.

https://support.sas.com/documentation/cdl/en/procstat/63104/HTML/default/viewer.htm#procstat_univariate_sect028.htm

Re: Percentile calculation for a table

varun1220 — Sat, 21 Nov 2015 03:20:43 GMT

I am in situation where I have more than 99 values and I need calculate the 99 percentiles of each of set of values. The table consists of 8760 rows and more than 100 columns.

I need to calculate 99 percentiles for each row.

Kindly revert as per your convenience.

Re: Percentile calculation for a table

Reeza — Sat, 21 Nov 2015 18:35:19 GMT

The answers from Nov 2 are correct. Either transpose your data and use proc means or use the percentile function. If those dont work you need to explain why so we can help further.