01-19-2015 10:35 AM
I am doing Wilcoxon Signed rank test for n=12 data points. The data has before and after numbers and i am testing seeing the statistical significance using SAS Proc Univariate. I also did this in Excel to compare the results . The P values in both are different and i am not sure about the reason behind this. The Signed rank value S is same as i calculated that. I would really appreciate if anyone can help me on this. Thanks !
01-19-2015 10:42 AM
Generally, there are so many problems with Excel....
That said, would suggest you post your 12 records, and the p-values you get from SAS and excel, and perhaps describe how you did it in Excel.
01-19-2015 11:15 AM
Agree with Quentin. Excel is not a statistical tool, anymore than its a database, data capture, <insert anything else you can think of> tool. If your worried about the result - and to be honest, you will have a paid a fair bit for a licence so should not have to worry about these things, then do the same in R. I would suggest that its 99.9% likely to be Excel.
01-19-2015 02:29 PM
Thanks Quentin & RW9 for your suggestions. I am posting the hypothetical data as i can't post the original data. I have checked this in both Excel and SAS and i am posting the steps below:
1) I pasted these values as is in Excel sheet. Then i imported this in SAS enterprise using import. The data was as is automatically converted into Num and SAS data set was created.
2) Then i created a new variable called diff = before-after and did proc univariate
proc univariate data=test;
var diff ;
3) this gave me the bunch of statistical results and i think for WIlcoxon signed rank we need to see Signed rank (S)= 4.5 and Pr> =ISI = 0.6328
I am not sure how to interpret these and determine the significance. I know how to calculate the value of S= 4.5 and it is correct . but not the P value here.
1) I use Real statistics addin in Excel and just ran the non-parametric test for two paired samples, alpha=.05, exact test, ties correction.
2) This gave me all the necessary values in one tail and we can double it for two tail.
I got one tail P vaule= .29421, two tail P value= >58842
then in excel it compares Tcrit by looking at Z score value from the Wilcoxon table and produces the results for significance.
Now my project requires me to do this in SAS and we have been doing it in excel. Now that my results doesn't match , i am not sure if i am doing something wrong in SAS.
01-19-2015 02:41 PM
Since this is for school, I'd actually recommend you follow the manual process in Excel rather than using the RealStat's add in and see how it works.
Note how it deals with ties.
01-19-2015 02:53 PM
We need to automate this so can't go manual. Before i proceed with my project i need to figure out the difference in SAS from Excel or if it is something i am not doing right in SAS.
01-19-2015 02:57 PM
You said you got the same value for the test statistic (4.5) from both SAS and Excel, right? That's good news.
So the only issue is how to get the p-value from the test statistic.
You mentioned a Z-Score in the Excel setting, suggesting it's using a normal approximation. Likely SAS is using an exact method, given n=10.
Here is a link to SAS docs with details:
Agree with , if you're going to use Excel, probably better to use it like a calculator and do it by hand, rather than use an add-in. Or better yet, just go old school and do it by hand (with a calculator), and look up the test statistic in a table...
01-19-2015 03:04 PM
I did by hand until how its calculating the test statistics. But not sure how it calculates P value in excel. I need to find that so i can do that manually and solve the probelm. But that's what i need help with . Also would you know how to interpret the SAS results of P value to check its significance. because it doesn't provide anything else like in excel it states very clearly if its significant or not.
01-19-2015 03:20 PM
Significance is subjective not objective, although many people treat 0.05 as the magic cutoff. Is 0.043 really that different from 0.053? Unless you specify a cutoff an application should not be able to say significant or insignificant but provide a p-value.
01-20-2015 02:31 AM
If I was right, Wilcoxon test should be done via proc npar1way . Due to your small sample ,EXACT test should be better .
I used both EXACT test and Approximation test .
data test; input Before After ; group='placebo '; value=before;output; group='treatment'; value=after;output; drop Before After; cards; 125 110 115 122 130 125 140 120 140 140 115 124 140 123 125 137 140 135 135 145 ; run; proc npar1way data=test wilcoxon; class group; var value ; exact wilcoxon; run; The NPAR1WAY Procedure Wilcoxon Scores (Rank Sums) for Variable value Classified by Variable group Sum of Expected Std Dev Mean group N Scores Under H0 Under H0 Score ------------------------------------------------------------------------- placebo 10 114.50 105.0 13.098815 11.450 treatment 10 95.50 105.0 13.098815 9.550 Average scores were used for ties. Wilcoxon Two-Sample Test Statistic (S) 114.5000 Normal Approximation Z 0.6871 One-Sided Pr > Z 0.2460 Two-Sided Pr > |Z| 0.4920 t Approximation One-Sided Pr > Z 0.2502 Two-Sided Pr > |Z| 0.5003 Exact Test One-Sided Pr >= S 0.2439 Two-Sided Pr >= |S - Mean| 0.4877 Z includes a continuity correction of 0.5.
01-20-2015 09:56 AM
Thanks Xia !
The way you grouped it, it produced wilcoxon rank sum test. My samples are not independent and are paired. So i need to do Wilcoxon Signed rank test. What i am unable to understand is the difference in excel P value to my sas P value. And how SAS does the calculation.
01-20-2015 10:12 AM
Have you checked the detail section of the documentation?
One issue is that your sample size is so small. Other online tools will say itd too small to calculate an accurate p-value especially with 0 and ties. There is no fixed rule on what distribution to use in such a case.
01-20-2015 10:20 AM
I have read many documents from last week Each one explains the overall method but none of them explained how to interpret it. I saw that about sample size too. If you find any document that you think is useful then please post here. Thanks !!