Learning SAS? Welcome to the exclusive online community for all SAS learners.

P value from Proc Univariate in Wilcoxon Signed rank test doesn't match Excel Wilcoxon signed rank test

Reply
Occasional Contributor
Posts: 9

P value from Proc Univariate in Wilcoxon Signed rank test doesn't match Excel Wilcoxon signed rank test

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 !

PROC Star
Posts: 1,231

Re: P value from Proc Univariate in Wilcoxon Signed rank test doesn't match Excel Wilcoxon signed rank test

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.

Super User
Super User
Posts: 7,401

Re: P value from Proc Univariate in Wilcoxon Signed rank test doesn't match Excel Wilcoxon signed rank test

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.

Occasional Contributor
Posts: 9

Re: P value from Proc Univariate in Wilcoxon Signed rank test doesn't match Excel Wilcoxon signed rank test

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:

Data:

Before After

125     110

115      122

130       125

140       120

140        140

115       124

140       123

125       137

140        135

135        145

In SAS:

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 ;

run;

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.

In Excel:

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.

Super User
Posts: 17,818

Re: P value from Proc Univariate in Wilcoxon Signed rank test doesn't match Excel Wilcoxon signed rank test

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.

Occasional Contributor
Posts: 9

Re: P value from Proc Univariate in Wilcoxon Signed rank test doesn't match Excel Wilcoxon signed rank test

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.

PROC Star
Posts: 1,231

Re: P value from Proc Univariate in Wilcoxon Signed rank test doesn't match Excel Wilcoxon signed rank test

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:

http://support.sas.com/documentation/cdl/en/procstat/65543/HTML/default/viewer.htm#procstat_univaria...

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...

Occasional Contributor
Posts: 9

Re: P value from Proc Univariate in Wilcoxon Signed rank test doesn't match Excel Wilcoxon signed rank test

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.


Super User
Posts: 17,818

Re: P value from Proc Univariate in Wilcoxon Signed rank test doesn't match Excel Wilcoxon signed rank test

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.

Super User
Posts: 9,676

Re: P value from Proc Univariate in Wilcoxon Signed rank test doesn't match Excel Wilcoxon signed rank test

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.





Xia Keshan

Occasional Contributor
Posts: 9

Re: P value from Proc Univariate in Wilcoxon Signed rank test doesn't match Excel Wilcoxon signed rank test


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.

Super User
Posts: 17,818

Re: P value from Proc Univariate in Wilcoxon Signed rank test doesn't match Excel Wilcoxon signed rank test

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. 

Occasional Contributor
Posts: 9

Re: P value from Proc Univariate in Wilcoxon Signed rank test doesn't match Excel Wilcoxon signed rank test

I have read many documents from last week Smiley Happy  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 !!

Super User
Posts: 17,818

Re: P value from Proc Univariate in Wilcoxon Signed rank test doesn't match Excel Wilcoxon signed rank test

Occasional Contributor
Posts: 9

Re: P value from Proc Univariate in Wilcoxon Signed rank test doesn't match Excel Wilcoxon signed rank test


Thanks Reeza ! yeah i read that.

Ask a Question
Discussion stats
  • 18 replies
  • 2014 views
  • 3 likes
  • 5 in conversation