topic Re: Macro for Wilcoxon Signed Rank Test in SAS Programming

Macro for Wilcoxon Signed Rank Test

Denali — Mon, 02 Oct 2017 21:59:15 GMT

Hi,

We have 20 students (N=20) who took pre-class test (70 questions) for the knowledge of a subject and did an exactly same test after taking the class.

We would like to know if their knowledge has improved after taking the class so I am going to run Wilcoxon signed rank test for all 70 questions from these 20 students.

Could anyone help me with turning the below code using Macro? Thank you very much!

For Question 1:

data kas2;
set kas;
diff_TC1= POSTTC1-PRETC1;
run;

proc univariate data=kas2;
var diff_TC1;
run;

For Question 2:

data kas2;
set kas;
diff_TC2= POSTTC2-PRETC2;
run;

proc univariate data=kas2;
var diff_TC2;
run;

(I am hoping not to type 70 same programs for the total 70 questions. Thanks!)

Re: Macro for Wilcoxon Signed Rank Test

Denali — Mon, 02 Oct 2017 21:55:36 GMT

I would also like to export 70 P-values from signed rank test. Could you please teach me how to do that? Thanks very much!

Macro for Wilcoxon Signed Rank Test

Denali — Mon, 02 Oct 2017 22:12:27 GMT

Hi,

We have 20 students (N=20) who took pre-class test (70 questions) for the knowledge of a subject and did an exactly same test after taking the class.

We would like to know if their knowledge has improved after taking the class so I am going to run Wilcoxon signed rank test for all 70 questions from these 20 students.

Could anyone help me with turning the below code using Macro? I would also like to export 70 P-values from signed rank test. Thank you very much!

For Question 1:

data kas2;
set kas;
diff_TC1= POSTTC1-PRETC1;
run;

proc univariate data=kas2;
var diff_TC1;
run;

For Question 2:

data kas2;
set kas;
diff_TC2= POSTTC2-PRETC2;
run;

proc univariate data=kas2;
var diff_TC2;
run;

(I am hoping not to type 70 same programs for the total 70 questions. Thanks!)

Re: Macro for Wilcoxon Signed Rank Test

Reeza — Mon, 02 Oct 2017 22:36:24 GMT

@Denali please do not post the same question multiple times.

I'm moving this to general programming rather than Statistics since there's not a statistical question here.

You don't need a macro, in fact it's more code.

First, create the diff in your kas2 data set by using arrays so all 70 are created at once.

Then run proc univariate on all at once.

data kas2;
set kas;

array pre(*) pre..... list of variables;
array postl(*) post...  list of variables;
array diff(*) diff1-diff70;

do i=1 to dim(diff);
diff(i) = post(i) - pre(i);
end;
run;

proc univariate data=kas2;
var diff:;
run;

Re: Macro for Wilcoxon Signed Rank Test

Denali — Mon, 02 Oct 2017 22:49:23 GMT

Thank you so much for the array programs. Then how do I export all the signed rank p-values (PROBS) to one file?

Re: Macro for Wilcoxon Signed Rank Test

Reeza — Mon, 02 Oct 2017 22:51:01 GMT

@Denali wrote:

Thank you so much for the array programs. Then how do I export all the signed rank p-values (PROBS) to one file?

How were you doing it for one file? I'd suspect the same way works.

Re: Macro for Wilcoxon Signed Rank Test

Denali — Mon, 02 Oct 2017 23:00:40 GMT

I got error message but I don't know what the problem is. Below is the log and code:

log:

