Quartz | Level 8

## Creating Macro for Value

HI Team,

I have a requirement to create Pvalue based on distribution of data , below is the requirement.

1 If data follows normal distribution, P-values were derived using paired sample t-test else by Wilcoxon test for baseline vs all the timepoints.

The below codes i have written , but the codes are too lengthy. Could you please help to make it in a macro

```/*Method 1 - Paired T test*/

%macro pir(dat1=,visn=,parn=,out=,rm=);

data &dat1;
set input;
where parcat1n=&parn. and  avisn=&visn.;
run;

ods trace on;
ods output TTests=&out. ;
proc ttest data=&dat1. alpha=0.05;
paired base*aval;
run;
ods trace off;

data &out._1(rename=(probc=&rm.));
set &out.;
probc=put(Probt,pvalue6.4)||"^{Super 1}";
label=" P-value";
ord=8.1;
keep label ord Variable1 Variable2 Difference probc;
run;

%mend;
%pir(dat1=input1,visn=1,parn=1,out=l_wk1,rm=_Lesional_);
%pir(dat1=input1,visn=1,parn=2,out=nl_wk1,rm=_Non_Lesional_);
%pir(dat1=input1,visn=1,parn=3,out=h_wk1,rm=_Healthy_);

%pir(dat1=input1,visn=2,parn=1,out=l_wk2,rm=_Lesional_);
%pir(dat1=input1,visn=2,parn=2,out=nl_wk2,rm=_Non_Lesional_);
%pir(dat1=input1,visn=2,parn=3,out=h_wk2,rm=_Healthy_);

%pir(dat1=input1,visn=3,parn=1,out=l_wk3,rm=_Lesional_);
%pir(dat1=input1,visn=3,parn=2,out=nl_wk3,rm=_Non_Lesional_);
%pir(dat1=input1,visn=3,parn=3,out=h_wk3,rm=_Healthy_);

%pir(dat1=input1,visn=4,parn=1,out=l_wk4,rm=_Lesional_);
%pir(dat1=input1,visn=4,parn=2,out=nl_wk4,rm=_Non_Lesional_);
%pir(dat1=input1,visn=4,parn=3,out=h_wk4,rm=_Healthy_);

%pir(dat1=input1,visn=5,parn=1,out=l_wk6,rm=_Lesional_);
%pir(dat1=input1,visn=5,parn=2,out=nl_wk6,rm=_Non_Lesional_);
%pir(dat1=input1,visn=5,parn=3,out=h_wk6,rm=_Healthy_);

%pir(dat1=input1,visn=6,parn=1,out=l_wk8,rm=_Lesional_);
%pir(dat1=input1,visn=6,parn=2,out=nl_wk8,rm=_Non_Lesional_);
%pir(dat1=input1,visn=6,parn=3,out=h_wk8,rm=_Healthy_);

%pir(dat1=input1,visn=7,parn=1,out=l_wk12,rm=_Lesional_);
%pir(dat1=input1,visn=7,parn=2,out=nl_wk12,rm=_Non_Lesional_);
%pir(dat1=input1,visn=7,parn=3,out=h_wk12,rm=_Healthy_);

%pir(dat1=input1,visn=8,parn=1,out=l_wk16,rm=_Lesional_);
%pir(dat1=input1,visn=8,parn=2,out=nl_wk16,rm=_Non_Lesional_);
%pir(dat1=input1,visn=8,parn=3,out=h_wk16,rm=_Healthy_);

%pir(dat1=input1,visn=9,parn=1,out=l_wk28,rm=_Lesional_);
%pir(dat1=input1,visn=9,parn=2,out=nl_wk28,rm=_Non_Lesional_);
%pir(dat1=input1,visn=9,parn=3,out=h_wk28,rm=_Healthy_);

%macro mrg(in_1=,in_2=,in_3=,vsn=,out=);
data &out.;
merge &in_1. &in_2. &in_3.;
by Variable1 Variable2;
avisn=&vsn.;
run;
%mend;

%mrg(in_1=l_wk1_1,in_2=nl_wk1_1,in_3=h_wk1_1,vsn=1,out=p_wk1);
%mrg(in_1=l_wk2_1,in_2=nl_wk2_1,in_3=h_wk2_1,vsn=2,out=p_wk2);
%mrg(in_1=l_wk3_1,in_2=nl_wk3_1,in_3=h_wk3_1,vsn=3,out=p_wk3);
%mrg(in_1=l_wk4_1,in_2=nl_wk4_1,in_3=h_wk4_1,vsn=4,out=p_wk4);
%mrg(in_1=l_wk6_1,in_2=nl_wk6_1,in_3=h_wk6_1,vsn=5,out=p_wk6);
%mrg(in_1=l_wk8_1,in_2=nl_wk8_1,in_3=h_wk8_1,vsn=6,out=p_wk8);
%mrg(in_1=l_wk12_1,in_2=nl_wk12_1,in_3=h_wk12_1,vsn=7,out=p_wk12);
%mrg(in_1=l_wk16_1,in_2=nl_wk16_1,in_3=h_wk16_1,vsn=8,out=p_wk16);
%mrg(in_1=l_wk28_1,in_2=nl_wk28_1,in_3=h_wk28_1,vsn=9,out=p_wk28);

/*Paired Ttest values*/

data PT_pval(rename=(variable2=varname));
set p_wk1 p_wk2 p_wk3 p_wk4 p_wk6 p_wk8 p_wk12 p_wk16 p_wk28;
run;

/*Checking normality of the dristribution to understand which test needs to perform*/

%macro uni(var=,a=,b=,d=,rnm=);

data input_1;
set input;
where  parcat1n=&b. ;
run;

proc sort;by avisn;run;

ods trace on;
ods output TestsForNormality=&d.;
Proc univariate data=input_1 normal;
var &var.;
by avisn;
RUN;
ods trace off;

data &d.(rename=(pValue=&rnm.));
set &d.;
where TEST="Shapiro-Wilk";
run;

%mend;

%uni(var=chg,b=1,d=pv_l_0s,rnm=ls);

%uni(var=chg,b=2,d=pv_nl_0s,rnm=nls);

%uni(var=chg,b=3,d=pv_h_0s,rnm=hs);

%macro mr(a=,in=,ord=);
data &a.;
merge &in.;
by avisn;
sord=&ord.;
keep avisn  VarName Test ls nls hs sord;
run;
%mend;
%mr(a=pval_0s,in=pv_l_0s pv_nl_0s pv_h_0s,ord=0);

data pvalue_T415;
Length nvar \$9.;
set pval_0s;
if ls<0.05 then nvar="ls";
if nls<0.05 then nvar="nls";
if hs<0.05 then nvar="hs";
if ls<0.05 and hs<0.05 then nvar="lshs";
if ls<0.05 and nls<0.05 then nvar="lsnls";
if nls<0.05 and hs<0.05 then nvar="nlshs";
if ls<0.05 and nls<0.05 and hs<0.05 then nvar="lsnlshs";
if ls ne . and nls ne . and hs ne .;
run;

/*Method 2 Non parametric method*/

%macro par(a=,b=,c=,rnm=);
data input1;
set input;
where parcat1n=&a.  and avisn in &b.;
run;;

proc sort data=input1;
by parcat1;
run;

ods trace on;
ods output TestsForLocation=&c.;
Proc univariate data=input1;
Var chg;
Run;
ods trace off;

data &c.(rename=(pValue=&rnm.));
Length varname \$4.;
set &c.;
where Test="Signed Rank";
varname="AVAL";
run;

%mend;
%par(a=1,b=(0,1),c=ls_1,rnm=npls);
%par(a=2,b=(0,1),c=nls_1,rnm=npnls);
%par(a=3,b=(0,1),c=hs_1,rnm=nphs);

%par(a=1,b=(0,2),c=ls_2,rnm=npls);
%par(a=2,b=(0,2),c=nls_2,rnm=npnls);
%par(a=3,b=(0,2),c=hs_2,rnm=nphs);

%par(a=1,b=(0,3),c=ls_3,rnm=npls);
%par(a=2,b=(0,3),c=nls_3,rnm=npnls);
%par(a=3,b=(0,3),c=hs_3,rnm=nphs);

%par(a=1,b=(0,4),c=ls_4,rnm=npls);
%par(a=2,b=(0,4),c=nls_4,rnm=npnls);
%par(a=3,b=(0,4),c=hs_4,rnm=nphs);

%par(a=1,b=(0,5),c=ls_5,rnm=npls);
%par(a=2,b=(0,5),c=nls_5,rnm=npnls);
%par(a=3,b=(0,5),c=hs_5,rnm=nphs);

%par(a=1,b=(0,6),c=ls_6,rnm=npls);
%par(a=2,b=(0,6),c=nls_6,rnm=npnls);
%par(a=3,b=(0,6),c=hs_6,rnm=nphs);

%par(a=1,b=(0,7),c=ls_7,rnm=npls);
%par(a=2,b=(0,7),c=nls_7,rnm=npnls);
%par(a=3,b=(0,7),c=hs_7,rnm=nphs);

%par(a=1,b=(0,8),c=ls_8,rnm=npls);
%par(a=2,b=(0,8),c=nls_8,rnm=npnls);
%par(a=3,b=(0,8),c=hs_8,rnm=nphs);

%par(a=1,b=(0,9),c=ls_9,rnm=npls);
%par(a=2,b=(0,9),c=nls_9,rnm=npnls);
%par(a=3,b=(0,9),c=hs_9,rnm=nphs);

%macro mr(a=,in=,ord=);
data &a.;
merge &in.;
by varname;
sord=&ord.;
keep varname test npls npnls nphs sord;
run;
%mend;

%mr(a=np_1,in=ls_1 nls_1 hs_1,ord=1);

%mr(a=np_2,in=ls_2 nls_2 hs_2,ord=2);

%mr(a=np_3,in=ls_3 nls_3 hs_3,ord=3);

%mr(a=np_4,in=ls_4 nls_4 hs_4,ord=4);

%mr(a=np_5,in=ls_5 nls_5 hs_5,ord=5);

%mr(a=np_6,in=ls_6 nls_6 hs_6,ord=6);

%mr(a=np_7,in=ls_7 nls_7 hs_7,ord=7);

%mr(a=np_8,in=ls_8 nls_8 hs_8,ord=8);

%mr(a=np_9,in=ls_9 nls_9 hs_9,ord=9);

data np_T415(rename=(ls=npls nls=npnls hs=nphs sord=avisn));
Length varname \$4.;
set np_1-np_9;
ls=put(npls,pvalue6.4)||"^{Super 2}";
nls=put(npnls,pvalue6.4)||"^{Super 2}";
hs=put(nphs,pvalue6.4)||"^{Super 2}";
drop npls npnls nphs;
run;

proc sort data=np_T415;by avisn;run;

proc sort data=pvalue_T415;by avisn;run;

data final_T415;
Length varname \$4.;
merge pvalue_T415(in=a) np_T415(in=b);
by avisn;
if a;
run;

/*Merging both Paired T test vlaue and non parameter value*/

data fi_pval;
merge final_T415(in=a) PT_pval(in=b);
by avisn;
if a;
run;

/*Assinging Pavlues based on the requiremnts- Less than 0.05 for Wilcoxon and rest are assigned with T test*/

data final_pval;
set fi_pval;
if nvar="ls" then _Lesional_=npls;
if nvar="nls" then _Non_Lesional_=npnls;
if nvar="hs" then _Healthy_=nphs;
if nvar="lshs" then _Healthy_=nphs;
if nvar="lshs" then _Lesional_=npls;
if nvar="nlshs" then _Healthy_Skin_Area=nphs;
if nvar="nlshs" then _Non_Lesional_=npnls;
if nvar="lsnls" then _Non_Lesional_=npnls;
if nvar="lsnls" then _Lesional_=npls;
if nvar="lsnlshs" then  _Lesional_=npls;
if nvar="lsnlshs" then _Non_Lesional_=npnls;
if nvar="lsnlshs" then _Healthy_=nphs;
;
run;```

