Macro and ODS to create table in word

rfarmenta — Wed, 25 Nov 2015 22:15:13 GMT

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;

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;

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;

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;

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=" ");

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

%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=" ");

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);

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=" ";

topic Macro and ODS to create table in word in ODS and Base Reporting

Macro and ODS to create table in word

Re: Macro and ODS to create table in word

Re: Macro and ODS to create table in word

Re: Macro and ODS to create table in word

Re: Macro and ODS to create table in word

Re: Macro and ODS to create table in word

Re: Macro and ODS to create table in word