The SAS Output Delivery System and reporting techniques

Macro and ODS to create table in word

Reply
Frequent Contributor
Posts: 134

Macro and ODS to create table in word

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

 

 

Grand Advisor
Posts: 16,267

Re: Macro and ODS to create table in word

Your column isn't wide enough to hold the values. Make sure your template specifies a width large enough for the column. Also, make sure that the SAS variable created was large enough to hold the value.
Frequent Contributor
Posts: 134

Re: Macro and ODS to create table in word

So do I have to widen the column in my proc print statement or in the macro code? Also, how do I check to make sure the SAS variable created is large enough to hold the value? I assume it should be since it is created the same as the values in the total column. Thank you for your reply. 

Grand Advisor
Posts: 16,267

Re: Macro and ODS to create table in word

1. Check that your data set has the correct values before proc print.
2. I typically use proc report for these and they support a width definition, but I'm not sure how proc print handles the same.
Frequent Contributor
Posts: 134

Re: Macro and ODS to create table in word

I checked the values in the dataset and they are correct. 

 

I can try proc report to run this but what I am most confused by is that proc print doesn't have any issues displaying the column for totals correctly so I am not sure why the other columns aren't displaying correctly. The widths for all of those columns should be the same. 

 

Thank you again. 

Trusted Advisor
Posts: 1,114

Re: Macro and ODS to create table in word

I cannot find a single LENGTH statement in your code. This being the case, it would be surprising if truncation of character strings did not occur somewhere, in some situation.

Esteemed Advisor
Esteemed Advisor
Posts: 6,674

Re: Macro and ODS to create table in word

Hi,

 

Whilst I am not going to spend the time looking through that mass of code I would firstly support Reeza in saying move to proc report, it has far more functionality.  You may also want to go through that code, as I don't see a reason for a wall of code to do what is relatively a simple output table.  Not to mention the code is very difficult to read, no indentations, code blocks unfinished, mixes of macro and datastep, muerous repeats of code etc.

Post a Question
Discussion Stats
  • 6 replies
  • 426 views
  • 0 likes
  • 4 in conversation