TINV Function

[Bindu_Borde]

I am planning to convert an excel code into SAS

ter is a function called TINV used in Excel,  same function is also in SAS too with same syntax, but i am getting two different results for the  same  numbers, please help me if both are different functions are the Same.

SAS

TINV(0.95,2)

Result:

2.9199855804

Excel

TINV(0.95,2)

Result

0.070799

[RW9]

There is no way to accurately answer this question.  SAS and Excel are different software, produced by different companies for different purposes uses different formula.  A simple search on the Excel function reveals some interesting information:

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheetfunction-tinv-method-excel

Read the Important part.  Seems like that is an old function that is outdated.  Personally given the choice I would always trust the SAS functions, as we pay a lot of money in licensing for them to ensure that the software is validated.  Excel is not. 

Now I don't know these functions at all, never used them, but looking at the text present, 

Excel TINV = Returns the t-value of the Student's t-distribution as a function of the probability and the degrees of freedom.

SAS TINV = Returns a quantile from the t distribution

Sounds to me like two very different functions.  I would start by getting the owner of that data to explain what is actually needed.  From that you can draw up a Functional Design Specification - this is so the code is properly documented, and you don't have to go through this paper trail hunt in the future.  With that specification, coding should be very simple.  As with any programming task - documentation is 99% of the problem.

[StatsMan]

You can find documentation for the Excel TINV function here.  The important part of the documentation is the line " TINV(p, df) is the inverse function for TDIST(x, df, 2). ", which tells us that TINV in Excel is returning the quantile from a 2-tail t.  The TINV function, or the more modern QUANTILE function in SAS, returns 1-tail results. 

Let's look at an example.  Put =TINV(.05,12) into a cell in your Excel spreadsheet.  That calculation returns the value 2.178813.  What does that value represent?  In Excel, the value returned is associated with the probability of observing a t-statistic greater than 2.178813 or less than -2.178813. That calculation takes the p-value provided (.05) and puts half of that (.025) in each tail.

In SAS, the TINV or QUANTILE function returns a 1-tail result.  The quantile, q, returned from the QUANTILE('t',.05,12) function in SAS satisfies p(t<q)=.05.  If you use QUANTILE('t',.05,12) then SAS returns -1.782287556.  

If you want to match the result from the TINV function in Excel, then take 1-half the p-value used as the argument to the QUANTILE function in SAS.  Using QUANTILE('t',.025,12) will return -2.178813.  QUANTILE returns the left-tailed quantile, while Excel's TINV returns the right-tailed quantile, which explains the difference in sign.

[MelissaM]

Phenomenal explanation and example! Thank you!