13186 data kas3;
13187 set kas;
13188 array pre(*) PRETC1 PRETC2 PRETC3 PRETC4 PRETC5 PRETC6 PRETC7 PREEBC1 PREEBC2 PREEBC3
13188! PREEBC4 PREEBC5 PREEBC6 PREEBC7 PREEBC8 PREEBC9 PREEBC10 PREEBC11 PRETOP1 PRETOP2 PRETOP3
13188! PRETOP4
13189 PRETOP5 PRETOP6 PRETOP7 PRETOP8 PRETOP9 PRETOP10 PRETOP11 PRETOP12 PRETOP13 PRETOP14
13189! PRETOP15 PRETOP16 PRETOP17 PRETOP18 PRETOP19 PRETOP20 PRETOP21 PREMENT PREMENTRANK1
13189! PREMENTRANK2
13190 PREMENTRANK3 PREMENTRANK4 PREMENTRANK5 PREMENTRANK6 PREMENTRANK7 PREMENTRANK8 PREMENTRANK9
13190! PREMENTRANK10 PREMENTRANK11 PREMENTRANK12 PREMENTRANK13 PREMENTRANK14 PREMENTRANK15
13191 PREMENTRANK16 PREMENTRANK17;
13192 array post(*) POSTTC1 POSTTC2 POSTTC3 POSTTC4 POSTTC5 POSTTC6 POSTTC7 POSTEBC1 POSTEBC2
13192! POSTEBC3 POSTEBC4 POSTEBC5 POSTEBC6 POSTEBC7 POSTEBC8 POSTEBC9 POSTEBC10 POSTEBC11 POSTTOP1
13193 OSTTOP2 POSTTOP3 POSTTOP4 POSTTOP5 POSTTOP6 POSTTOP7 POSTTOP8 POSTTOP9 POSTTOP10 POSTTOP11
13193! POSTTOP12 POSTTOP13 POSTTOP14 POSTTOP15 POSTTOP16 POSTTOP17 POSTTOP18 POSTTOP19 POSTTOP20
13194 POSTTOP21 POSTMENT POSTMENTRANK1 POSTMENTRANK2 POSTMENTRANK3 POSTMENTRANK4 POSTMENTRANK5
13194! POSTMENTRANK6 POSTMENTRANK7 POSTMENTRANK8 POSTMENTRANK9 POSTMENTRANK10 POSTMENTRANK11
13195 POSTMENTRANK12 POSTMENTRANK13 POSTMENTRANK14 POSTMENTRANK15 POSTMENTRANK16 POSTMENTRANK17;
13196 array diff(*) diff1-diff67;
13197 do i=1 to dim(diff);
13198 diff(i) = post(i)-pre(i);
13199 end;
13200 run;

