/*-------------------------------------------------------------------* * Name: DemographTable.sas * * Title: Create demographic table by a binary variable. * *-------------------------------------------------------------------* * Author: Yu Wang * * Created: 2/20/2019 * * Revised: * * Version: 1 * * - Changes are listed here followed by date (m/d/yyyy) * *-------------------------------------------------------------------*/ /*------------------------------------------------------------------* | DEMOGRAPHTABLE macro - create a demographic table by a binary | | variable. Includes both nominal and numerical | | variables simultaneously. | | Test: Allows you to set the method used for comparing | | numeric variables. Ttest option uses independent T-test, | | Wilcoxon option uses the Wilcoxon rank-sum test. | *------------------------------------------------------------------*/ %macro DEMOGRAPHTABLE( TrtVar, /*The treatment variable name or class variable name,which should be binary*/ value1, /*One value of the treatment variable*/ value2, /*The other value of the treatment variable*/ catlist, /*The list of the nominal variables' names*/ numlist, /*The list of the numerical variables' names*/ Test, /*The statistical test used: Ttest, Wilcoxon*/ dataname /*The dataset name*/ ); ods output CrossTabFreqs=crosstable PearsonChiSq =Exactpchisq; proc freq data=&dataname order=data; where &TrtVar in ("&value1" "&value2"); tables (&catlist)*&TrtVar/ nopercent; exact pchi; run; ods output close; data crosstableoutput; length varname $32 fvalue $60 ; set crosstable; varname=scan(table,2); fvalue=vvaluex(varname); keep varname fvalue &TrtVar frequency ColPercent ; run; data crosstab1 (keep = varname fvalue &TrtVar Frequency_1 Frequency_2 Colpercent_1 Colpercent_2); set crosstableoutput; if &TrtVar= "&value1" then do; Frequency_1=Frequency; Colpercent_1=ColPercent; end; if &TrtVar= "&value2" then do; Frequency_2=Frequency; Colpercent_2=ColPercent; end; run; data crosstabclass1(drop = &TrtVar Frequency_2 Colpercent_2 ) crosstabclass2(drop = &TrtVar Frequency_1 Colpercent_1 ); set crosstab1; if &TrtVar = "&value1" then output crosstabclass1; if &TrtVar = "&value2" then output crosstabclass2; run; ods output onewayfreqs=freqout; proc freq data=&dataname order=data; tables &catlist &TrtVar; run; ods output close; proc sql; select max(CumFrequency) into :N from freqout where CumPercent=100; select Frequency into: N1 from freqout where &TrtVar="&value1"; select Frequency into: N2 from freqout where &TrtVar="&value2"; quit; %let N=%CMPRES(&N); %let N1= %CMPRES(&N1); %let N2= %CMPRES(&N2); data freqout; length varname $32 fvalue $60 ; set freqout; if scan(table,-1)="&TrtVar" then delete; varname=scan(table,-1); fvalue=strip(vvaluex(varname)); frequency=round(frequency,0.1); percent=round(percent,0.1); keep varname fvalue frequency percent; run; proc sort data = crosstabclass1; by varname; run; proc sort data = crosstabclass2; by varname; run; proc sort data = freqout; by varname; run; data crosstabfinal; merge crosstabclass1 crosstabclass2 freqout; by varname; fvalue=STRIP(fvalue); if fvalue in ('.' '') then delete; run; data Pearsonpvalue ; length varname $32; set Exactpchisq; varname=scan(table,2); keep varname Name1 Label1 nValue1; run; data Pearsonpvalue2; set Pearsonpvalue; if Name1 = 'XP_PCHI' then output Pearsonpvalue2; run; data Pearsonpvalue2(drop = Name1 Label1); set Pearsonpvalue2(rename=(nValue1=P_value)); run; proc sort data = Pearsonpvalue2; by varname; run; data Pearsonpvalue2(keep= varname P_value); retain varname P_value; set Pearsonpvalue2; run; data Exactpearsonfinal; length Total statR1 statR2 $12; merge crosstabfinal Pearsonpvalue2; by varname; Colpercent_1=round(Colpercent_1,0.1); Colpercent_2=round(Colpercent_2,0.1); Total=strip(frequency)||' '||'('||strip(percent)||'%'||')'; statR1=STRIP(Frequency_1)||' '||'('||STRIP(Colpercent_1)||'%'||')'; statR2=STRIP(Frequency_2)||' '||'('||STRIP(Colpercent_2)||'%'||')'; P_value=round(P_value,0.001); keep varname fvalue Total statR1 statR2 P_value; run; /*-------------------------------------* | Numerical variable analysis section | *--------------------------------------*/ %if &Test=Ttest %then %do; %let Test= Independent T-Test; %put Numeric test was done by &Test; ods output Statistics= T_stats TTests=T_tests Equality= T_vars; proc ttest data=&dataname plots=none; where &TrtVar in ("&value1" "&value2"); class &TrtVar; var &numlist; run; ods output close; data T_stats1 (keep = variable class Mean_1 SD_1 Mean_2 SD_2 ); set T_stats; if class = "&value1" then do; Mean_1 =Mean ; SD_1=StdDev; end; if class = "&value2" then do; Mean_2 =Mean ; SD_2=StdDev; end; run; data T_class1(drop = class SD_2 Mean_2) T_class2(drop = class SD_1 Mean_1); set T_stats1; if class = "&value1" then output T_class1; if class = "&value2" then output T_class2; run; proc univariate data=&dataname outtable=T_all(keep=_VAR_ _MEAN_ _STD_ rename=(_VAR_=variable)) noprint; var &numlist; run; data T_all; set T_all; format _MEAN_ _STD_ 12.1; run; proc sort data = T_class1; by variable; run; proc sort data = T_class2; by variable; run; proc sort data = T_all; by variable; run; data T_statsfinal; merge T_class1 T_class2 T_all; by variable; run; data T_vars; set T_vars (rename=(probf=probvar)); run; proc sort data = T_vars; by variable; run; proc sort data = T_tests;by variable; run; data T_merged; merge T_tests (in=a) T_vars (in=b keep = variable probvar); by variable; if a; run; data T_diffsame; set T_merged; if probvar le .050 and variances = 'Unequal' then output; if probvar gt .050 and variances = 'Equal' then output; run; proc sort data = T_statsfinal; by variable; run; proc sort data = T_diffsame; by variable; run; data T_ttest; merge T_statsfinal T_diffsame; by variable; run; data Table_ttest (keep =varname fvalue Total statR1 statR2 P_value); length Total statR1 statR2 $12; retain variable N_1 Mean_1 SD_1 N_2 Mean_2 SD_2 tValue DF Probt; set T_ttest; Mean_1=round(Mean_1,0.1); SD_1 =round(SD_1,0.1); Mean_2=round(Mean_2,0.1); SD_2 =round(SD_2,0.1); fvalue='Mean (SD)'; statR1=STRIP(Mean_1)||' '||'('||STRIP(SD_1)||')'; statR2=STRIP(Mean_2)||' '||'('||STRIP(SD_2)||')'; Total=strip(_MEAN_)||' '||'('||strip(_STD_)||')'; Probt=round(Probt,0.001); rename variable=varname Probt=P_value; run; data DemtableFinal; set Exactpearsonfinal Table_ttest; run; %end; %else %do; %let Test= Wilcoxon Rank-Sum test; %put Numeric test was done by &Test; PROC NPAR1WAY data=&dataname noprint wilcoxon; where &TrtVar in ("&value1","&value2"); Class &TrtVar; Var &numlist; output out=ranksum1(rename=(_VAR_=varname)); Run; /* This section prepare the Median min and max data set for each variable in varlist*/ /*----------------------------------------------------------------------------------*/ proc univariate data=&dataname outtable=M_stats(keep=_VAR_ &TrtVar _MEDIAN_ _MIN_ _MAX_) noprint; class &TrtVar; var &numlist; run; data M_sets(keep= varname &TrtVar Median1 Min1 Max1 Median2 Min2 Max2); set M_stats; if &TrtVar="&value1" then do; Median1=round(_MEDIAN_,0.1); Min1=round(_MIN_,0.1); Max1=round(_MAX_,0.1); end; else if &TrtVar="&value2" then do; Median2=round(_MEDIAN_,0.1); Min2=round(_MIN_,0.1); Max2=round(_MAX_,0.1); end; rename _VAR_=varname; run; data M_1(drop = &TrtVar Median2 Min2 Max2) M_2(drop = &TrtVar Median1 Min1 Max1); set M_sets; if &TrtVar = "&value1" then output M_1; if &TrtVar = "&value2" then output M_2; run; proc univariate data=&dataname outtable=M_3(keep=_VAR_ _MEDIAN_ _MIN_ _MAX_ rename=(_VAR_=varname)) noprint; var &numlist; run; data M_3; set M_3; format _MEDIAN_ _MIN_ _MAX_ 12.1; run; /******************************************************************************************/ /* Merge the median data and wilcoxon rank_sum test day set */ /******************************************************************************************/ proc sort data=M_1; by varname; run; proc sort data=M_2; by varname; run; proc sort data=M_3; by varname; run; proc sort data=ranksum1; by varname; run; data ranksum_final(keep=varname fvalue Total statR1 statR2 P_value); length Total statR1 statR2 $12; merge M_1 M_2 M_3 ranksum1; by varname; fvalue="Median(range)"; statR1=STRIP(Median1)||' '||'('||STRIP(Min1)||'-'||STRIP(Max1)||')'; statR2=STRIP(Median2)||' '||'('||STRIP(Min2)||'-'||STRIP(Max2)||')'; Total=strip(_MEDIAN_)||' '||'('||strip(_MIN_)||'-'||strip(_MAX_)||')'; P_value=round(P2_WIL,0.001); run; data DemtableFinal; set Exactpearsonfinal ranksum_final; run; %end; proc datasets nolist; delete crosstable Exactpchisq crosstableoutput crosstab1 crosstabclass1 crosstabclass2 freqout crosstabfinal Pearsonpvalue Pearsonpvalue2 Exactpearsonfinal Table_ttest T_stats1 T_ttest T_class1 T_class2 T_all T_vars T_tests T_stats T_merged T_diffsame T_statsfinal M_1 M_2 M_3 ranksum1 M_sets M_stats ranksum_final; run; proc format; value p_sig 0-0.051=red; value pform 0-<0.001='<0.001'; run; ods excel file="&home\Report\Demgraphical Table by &TrtVar (&value1 vs &value2) from &dataname on &SYSDATE..xlsx" options(sheet_label="Demgraphic Table" embedded_titles="no"); proc report data=DemtableFinal headline split='/'; column varname fvalue Total ("&TrtVar" statR1 statR2) P_value; define varname/order order=data noprint; define fvalue/ order=data style={cellwidth=36% just=c }; define Total/ style={cellwidth=18% just=c}; define statR1/ style={cellwidth=18% just=c }; define statR2/ style={cellwidth=18% just=c }; define P_value/order missing format=pform. style={background=p_sig. just=c}; label statR1="&value1/(N=&N1)" fvalue="Variables" Total="Total/(N=&N)" statR2="&value2/(N=&N2)" P_value='p-value*'; compute before varname/ style=[font_weight=bold just=l]; line varname $100.; endcomp; compute after/style=[just=l]; line "*Note: For two group comparisions, P-value for discrete variables,using Chi-Square Exact Test;"; line "P-value of numeric variables, using &Test"; line ''; endcomp; Title "Demgraphical Table by &TrtVar"; run; Title; ods excel close; %Mend DemographTable;