I have a macro that is used to create tables in microsoft word. Overall the macro is working well, however, I am getting some truncation of my output and I can't figure out why. Basically, for the percentages in two columns they are getting cut off at the decimal point and it is not printing the % sign. For continuous variables the variable label or p-value is also not printing out. I have tried several different formats and work around but nothing seems to change. It will not let me attach the sas file so I am pasting the macro code below. I am posting the code to invoke the macro below as well as what I am getting when I run the code. As you can see the totals column runs well, but in the yes and no columns the percents are getting truncated. I am not getting the Age label or p-value for the continuous variable either. I know this is somewhat more complicated but I would apprecaite any advice, I've been trying to fix for a while and have had no luck.
data alldata; set have; idnum=studyid; **insert study id from dataset; if outcomea=1 then site=1; else if outcomea=0 then site=2; ***Outcome/stratification variable; proc sort data=alldata; by idnum; run;
data bydata; set alldata; keep site idnum ; run;
%let stratvar=site; data last; totals=" "; run;
****continuous variable; %conti(site=-1,alldata=alldata, bydata=bydata,row=age, rlabel="Age ", col=site, source3="All data", numfmt=7.2);
*categorical variable;
%table2(site=-1,alldata=alldata, bydata=bydata, row=Sex, rlabel="Sex ", rowf= sex.,col=site, missnum=9999, pname=p_pchi, source3="All data");
data last1; set last; run;
***CODE TO OUTPUT TO WORD TABLE***; ods rtf file="table.doc" startpage=no bodytitle; proc print data=last1 label noobs; Title1 'Table 1'; Title2 ''; var varname var1 totals arma armb p; label varname="Variable" var1="Response" arma="Yes" armb="No" totals="Total"; run;
ods rtf close;
Variable
Response
Total
Yes
No
p
n
34495.0
10473.0
24022.0
mean
34.02
31.82
34.97
std
9.60
9.17
9.63
max
66.00
61.00
66.00
q3
40.00
38.00
41.00
median
34.00
31.00
35.00
q1
26.00
24.00
27.00
min
17.00
17.00
17.00
Sex
(n=34495)
(n=10473)
(n=24022)
<.001
Male
22045(63.9%)
7140(68.
14905(62.
Female
12450(36.1%)
3333(31.
9117(38.
*****MACRO CODE IS EVERYTHING BELOW HERE**************;
%macro conti4mediqr(site=,alldata=, bydata=, row=, rlabel=, col=, numfmt=12.0, source1=" ", source2=" ", source3=" ");
data temp; merge &alldata &bydata(in=idu1); by idnum; if idu1; if &col>. ; keep &row &col &stratvar; %if &site>-1 %then %do; data one; set temp; if &stratvar=&site; %end; %if &site=-1 %then %do; data one; set temp; %end;
proc sort data=one; by &col; proc univariate data= one noprint; var &row; by &col; output out=out2 median=median min=min max=max q1=q1 q3=q3 mean=mean std=std n=n;
data out2; set out2; MedianIQR=put(median, 4.1)||'('||put(q1, 4.1)||',' || put(q3, 4.1)||')'; MeanSTD=put(mean, 6.1)||'('||put(std, 6.1)||')';
proc univariate data= one noprint; var &row; output out=out5 median=median min=min max=max q1=q1 q3=q3 mean=mean std=std n=n;
data out5; set out5; MedianIQR=put(median, 4.1)||'('||put(q1, 4.1)||',' || put(q3, 4.1)||')'; MeanSTD=put(mean, 6.1)||'('||put(std, 6.1)||')'; &col=100;
data out2; set out2 out5; proc print data=out5;
proc transpose data=out2 out=out3 ( drop=_label_); id &col; proc print data=out3; data out3; set out3; arma=put(_1, &numfmt); armb=put(_2, &numfmt); armc=put(_3, &numfmt); armd=put(_4, &numfmt); totals=put(_100, &numfmt);
data miqr; set out2; keep MedianIQR;
proc transpose data=miqr out=test1; var MedianIQR;
data newtest1; set test1; arma=col1; armb=col2; armc=col3; armd=col4; totals=col5; drop col1 col2 col3 col4 col5;
data mstd; set out2; keep MeanSTD;
proc transpose data=mstd out=test2; var MeanSTD;
data newtest2; set test2; arma=col1; armb=col2; armc=col3; armd=col4; totals=col5; drop col1 col2 col3 col4 col5;
data out3; set newtest1 newtest2 out3;
proc npar1way data=one wilcoxon; class &col; var &row; output out=pvals wilcoxon; data pvals(keep=variable pvalue); set pvals; rename _var_= variable; if p2_wil^=. then pvalue=p2_wil; if p_kw^=. then pvalue=p_kw; else pvalue=.; run;
data ntotal; set pvals; arma=" "; armb=" "; if pvalue<=0.0001 then p="<0.0001"; else p=put(pvalue, 6.4); varname=&rlabel; if varname="" then varname=variable;
if pvalue<0.001 then p='<.001'; else if pvalue>=0.050001 then p= put(pvalue, 4.3); else if pvalue>=0.001 then p= put(pvalue, 4.3); varname=&rlabel;
if &site=1 then datasource= &source1; if &site=2 then datasource= &source2; if &site=3 then datasource= &source3;
if &site=-1 then datasource="All Data"; data four; set ntotal out3; data last(keep=varname datasource var1 arma armb armc armd totals p); set last four(rename=(_name_=var1)); proc print data=last noobs label; var varname datasource var1 arma armb armc armd totals p; ;
%mend;
%macro table2(site=,alldata=, bydata=, row=, rlabel=, rowf=, col=, pname=,missnum=, source1=" ", source2=" ", source3=" ", pctfmt=4.3); data temp; merge &alldata &bydata; by idnum; col=&col; row=&row; if &col=0 then &col=2; *** change 0 to 2 as no****; if &col>. ; keep idnum &row &col &stratvar; run;
%if &site>-1 %then %do; data temp; set temp; if &stratvar=&site; %end; run;
%if &site=-1 %then %do; data temp; set temp; %end; run;
proc sort data=temp; by &col &row; run;
data temp; set temp; if &row>=&missnum then &row=.; run;
proc freq data= temp; by &col; tables &row/ out=counts noprint; run;
data col1; set counts; if &col=1; run;
data col2; set counts; if &col=2; run;
***** calculating combined rows******; proc freq data= temp; tables &row/ out=totals noprint; run;
data tot; set totals; totals=put(count, 5.)||'('||put(percent, &pctfmt)||'%)'; keep &row totals; if &row>.; run;
data both; merge col1(rename=(count=cola percent=pcta)) col2(rename=(count=colb percent=pctb)); by &row; if &row>. and cola=. then cola=0; if &row>. and colb=. then colb=0; if &row>. and pcta=. then pcta=0; if &row>. and pctb=. then pctb=0; if &row>.; keep &row cola colb pcta pctb arma armb; arma=put(cola, 5.)||'('||put(pcta, &pctfmt)||'%)'; armb=put(colb, 5.)||'('||put(pctb, &pctfmt)||'%)'; run; data both; merge both tot; by &row; run;
proc print; run; /* data temp; set temp; if &row>=&missnum then &row=.; */ data pvalue; &pname=99999; run;
proc freq data= temp; tables &row*&col/all trend; output out=pvalue pchi fisher trend cmh n nmiss; run;
data pvalue; set pvalue; if &pname>.; varname=" "; varname=&rlabel; if &site=1 then datasource= &source1; if &site=2 then datasource= &source2; if &site=3 then datasource= &source3; if &site=-1 then datasource="All Data"; run;
proc print; run;
proc means data=temp n; class &col; var &row; output out=ntotal n=n; run;
data na; set ntotal; if &col=1 and _type_=1; run;
data nb;set ntotal; if &col=2 and _type_=1; run;
data ntotal; merge na(rename=(n=na)) nb(rename=(n=nb)); run;
data ntotal; set ntotal; arma="(n="||put(na, 5.)||")"; armb="(n="||put(nb, 5.)||")"; *drop na nb; run;
proc print; run;
proc print data=pvalue; run; data total; merge ntotal pvalue; if &pname<0.001 then p='<.001'; else if &pname>=0.01 then p= put(&pname, 3.2); else if &pname>=0.001 then p= put(&pname, 4.3); /* if &pname<=0.01 and &pname>. then p="<0.01"; else p=put(&pname, 6.3); */ totals="(n="||put(na+nb, 5.)||")"; allnmiss=put(nmiss, 5.); run;
proc print data=total; run; data four; set total both; keep datasource varname var1 totals allnmiss arma armb totals p; if &row=. then var1=" "; else var1=put(&row, &rowf); run; proc print data=four; run;
data last(keep=varname datasource var1 arma armb totals p); set last four; run;
proc contents; run;
proc print data=last noobs label; var varname datasource var1 arma armb totals p; run;
%mend;
%macro table2(site=,alldata=, bydata=, row=, rlabel=, rowf=, col=, pname=,missnum=, source1=" ", source2=" ", source3=" ", pctfmt=4.3); data temp; merge &alldata &bydata; by idnum; col=&col; row=&row; if &col=0 then &col=2; *** change 0 to 2 as no****; if &col>. ; keep idnum &row &col &stratvar; run;
%if &site>-1 %then %do; data temp; set temp; if &stratvar=&site; %end; run;
%if &site=-1 %then %do; data temp; set temp; %end; run;
proc sort data=temp; by &col &row; run;
data temp; set temp; if &row>=&missnum then &row=.; run;
proc freq data= temp; by &col; tables &row/ out=counts noprint; run;
data col1; set counts; if &col=1; run;
data col2; set counts; if &col=2; run;
***** calculating combined rows******; proc freq data= temp; tables &row/ out=totals noprint; run;
data tot; set totals; totals=put(count, 5.)||'('||put(percent, &pctfmt)||'%)'; keep &row totals; if &row>.; run;
data both; merge col1(rename=(count=cola percent=pcta)) col2(rename=(count=colb percent=pctb)); by &row; if &row>. and cola=. then cola=0; if &row>. and colb=. then colb=0; if &row>. and pcta=. then pcta=0; if &row>. and pctb=. then pctb=0; if &row>.; keep &row cola colb pcta pctb arma armb; arma=put(cola, 5.)||'('||put(pcta, &pctfmt)||'%)'; armb=put(colb, 5.)||'('||put(pctb, &pctfmt)||'%)'; run; data both; merge both tot; by &row; run;
proc print; run; /* data temp; set temp; if &row>=&missnum then &row=.; */ data pvalue; &pname=99999; run;
proc freq data= temp; tables &row*&col/all trend; output out=pvalue pchi fisher trend cmh n nmiss; run;
data pvalue; set pvalue; if &pname>.; varname=" "; varname=&rlabel; if &site=1 then datasource= &source1; if &site=2 then datasource= &source2; if &site=3 then datasource= &source3; if &site=-1 then datasource="All Data"; run;
proc print; run;
proc means data=temp n; class &col; var &row; output out=ntotal n=n; run;
data na; set ntotal; if &col=1 and _type_=1; run;
data nb;set ntotal; if &col=2 and _type_=1; run;
data ntotal; merge na(rename=(n=na)) nb(rename=(n=nb)); run;
data ntotal; set ntotal; arma="(n="||put(na, 5.)||")"; armb="(n="||put(nb, 5.)||")"; *drop na nb; run;
proc print; run;
proc print data=pvalue; run; data total; merge ntotal pvalue; if &pname<0.001 then p='<.001'; else if &pname>=0.01 then p= put(&pname, 3.2); else if &pname>=0.001 then p= put(&pname, 4.3); /* if &pname<=0.01 and &pname>. then p="<0.01"; else p=put(&pname, 6.3); */ totals=" (n="||put(na+nb, 5.)||")"; allnmiss=put(nmiss, 5.); run;
proc print data=total; run; data four; set total both; keep datasource varname var1 totals allnmiss arma armb totals p; if &row=. then var1=" "; else var1=put(&row, &rowf); run; proc print data=four; run;
data last(keep=varname datasource var1 arma armb totals p); set last four; run;
proc contents; run;
proc print data=last noobs label; var varname datasource var1 arma armb totals p; run;
%mend;
%macro table3(site=,alldata=, bydata=, row=, rlabel=, rowf=, col=, pname=,missnum=, source1=" ", source2=" ", source3=" ", pctfmt=3.0); data temp; merge &alldata &bydata; by idnum; col=&col; row=&row; if &col>. ; keep idnum &row &col &stratvar;
%if &site>-1 %then %do; data temp; set temp; if &stratvar=&site; %end;
%if &site=-1 %then %do; data temp; set temp; %end;
proc sort data=temp; by &col &row;
data temp; set temp; if &row>=&missnum then &row=.;
proc freq data= temp; by &col; tables &row/ out=counts noprint; data col1; set counts; if &col=1; data col2; set counts; if &col=2; data col3; set counts; if &col=3;
***** calculating combined rows******; proc freq data= temp; tables &row/ out=totals noprint;
data tot; set totals; totals=put(count, 5.)||'('||put(percent, &pctfmt)||'%)'; keep &row totals; if &row>.; data both; merge col1(rename=(count=cola percent=pcta)) col2(rename=(count=colb percent=pctb)) col3(rename=(count=colc percent=pctc)); by &row; if &row>. and cola=. then cola=0; if &row>. and colb=. then colb=0; if &row>. and colc=. then colc=0; if &row>. and pcta=. then pcta=0; if &row>. and pctb=. then pctb=0; if &row>. and pctc=. then pctc=0; if &row>.; keep &row cola colb colc pcta pctb pctc arma armb armc; arma=put(cola, 5.)||'('||put(pcta, &pctfmt)||'%)'; armb=put(colb, 5.)||'('||put(pctb, &pctfmt)||'%)'; armc=put(colc, 5.)||'('||put(pctc, &pctfmt)||'%)'; data both; merge both tot; by &row; proc print; /* data temp; set temp; if &row>=&missnum then &row=.; */ data pvalue; &pname=99999;
proc freq data= temp; tables &row*&col/all; output out=pvalue pchi fisher cmh n nmiss; data pvalue; set pvalue; if &pname>.; varname=" "; varname=&rlabel; if &site=1 then datasource= &source1; if &site=2 then datasource= &source2; if &site=3 then datasource= &source3; if &site=-1 then datasource="All Data"; proc print;
proc means data=temp n; class &col; var &row; output out=ntotal n=n; data na; set ntotal; if &col=1 and _type_=1; data nb;set ntotal; if &col=2 and _type_=1; data nc;set ntotal; if &col=3 and _type_=1; data ntotal; merge na(rename=(n=na)) nb(rename=(n=nb)) nc(rename=(n=nc)); if na=. then na=0; if nb=. then nb=0; if nc=. then nc=0;
data ntotal; set ntotal; arma=" (n="||put(na, 5.)||")"; armb=" (n="||put(nb, 5.)||") "; armc="(n="||put(nc, 5.)||")"; *drop na nb; proc print;
proc print data=pvalue; data total; merge ntotal pvalue; if &pname<0.001 then p='<.001'; else if &pname>=0.01 then p= put(&pname, 3.2); else if &pname>=0.001 then p= put(&pname, 4.3); totals="(n="||put(na+nb+nc, 5.)||")"; allnmiss=put(nmiss, 4.); proc print data=total; data four; set total both; keep datasource varname var1 totals allnmiss arma armb armc totals p; if &row=. then var1=" "; else var1=put(&row, &rowf); proc print data=four; data last(keep=varname datasource var1 arma armb armc totals p); set last four;
proc contents; proc print data=last noobs label; var varname datasource var1 arma armb armc totals p; %mend;
%macro table4(site=,alldata=, bydata=, row=, rlabel=, rowf=, col=, pname=,missnum=, source1=" ", source2=" ", source3=" "); data temp; merge &alldata &bydata; by idnum; col=&col; row=&row; if &col>. ; keep idnum &row &col &stratvar;
%if &site>-1 %then %do; data temp; set temp; if &stratvar=&site; %end;
%if &site=-1 %then %do; data temp; set temp; %end;
proc sort data=temp; by &col &row;
data temp; set temp; if &row>=&missnum then &row=.;
proc freq data= temp; by &col; tables &row/ out=counts noprint; data col1; set counts; if &col=1; data col2; set counts; if &col=2; data col3; set counts; if &col=3; data col4; set counts; if &col=4;
***** calculating combined rows******; proc freq data= temp; tables &row/ out=totals noprint;
data tot; set totals; totals=put(count, 5.)||'('||put(percent, &pctfmt)||'%)'; keep &row totals; if &row>.; data both; merge col1(rename=(count=cola percent=pcta)) col2(rename=(count=colb percent=pctb)) col3(rename=(count=colc percent=pctc)) col4(rename=(count=cold percent=pctd)); by &row; if &row>. and cola=. then cola=0; if &row>. and colb=. then colb=0; if &row>. and colc=. then colc=0; if &row>. and cold=. then cold=0; if &row>. and pcta=. then pcta=0; if &row>. and pctb=. then pctb=0; if &row>. and pctc=. then pctc=0; if &row>. and pctd=. then pctd=0; if &row>.; keep &row cola colb colc colcd pcta pctb pctc pctd arma armb armc armd; arma=put(cola, 5.)||'('||put(pcta, &pctfmt)||'%)'; armb=put(colb, 5.)||'('||put(pctb, &pctfmt)||'%)'; armc=put(colc, 5.)||'('||put(pctc, &pctfmt)||'%)'; armd=put(cold, 5.)||'('||put(pctd, &pctfmt)||'%)';
data both; merge both tot; by &row; proc print; /* data temp; set temp; if &row>=&missnum then &row=.; */ data pvalue; &pname=99999;
proc freq data= temp; tables &row*&col/all; output out=pvalue pchi fisher cmh n nmiss; data pvalue; set pvalue; if &pname>.; varname=" "; varname=&rlabel; if &site=1 then datasource= &source1; if &site=2 then datasource= &source2; if &site=3 then datasource= &source3; if &site=-1 then datasource="All Data"; proc print;
proc means data=temp n; class &col; var &row; output out=ntotal n=n; data na; set ntotal; if &col=1 and _type_=1; data nb;set ntotal; if &col=2 and _type_=1; data nc;set ntotal; if &col=3 and _type_=1; data nd;set ntotal; if &col=4 and _type_=1;
data ntotal; merge na(rename=(n=na)) nb(rename=(n=nb)) nc(rename=(n=nc)) nd(rename=(n=nd)); data ntotal; set ntotal; arma=" (n="||put(na, 5.)||")"; armb="(n="||put(nb, 5.)||")"; armc=" (n="||put(nc, 5.)||")"; armd="(n="||put(nd, 5.)||")"; proc print;
proc print data=pvalue; data total; merge ntotal pvalue; *if &pname<=0.0001 and &pname>. then p="<0.0001"; *else p=put(&pname, 6.4); *totals=" (n="||put(na+nb+nc+nd, 4.)||")"; allnmiss=put(nmiss, 4.); *proc print data=total; if &pname<0.001 then p='<.001'; else if &pname>=0.01 then p= put(&pname, 3.2); else if &pname>=0.001 then p= put(&pname, 3.2); totals="(n="||put(sum(na, nb, nc, nd), 5.)||")"; allnmiss=put(nmiss, 4.); proc print data=total;
data four; set total both; keep datasource varname var1 totals allnmiss arma armb armc armd totals p; if &row=. then var1=" "; else var1=put(&row, &rowf); proc print data=four; data last(keep=varname datasource var1 arma armb armc armd totals p); set last four;
proc contents; proc print data=last noobs label; var varname datasource var1 arma armb armc armd totals p; %mend;
%macro table5(site=,alldata=, bydata=, row=, rlabel=, rowf=, col=, pname=,missnum=, source1=" ", source2=" ", source3=" ", pctfmt=3.0); data temp; merge &alldata &bydata; by idnum; col=&col; row=&row; if &col>. ; keep idnum &row &col &stratvar; run;
%if &site>-1 %then %do; data temp; set temp; if &stratvar=&site; %end; run;
%if &site=-1 %then %do; data temp; set temp; %end; run;
proc sort data=temp; by &col &row; run; data temp; set temp; if &row>=&missnum then &row=.; run; proc freq data= temp; by &col; tables &row/ out=counts noprint;run; data col1; set counts; if &col=1;run; data col2; set counts; if &col=2;run; data col3; set counts; if &col=3;run; data col4; set counts; if &col=4;run; data col5; set counts; if &col=5;run;
***** calculating combined rows******; ***Changed the 4.1s proc freq data= temp; tables &row/ out=totals noprint; run; data tot; set totals; totals=put(count, 5.)||'('||put(percent, &pctfmt)||'%)'; keep &row totals; if &row>.; run; data both; merge col1(rename=(count=cola percent=pcta)) col2(rename=(count=colb percent=pctb)) col3(rename=(count=colc percent=pctc)) col4(rename=(count=cold percent=pctd)) col5(rename=(count=cole percent=pcte)); by &row; if &row>. and cola=. then cola=0; if &row>. and colb=. then colb=0; if &row>. and colc=. then colc=0; if &row>. and cold=. then cold=0; if &row>. and cole=. then cole=0; if &row>. and pcta=. then pcta=0; if &row>. and pctb=. then pctb=0; if &row>. and pctc=. then pctc=0; if &row>. and pctd=. then pctd=0; if &row>. and pcte=. then pcte=0;
if &row>.; keep &row cola colb colc colcd colce pcta pctb pctc pctd pcte arma armb armc armd arme; arma=put(cola, 5.)||'('||put(pcta, &pctfmt)||'%)'; armb=put(colb, 5.)||'('||put(pctb, &pctfmt)||'%)'; armc=put(colc, 5.)||'('||put(pctc, &pctfmt)||'%)'; armd=put(cold, 5.)||'('||put(pctd, &pctfmt)||'%)'; arme=put(cole, 5.)||'('||put(pcte, &pctfmt)||'%)'; run; data both; merge both tot; by &row; run; proc print;run; /* data temp; set temp; if &row>=&missnum then &row=.; */ data pvalue; &pname=99999;run;
proc freq data= temp; tables &row*&col/all; output out=pvalue pchi fisher cmh n nmiss;run; data pvalue; set pvalue; if &pname>.; varname=" "; varname=&rlabel; if &site=1 then datasource= &source1; if &site=2 then datasource= &source2; if &site=3 then datasource= &source3; if &site=-1 then datasource="All Data";run; proc print; run;
proc means data=temp n; class &col; var &row; output out=ntotal n=n; run; data na; set ntotal; if &col=1 and _type_=1;run; data nb;set ntotal; if &col=2 and _type_=1;run; data nc;set ntotal; if &col=3 and _type_=1;run; data nd;set ntotal; if &col=4 and _type_=1;run; data ne;set ntotal; if &col=5 and _type_=1; run;
data ntotal; merge na(rename=(n=na)) nb(rename=(n=nb)) nc(rename=(n=nc)) nd(rename=(n=nd)) ne(rename=(n=ne));run; data ntotal; set ntotal; if na=. then na=0; if nb=. then nb=0; if nc=. then nc=0; if nd=. then nd=0; if ne=. then ne=0; arma="(n="||put(na, 5.)||")"; armb="(n="||put(nb, 5.)||")"; armc=" (n="||put(nc, 5.)||")"; armd="(n="||put(nd, 5.)||")"; arme=" (n="||put(ne, 5.)||")"; run; proc print;run;
proc print data=pvalue;run; data total; merge ntotal pvalue; if &pname<=0.0001 and &pname>. then p="<0.0001"; else p=put(&pname, 6.4); totals="(n="||put(sum(na,nb,nc,nd,ne), 5.)||")"; allnmiss=put(nmiss, 4.);run; proc print data=total; run; data four; set total both; keep datasource varname var1 totals allnmiss arma armb armc armd arme totals p; if &row=. then var1=" "; else var1=put(&row, &rowf);run; proc print data=four;run; data last(keep=varname datasource var1 arma armb armc armd arme totals p); set last four; run; proc contents;run; proc print data=last noobs label; var varname datasource var1 arma armb armc armd arme totals p;run; %mend;
data last; totals=" "; %macro conti(site=,alldata=, bydata=, row=, rlabel=, col=, numfmt=6.0, source1=" ", source2=" ", source3=" ");
data temp; merge &alldata &bydata(in=idu1); by idnum; if idu1; if &col>. ; keep &row &col &stratvar; if &col=0 then &col=2; *** change 0 to 2 as no****; run; %if &site>-1 %then %do; data one; set temp; if &stratvar=&site; %end; run; %if &site=-1 %then %do; data one; set temp; %end; run;
proc sort data=one; by &col; run; proc univariate data= one noprint; var &row; by &col; output out=out2 median=median min=min max=max q1=q1 q3=q3 mean=mean std=std n=n; run;
proc univariate data= one noprint; var &row; output out=out5 median=median min=min max=max q1=q1 q3=q3 mean=mean std=std n=n; run;
data out5; set out5; &col=100; run; data out2; set out2 out5; run; proc print data=out5; run;
proc transpose data=out2 out=out3 ( drop=_label_); id &col; run; /* data null; set out3; if _NAME_="n"; %if (_1=0 or _2=0) %then %goto endmacro; */ proc print data=out3; run; data out3; set out3; arma=put(_1, &numfmt); armb=put(_2, &numfmt); totals=put(_100, &numfmt); run;
proc npar1way data=one wilcoxon; class &col; var &row; output out=pvals wilcoxon; data pvals(keep=variable pvalue); set pvals; rename _var_= variable; if p2_wil^=. then pvalue=p2_wil; else if p_kw^=. then pvalue=p_kw; else pvalue=.; run;
data ntotal; set pvals; arma=" "; armb=" "; /* if pvalue<=0.01 then p="<0.01"; else p=put(pvalue, 6.3); */
if pvalue<0.001 then p='<.001'; else if pvalue>=0.050001 then p= put(pvalue, 3.2); else if pvalue>=0.001 then p= put(pvalue, 4.3);
varname=&rlabel; *if varname="" then varname=variable; if &site=1 then datasource= &source1; if &site=2 then datasource= &source2; if &site=3 then datasource= &source3;
if &site=-1 then datasource="All Data"; run; data four; set ntotal out3; run;
data last(keep=varname datasource var1 arma armb totals p); set last four(rename=(_name_=var1)); run; proc print data=last noobs label; var varname datasource var1 arma armb totals p; ; run;
%endmacro:
%mend;
%macro conti4(site=,alldata=, bydata=, row=, rlabel=, col=, numfmt=6.0, source1=" ", source2=" ", source3=" ");
data temp; merge &alldata &bydata(in=idu1); by idnum; if idu1; if &col>. ; keep &row &col &stratvar; %if &site>-1 %then %do; data one; set temp; if &stratvar=&site; %end; %if &site=-1 %then %do; data one; set temp; %end;
proc sort data=one; by &col; proc univariate data= one noprint; var &row; by &col; output out=out2 median=median min=min max=max q1=q1 q3=q3 mean=mean std=std n=n;
proc univariate data= one noprint; var &row; output out=out5 median=median min=min max=max q1=q1 q3=q3 mean=mean std=std n=n;
data out5; set out5; &col=100; data out2; set out2 out5;
proc print data=out5;
proc transpose data=out2 out=out3 ( drop=_label_); id &col; proc print data=out3; data out3; set out3; arma=put(_1, &numfmt); armb=put(_2, &numfmt); armc=put(_3, &numfmt); armd=put(_4, &numfmt); totals=put(_100, &numfmt);
proc npar1way data=one wilcoxon; class &col; var &row; output out=pvals wilcoxon; data pvals(keep=variable pvalue); set pvals; rename _var_= variable; /*if p2_wil^=. then pvalue=p2_wil;*/ if p_kw^=. then pvalue=p_kw; else pvalue=.; run;
data ntotal; set pvals; arma=" "; armb=" "; *if pvalue<=0.0001 then p="<0.0001"; *else p=put(pvalue, 6.4); *varname=&rlabel; *if varname="" then varname=variable;
if pvalue<0.001 then p='<.001'; else if pvalue>=0.050001 then p= put(pvalue, 4.3); else if pvalue>=0.001 then p= put(pvalue, 4.3); varname=&rlabel;
if &site=1 then datasource= &source1; if &site=2 then datasource= &source2; if &site=3 then datasource= &source3;
if &site=-1 then datasource="All Data"; data four; set ntotal out3; data last(keep=varname datasource var1 arma armb armc armd totals p); set last four(rename=(_name_=var1)); proc print data=last noobs label; var varname datasource var1 arma armb armc armd totals p; ;
%mend;
%macro conti5(site=,alldata=, bydata=, row=, rlabel=, col=, numfmt=6.0, source1=" ", source2=" ", source3=" ");
data temp; merge &alldata &bydata(in=idu1); by idnum; if idu1; if &col>. ; keep &row &col &stratvar;run; %if &site>-1 %then %do; data one; set temp; if &stratvar=&site; %end;run; %if &site=-1 %then %do; data one; set temp; %end;run;
proc sort data=one; by &col;run; proc univariate data= one noprint; var &row; by &col; output out=out2 median=median min=min max=max q1=q1 q3=q3 mean=mean std=std n=n; run; proc univariate data= one noprint; var &row; output out=out5 median=median min=min max=max q1=q1 q3=q3 mean=mean std=std n=n; run; data out5; set out5; &col=100;run; data out2; set out2 out5;run;
proc print data=out5;run;
proc transpose data=out2 out=out3 ( drop=_label_); id &col;run; proc print data=out3;run; data out3; set out3; arma=put(_1, &numfmt); armb=put(_2, &numfmt); armc=put(_3, &numfmt); armd=put(_4, &numfmt); arme=put(_4, &numfmt); totals=put(_100, &numfmt); run; proc npar1way data=one wilcoxon; class &col; var &row; output out=pvals wilcoxon; data pvals(keep=variable pvalue); set pvals; rename _var_= variable; if p2_wil^=. then pvalue=p2_wil; else if p_kw^=. then pvalue=p_kw; else pvalue=.; run;
data ntotal; set pvals; arma=" "; armb=" "; if pvalue<=0.0001 then p="<0.0001"; else p=put(pvalue, 6.4); varname=&rlabel; *if varname="" then varname=variable;
if &site=1 then datasource= &source1; if &site=2 then datasource= &source2; if &site=3 then datasource= &source3;
if &site=-1 then datasource="All Data"; run; data four; set ntotal out3;run; data last(keep=varname datasource var1 arma armb armc armd arme totals p); set last four(rename=(_name_=var1));run; proc print data=last noobs label; var varname datasource var1 arma armb armc armd arme totals p; ; run;
%mend;
%macro conti3(site=,alldata=, bydata=, row=, rlabel=, col=, numfmt=6.0, source1=" ", source2=" ", source3=" ");
data temp; merge &alldata &bydata(in=idu1); by idnum; if idu1; if &col>. ; keep &row &col &stratvar; %if &site>-1 %then %do; data one; set temp; if &stratvar=&site; %end; %if &site=-1 %then %do; data one; set temp; %end;
proc sort data=one; by &col; proc univariate data= one noprint; var &row; by &col; output out=out2 median=median min=min max=max q1=q1 q3=q3 mean=mean std=std n=n;
proc univariate data= one noprint; var &row; output out=out5 median=median min=min max=max q1=q1 q3=q3 mean=mean std=std n=n;
data out5; set out5; &col=100; data out2; set out2 out5;
proc print data=out5;
proc transpose data=out2 out=out3 ( drop=_label_); id &col; proc print data=out3; data out3; set out3; arma=put(_1, &numfmt); armb=put(_2, &numfmt); armc=put(_3, &numfmt); totals=put(_100, &numfmt);
proc npar1way data=one wilcoxon; class &col; var &row; output out=pvals wilcoxon; data pvals(keep=variable pvalue); set pvals; rename _var_= variable; if p2_wil^=. then pvalue=p2_wil; else if p_kw^=. then pvalue=p_kw; else pvalue=.; run;
data ntotal; set pvals; arma=" "; armb=" "; if pvalue<=0.0001 then p="<0.0001"; else p=put(pvalue, 6.4); varname=&rlabel; *if varname="" then varname=variable;
if &site=1 then datasource= &source1; if &site=2 then datasource= &source2; if &site=3 then datasource= &source3;
if &site=-1 then datasource="All Data"; data four; set ntotal out3; data last(keep=varname datasource var1 arma armb armc totals p); set last four(rename=(_name_=var1)); proc print data=last noobs label; var varname datasource var1 arma armb armc totals p; ; %mend;
data last; alln=" ";
%table2(site=-1,alldata=alldata, bydata=idu, row=&var, rlabel=" ", rowf=&varfmt,col=idu, missnum=&missnum, pname=&testname, source3="All data"); %mend;
%macro bar(var=, varlabel=, varfmt=, testname=xp2_fish, missnum=900);
%table2(site=-1,alldata=alldata, bydata=bar, row=&var, rlabel=" ", rowf=&varfmt,col=bar, missnum=&missnum, pname=&testname, source3="All data"); %mend;
******Continuous variables***********; %macro cbar(var=, varlabel=, missnum=9000, numbfmt=6.0); %conti(site=-1,alldata=alldata, bydata=bar,row=&var, rlabel=" ",col=bar, source3="All data", numfmt=&numbfmt); %mend;
%macro site(var=, varlabel=, varfmt=, testname=xp2_fish, missnum=900); %table2(site=-1,alldata=alldata, bydata=bydata, row=&var, rlabel=&varlabel, rowf=&varfmt,col=site, missnum=&missnum, pname=&testname, source3="All data"); %mend;
%macro csite(var=, varlabel=, missnum=9000, numbfmt=6.0); %conti(site=-1,alldata=alldata, bydata=bydata,row=&var, rlabel=&varlabel,col=site, source3="All data", numfmt=&numbfmt); %mend;
*****generalized macros*******;
%macro categ(var=, varlabel=, varfmt=, testname=xp2_fish, missnum=900);
%table2(site=-1,alldata=&alldata, bydata=&bydata, row=&var, rlabel=" ", rowf=&varfmt,col=&cvar, missnum=&missnum, pname=&testname, source3="All data"); %mend;
******Continuous variables***********; %macro cont(var=, varlabel=, missnum=9000, numbfmt=6.0); %conti(site=1,alldata=&alldata, bydata=&bydata,row=&var,
%conti(site=-1,alldata=&alldata, bydata=&bydata,row=&var, rlabel=" ",col=&cvar, source3="All data", numfmt=&numbfmt); %mend;
%macro logistic(var=, varlabel=);
proc logistic descending data=alldata; model &yvar=&var /risklimits lackfit; ODS output ParameterEstimates=uni CLOddsWald=ci; run;
data uni; set uni(keep=variable Estimate ProbChisq); if variable^="Intercept"; data uni; set uni; obs=_N_; data ci; set ci (keep=OddsRatioEst LowerCL UpperCL); obs=_N_; data unici; merge uni ci; by obs; drop obs; varlabel=&varlabel;
data unilogistic; set unilogistic unici;
%mend; data unilogistic; variable=" ";
... View more