ERROR: Array subscript out of range at line 13198 column 19.
ID=201703 DEM1=1 DEM1SPECIFY= DEM2=1,5 DEM3=5 DEM4=1 DEM4SPECIFY= DEM5=2 DEM6=1 DEM6SPECIFY=
PRETC1=4 PRETC2=4 PRETC3=3 PRETC4=3 PRETC5=3 PRETC6=3 PRETC7=3 PREEBC1=4 PREEBC2=4 PREEBC3=4
PREEBC4=4 PREEBC5=4 PREEBC6=4 PREEBC7=1 PREEBC8=4 PREEBC9=4 PREEBC10=4 PREEBC11=4 PRETOP1=5
PRETOP2=4 PRETOP3=4 PRETOP4=4 PRETOP5=4 PRETOP6=3 PRETOP7=4 PRETOP8=5 PRETOP9=4 PRETOP10=4
PRETOP11=3 PRETOP12=3 PRETOP13=3 PRETOP14=4 PRETOP15=3 PRETOP16=3 PRETOP17=3 PRETOP18=3 PRETOP19=3
PRETOP20=4 PRETOP21=4 PREMENT=1 PREMENTRANK1=0 PREMENTRANK2=0 PREMENTRANK3=0 PREMENTRANK4=0
PREMENTRANK5=2 PREMENTRANK6=0 PREMENTRANK7=0 PREMENTRANK8=0 PREMENTRANK9=0 PREMENTRANK10=0
PREMENTRANK11=0 PREMENTRANK12=3 PREMENTRANK13=1 PREMENTRANK14=0 PREMENTRANK15=0 PREMENTRANK16=0
PREMENTRANK17=0 PREMENTRANK18=0 PREMENTRANK18SPECIFY= PREMENTRANK19= POSTTC1=5 POSTTC2=5
POSTTC3=5 POSTTC4=5 POSTTC5=5 POSTTC6=5 POSTTC7=5 POSTEBC1=5 POSTEBC2=5 POSTEBC3=5 POSTEBC4=5
POSTEBC5=5 POSTEBC6=5 POSTEBC7=0 POSTEBC8=5 POSTEBC9=5 POSTEBC10=5 POSTEBC11=5 POSTTOP1=5
POSTTOP2=5 POSTTOP3=5 POSTTOP4=5 POSTTOP5=5 POSTTOP6=4 POSTTOP7=4 POSTTOP8=5 POSTTOP9=5
POSTTOP10=5 POSTTOP11=5 POSTTOP12=4 POSTTOP13=5 POSTTOP14=5 POSTTOP15=4 POSTTOP16=5 POSTTOP17=5
POSTTOP18=5 POSTTOP19=5 POSTTOP20=5 POSTTOP21=5 POSTMENT=1 POSTMENTRANK1=0 POSTMENTRANK2=0
POSTMENTRANK3=0 POSTMENTRANK4=0 POSTMENTRANK5=1 POSTMENTRANK6=0 POSTMENTRANK7=0 POSTMENTRANK8=0
POSTMENTRANK9=0 POSTMENTRANK10=0 POSTMENTRANK11=0 POSTMENTRANK12=3 POSTMENTRANK13=2
POSTMENTRANK14=0 POSTMENTRANK15=0 POSTMENTRANK16=0 POSTMENTRANK17=0 POSTMENTRANK18=0
POSTMENTRANK18SPECIFY= POSTMENTRANK19= OSTTOP2=. diff1=1 diff2=1 diff3=2 diff4=2 diff5=2 diff6=2
diff7=2 diff8=1 diff9=1 diff10=1 diff11=1 diff12=1 diff13=1 diff14=-1 diff15=1 diff16=1 diff17=1
diff18=1 diff19=0 diff20=. diff21=1 diff22=1 diff23=1 diff24=1 diff25=0 diff26=0 diff27=1 diff28=1
diff29=2 diff30=1 diff31=2 diff32=1 diff33=1 diff34=2 diff35=2 diff36=2 diff37=2 diff38=1 diff39=1
diff40=0 diff41=0 diff42=0 diff43=0 diff44=0 diff45=-1 diff46=0 diff47=0 diff48=0 diff49=0
diff50=0 diff51=0 diff52=0 diff53=1 diff54=0 diff55=0 diff56=0 diff57=0 diff58=. diff59=. diff60=.
diff61=. diff62=. diff63=. diff64=. diff65=. diff66=. diff67=. i=58 _ERROR_=1 _N_=1
NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line):(Column).
1 at 13198:18
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 1 observations read from the data set WORK.KAS.
WARNING: The data set WORK.KAS3 may be incomplete. When this step was stopped there were 0
observations and 199 variables.
WARNING: Data set WORK.KAS3 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds

Code:

