JMP 16.2 Equivalent to Excel PERCENTILERANK Function?

[#1]

Would like to find the percentile of a number within a data set. What percentage of values are above/below a certain number? Other than eyeballing a CDF or Prob Dist chart is there an existing path to get this number or is this a JSL task?

[#2]

You can use the Quantiles statistic within the Tabulate platform (Analyze>Tabulate).

[#3]

You can do this by a formula or JSL snippet as you like, see screenshot and script below.

The first part of the formula generates the table for values and percentiles, the second looks up the percentile for the current row.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA\Big Class.jmp" );

dt << New Column( "PercentileRank",
	Formula(
		cdf_arr = CDF( :weight << get values );
		cdf_arr[2][Loc( cdf_arr[1], :weight )];
	)
);

[#4]

Georg:

Thank you for your input. I adjusted the JSL to match my table and column name but I am guessing this is a very processor intensive calculation. It has been over 20 minutes and the wheels are still turning. I have about 240K rows of data to deal with. It looks like I'll stick with the Excel method I set up until I can find time to investigate this issue in more depth. Thanks again.

Greg

[#5]

David:

Thank you for the response. I was familiar with the Quantile statistic. With this I am specifying the Quantile and JMP is generating the value that matches that Quantile. I want to specify the value and generate the Quantile. My initial explanation was insufficient.

Greg

[#6]

Here is a JMP Function using the exact methodology used in the Excel function, PERCENTILERANK as described in the MicroSoft Excel help page

https://support.microsoft.com/en-us/office/percentrank-function-f1b5836c-9619-4847-9fc9-080ec9024442 

Below I have provided the example table from the help page, along with the JMP function I created

Names Default To Here( 1 );

New Table( "Excel Example",
	Add Rows( 10 ),
	New Column( "Data",
		Numeric,
		"Nominal",
		Format( "Best", 12 ),
		Set Values( [13, 12, 11, 8, 4, 3, 2, 1, 1, 1] )
	)
);

dt = Current Data Table();

percentRank = Function( {col, value},{default local},
	mydt = Current Data Table();
	theValue = .;
	theRows = mydt << get rows where( As Column( mydt, col ) == value );
	If( N Rows( theRows ) > 0,
		gt = Col Number( If( As Column( mydt, col )[Row()] > value, Column( mydt, col )[Row()], . ) );
		lt = Col Number(
			If( As Column( mydt, col )[Row()] < value & Is Missing( As Column( mydt, col )[Row()] ) == 0,
				As Column( mydt, col )[Row()],
				.
			)
		);
		theValue = lt / (lt + gt);
	,
		lower = upper = .;
		lower = Col Max( If( As Column( mydt, col ) < value & Is Missing( As Column( mydt, col )[Row()] ) == 0, Column( mydt, col )[Row()], . ) );
		upper = Col Min( If( As Column( mydt, col ) > value, Column( mydt, col )[Row()], . ) );
		
		// get the percentileRank for upper and lower
		upperPR = percentRank(col,upper);
		lowerPR = percentRank(col,lower);
		intervalPCT=(value-lower)/(upper-lower);
		theValue = lowerPR+(intervalPCT*(upperPR-lowerPR));

	);
	Return( theValue );
);

x = percentRank( "data", 8 );  // a data point found in the data
show("found in table",x);
x = percentRank( "data", 5 );  // a data point not found in the data
show("Not found in table",x);

[#7]

Jim:

Thank you for coding example provided. As I am trying to improve my JSL skills these types of examples are great tutorials. Once again I appreciate your input.

Greg

[#8 · Accepted solution]

Hi @WoHNY ,

while others have provided fantastic solutions let me offer a more basic one. try the following script it is fast enough for me on a mediocre computer.

Names Default To Here( 1 );

dt = New Table( "rank percentile - example",
	New Column( "X", Numeric, "Continuous",  Formula( Random Normal( 20, 5 ) ), Set Selected ),
	New Column( "rank percentile", Numeric, "Continuous", Format( "Percent", 12, 0 ), Formula( Col Rank( :X ) / Col Number( :X ) ) )
);
dt << Add Rows( 1000000000 );

Ron

[#9]

Ron:

Many thanks. It works great.

Greg