, Is there any way to make it small and create a macro for this

5 REPLIES 5
Diamond | Level 26

## Re: Creating Macro for Value

I have a requirement to create Pvalue based on distribution of data , below is the requirement. 1 If data follows normal distribution, P-values were derived using paired sample t-test else by Wilcoxon test for baseline vs all the timepoints. The below codes i have written , but the codes are too lengthy. Could you please help to make it in a macro

Not able to test this, as I don't have your data. But the general idea is to sort and then use a BY statement.

``````proc sort data=input;
by parcat1n avisn;
run;

proc ttest data=input alpha=0.05;
ods output ttests=out;
by parcat1n avisn;
paired base*aval;
run;``````

The general idea here (and hopefully for the rest of your SAS life) is to avoid using macros when a BY statement(s) can do the work.

Also, the same idea applies to using testing the distribution and for non-parametric tests, no need for a macro there either, so I did not write code for that, see if you can follow what I did to get distribution tests and non-parametric tests working without a macro.

--
Paige Miller
Quartz | Level 8

## Re: Creating Macro for Value

As you suggested the first method I have changed and it works...but in second method i need to show baseline VS all other timepoints (i.e visits)
Diamond | Level 26

## Re: Creating Macro for Value

...but in second method i need to show baseline VS all other timepoints (i.e visits)

I'm sorry but I don't know what this brief comment means, I don't know what doesn't work, I don't even know what baseline you are referring to. Please explain in detail.

--
Paige Miller
Quartz | Level 8

## Re: Creating Macro for Value

need to check the Pvalue for first visit(Baseline) and all other visits
Diamond | Level 26