data kas3;
set kas;
array pre(*) PRETC1 PRETC2 PRETC3 PRETC4 PRETC5 PRETC6 PRETC7 PREEBC1 PREEBC2 PREEBC3 PREEBC4 PREEBC5 PREEBC6 PREEBC7 PREEBC8 PREEBC9 PREEBC10 PREEBC11 PRETOP1 PRETOP2 PRETOP3 PRETOP4
PRETOP5 PRETOP6 PRETOP7 PRETOP8 PRETOP9 PRETOP10 PRETOP11 PRETOP12 PRETOP13 PRETOP14 PRETOP15 PRETOP16 PRETOP17 PRETOP18 PRETOP19 PRETOP20 PRETOP21 PREMENT PREMENTRANK1 PREMENTRANK2
PREMENTRANK3 PREMENTRANK4 PREMENTRANK5 PREMENTRANK6 PREMENTRANK7 PREMENTRANK8 PREMENTRANK9 PREMENTRANK10 PREMENTRANK11 PREMENTRANK12 PREMENTRANK13 PREMENTRANK14 PREMENTRANK15
PREMENTRANK16 PREMENTRANK17;
array post(*) POSTTC1 POSTTC2 POSTTC3 POSTTC4 POSTTC5 POSTTC6 POSTTC7 POSTEBC1 POSTEBC2 POSTEBC3 POSTEBC4 POSTEBC5 POSTEBC6 POSTEBC7 POSTEBC8 POSTEBC9 POSTEBC10 POSTEBC11 POSTTOP1
OSTTOP2 POSTTOP3 POSTTOP4 POSTTOP5 POSTTOP6 POSTTOP7 POSTTOP8 POSTTOP9 POSTTOP10 POSTTOP11 POSTTOP12 POSTTOP13 POSTTOP14 POSTTOP15 POSTTOP16 POSTTOP17 POSTTOP18 POSTTOP19 POSTTOP20
POSTTOP21 POSTMENT POSTMENTRANK1 POSTMENTRANK2 POSTMENTRANK3 POSTMENTRANK4 POSTMENTRANK5 POSTMENTRANK6 POSTMENTRANK7 POSTMENTRANK8 POSTMENTRANK9 POSTMENTRANK10 POSTMENTRANK11
POSTMENTRANK12 POSTMENTRANK13 POSTMENTRANK14 POSTMENTRANK15 POSTMENTRANK16 POSTMENTRANK17;
array diff(*) diff1-diff67;
do i=1 to dim(diff);
diff(i) = post(i)-pre(i);
end;
run;

Re: Macro for Wilcoxon Signed Rank Test

Reeza — Mon, 02 Oct 2017 23:03:24 GMT

ERROR: Array subscript out of range at line 13198 column 19.

Make sure all your arrays have the same number of variables.

Re: Macro for Wilcoxon Signed Rank Test

Denali — Mon, 02 Oct 2017 23:09:54 GMT

I changed it to 57 variables for both pre and post so it worked! Thanks so much!

What I meant about exporting the p-value is that I would like to extract the p-value and output all 57 of them in one file.

When I ran the below code, it only exported one p-value from the first variable" diff_TC1" instead of all 57 of them. How to extract all the 57 p-value? Thanks!

proc univariate data=kas2;
var diff_TC1--diff_MENTRANK17;
output out=signrank PROBS=Prob_signrank;
run;

Re: Macro for Wilcoxon Signed Rank Test

Reeza — Tue, 03 Oct 2017 20:03:27 GMT

ods table testsforLocation=PValueTable;

https://blogs.sas.com/content/iml/2017/01/09/ods-output-any-statistic.html

Re: Macro for Wilcoxon Signed Rank Test

Denali — Tue, 03 Oct 2017 20:27:16 GMT

Hi @Reeza, I got below table after running your code. Is there a way that I can keep Signed Rank test p value only? I do not need the p-value from Student's t test. Thank you!

diff_TC1	Student's t	t	6.190189	Pr > \|t\|	<.0001
diff_TC1	Sign	M	7.5	Pr >= \|M\|	<.0001
diff_TC1	Signed Rank	S	60	Pr >= \|S\|	<.0001
diff_TC2	Student's t	t	7.093964	Pr > \|t\|	<.0001
diff_TC2	Sign	M	9.5	Pr >= \|M\|	<.0001
diff_TC2	Signed Rank	S	95	Pr >= \|S\|	<.0001
diff_TC3	Student's t	t	3.83532	Pr > \|t\|	0.0011
diff_TC3	Sign	M	8.5	Pr >= \|M\|	<.0001
diff_TC3	Signed Rank	S	76	Pr >= \|S\|	0.0008
diff_TC4	Student's t	t	6.474259	Pr > \|t\|	<.0001
diff_TC4	Sign	M	8.5	Pr >= \|M\|	<.0001
diff_TC4	Signed Rank	S	90.5	Pr >= \|S\|	<.0001
diff_TC5	Student's t	t	11.61035	Pr > \|t\|	<.0001
diff_TC5	Sign	M	10	Pr >= \|M\|	<.0001

Re: Macro for Wilcoxon Signed Rank Test

Reeza — Tue, 03 Oct 2017 21:30:40 GMT

Filter it afterwards using a WHERE statement.

If you really want you can likely use it as a data set option.