How to create report with varying column and row numbers using proc report?

jp2708 — Thu, 14 Dec 2023 09:49:32 GMT

Hi all,

I need to create the following format of the output but i am finding it difficult because of the different column and row numbers.

can anyone help me with this?

Thanks.

Re: How to create report with varying column and row numbers using proc report?

andreas_lds — Thu, 14 Dec 2023 11:50:47 GMT

Can be done using Report Writing Interface. Can you post data in usable form?

Re: How to create report with varying column and row numbers using proc report?

Ksharp — Fri, 15 Dec 2023 05:32:06 GMT

Yes. Using RWI skill.

Here is two example:

data table3way;
  length dmtype durationbi $15;
  infile datalines dlm=',' dsd;
  input dmtype $ ordvar Durationbi $ Num cMean cSTD LowerCL UpperCL Prob_t;
return;
datalines;
"Type 1",1,"<=10 years",17,76.76,17.85,67.59,85.94,0.7243
"Type 1",2,">10 year",44,75.30,13.06,71.32,79.27,0.7243
"Type 2",1,"<=10 years",47,67.23,22.22,44.44,88.88,0.5584
"Type 2",2,">10 year",35,69.43,33.33,55.55,99.99,0.5584
;
run;

options orientation=portrait;

ods html(id=ht) path='c:\temp\' file="spantest.html";
ods rtf (id=pd) file="c:\temp\spantest.rtf"   ;

title 'How to span Header Rows amd Data Rpws with RWI and DATA step';
data _null_; 
  set table3way end=last; 
  by dmtype;
  if _N_ = 1 then do; 
      dcl odsout obj(); 
      obj.table_start(); 
      obj.head_start(); 
	  ** Header row 1;
      obj.row_start(type: "Header"); 
      obj.format_cell(text: "Type", row_span:2, column_span: 1, style_attr:"vjust=m color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "Results", column_span:7, style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.row_end(); 
	  ** Header row 2;
      obj.row_start(type: "Header"); 
      obj.format_cell(text: "Durationbi", style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "num", style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "cmean", style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "cstd", style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "lowercl",style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "uppercl", style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "probt",style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.row_end(); 
      obj.head_end(); 
    end;
  ** row for every obs;
  ** treat dmtype and prob_t differently so they span rows;

      obj.row_start(); 
	  if first.dmtype then do;
         obj.format_cell(data: dmtype,row_span:2, style_attr:"vjust=m fontweight=bold" ); 
	  end;
      obj.format_cell(data: Durationbi, row_span:1); 
      obj.format_cell(data: num, row_span:1); 
      obj.format_cell(data: cmean, row_span:1); 
      obj.format_cell(data: cstd, row_span:1); 
      obj.format_cell(data: lowercl, row_span:1); 
      obj.format_cell(data: uppercl, row_span:1); 
	  if first.dmtype then do;
         obj.format_cell(data: prob_t, row_span:2, style_attr:"vjust=m fontweight=bold"); 
	  end;
      obj.row_end(); 
   
  if last then do; 
      obj.table_end(); 
    end; 
run;
footnote;title;
ods rtf(id=pd) close;
ods html(id=ht) close;

%let path= D:\XiaKeShan\通过程序生成完整版CRF的sas需求\want.docx ;  * WORD文件（CRF）的输出路径;









%let title_left=方案编号	       研究中心标识符	    受试者标识符	      受试者姓名缩写	       访视名称 ;   /*页眉的左边部分*/
%let title_right=XXX	             |__|__|	       |__|__|__|__|__|	        |__|__|__|	      访视1-筛选期  ;   /*页眉的右边部分*/

%let footnote_left= 研究者签字：________________________;   /*页脚的左边部分*/
%let footnote_right=签字日期：20|__|__|年|__|__|月|__|__|日 ;                   /*页脚的右边部分*/













title     j=l "(*ESC*)S={fontstyle=roman fontweight=light FONTFAMILY='宋体' fontsize=0.38cm asis=on}&title_left." ;

title2    j=l "(*ESC*)S={fontstyle=roman fontweight=light FONTFAMILY='宋体' fontsize=0.38cm asis=on}&title_right.";

footnote  j=l "(*ESC*)S={fontstyle=roman fontweight=light FONTFAMILY='宋体' fontsize=0.38cm}&footnote_left." 
          j=r "(*ESC*)S={fontstyle=roman fontweight=light FONTFAMILY='宋体' fontsize=0.38cm}&footnote_right.";

options validvarname=any validmemname=extend nodate nonumber leftmargin=1.91cm rightmargin=1.91cm topmargin=2.54cm bottommargin=2.54cm  ; 
ods path work.template(update) sashelp.tmplmst;
proc template;
define style styles._journal;
parent=styles.word;  /*word journal*/
class table / margintop=20 rules=all frame=box NOBREAKSPACE=OFF outputwidth=100%  cellpadding=10 cellspacing=0 FONTFAMILY='宋体' just=l; /*cellpadding=0 cellspacing=0*/
style header from header / background=black;
end;
run;




/**********************************/
data 签署知情同意书;
infile cards truncover;
input a :$30. b $200.;
cards;
签署知情同意书   .
签署知情同意书日期	~{style [FONTFAMILY='Times New Roman Uni']|__|__|__|__|}年~{style [FONTFAMILY='Times New Roman Uni']|__|__|}月~{style [FONTFAMILY='Times New Roman Uni']|__|__|}日
;
%macro crf1;
proc odstext PAGEBREAK= No contents="" ;
h2 "签署知情同意书" / style={just=l color=black  fontweight=bold FONTFAMILY='宋体' fontsize=0.50cm  };
run ;
data _null_; 
  set 签署知情同意书 end=last; 
    if _N_ = 1 then do; 
      dcl odsout obj(); 
      obj.table_start(); 
    end;
  ** row for every obs;
      obj.row_start(); 
  if _n_=1 then  obj.format_cell(data: a,column_span:2, style_attr:"fontweight=bold FONTFAMILY='宋体' fontsize=0.50cm background=graybb just=l" ); 
    else do;
      obj.format_cell(data: a, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m just=l "); 
      obj.format_cell(data: b, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m just=l  "); 
	end;
      obj.row_end(); 
   
  if last then do; 
      obj.table_end(); 
    end; 
run;
%mend;




/******************************/
data 人口统计学资料;
infile cards truncover;
input a :$30. b $200.;
cards;
人口统计学资料   .
出生日期	 ~{style [FONTFAMILY='Times New Roman Uni']|__|__|__|__|}年~{style [FONTFAMILY='Times New Roman Uni']|__|__|}月~{style [FONTFAMILY='Times New Roman Uni']|__|__|}日
民族	     ○汉族   ○其他 ~{style [color=white FONTFAMILY='Times New Roman Uni']x}
其他民族	 ~{style [FONTFAMILY='Times New Roman Uni']__________________}
性别   	○男   ○女 ~{style [color=white FONTFAMILY='Times New Roman Uni']x}
身高  	~{style [FONTFAMILY='Times New Roman Uni']|__|__|__|.|__| cm}
体重  	~{style [FONTFAMILY='Times New Roman Uni']|__|__|__|.|__| kg}
;
%macro crf2;
ods text='  ';
proc odstext PAGEBREAK= No contents="" ;
h2 "人口统计学资料" / style={just=l color=black  fontweight=bold FONTFAMILY='宋体' fontsize=0.50cm  };
run ;
data _null_; 
  set 人口统计学资料 end=last; 
    if _N_ = 1 then do; 
      dcl odsout obj(); 
      obj.table_start(); 
    end;
  ** row for every obs;
      obj.row_start(); 
  if _n_=1 then  obj.format_cell(data: a,column_span:2, style_attr:"fontweight=bold FONTFAMILY='宋体' fontsize=0.50cm background=graybb just=l" ); 
    else do;
      obj.format_cell(data: a, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m just=l "); 
      obj.format_cell(data: b, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m just=l  "); 
	end;
      obj.row_end(); 
   
  if last then do; 
      obj.table_end(); 
    end; 
run;
%mend;


/*************************************/
data 生命体征;
infile cards truncover dlm=',' expandtabs;
input a :$30.  b :$200. c :$80. d :$400. e :$20. f :$20. g $;
cards;
生命体征
是否进行生命体征检查,○是   ○否 ~{style [color=white FONTFAMILY='Times New Roman Uni']x}
检查日期	,~{style [FONTFAMILY='Times New Roman Uni']|__|__|__|__|}年~{style [FONTFAMILY='Times New Roman Uni']|__|__|}月~{style [FONTFAMILY='Times New Roman Uni']|__|__|}日
检查项目,检查结果,单位,临床意义,正常值范围下限,正常值范围上限,备注
体温	,~{style [color=red]（数值型）},	~{style [FONTFAMILY='Times New Roman Uni']℃	},○~{style [FONTFAMILY='Times New Roman Uni']1} ○~{style [FONTFAMILY='Times New Roman Uni']2} ○~{style [FONTFAMILY='Times New Roman Uni']3} ○~{style [FONTFAMILY='Times New Roman Uni']4}
呼吸	,~{style [color=red]（数值型）},	次/分钟,○~{style [FONTFAMILY='Times New Roman Uni']1} ○~{style [FONTFAMILY='Times New Roman Uni']2} ○~{style [FONTFAMILY='Times New Roman Uni']3} ○~{style [FONTFAMILY='Times New Roman Uni']4}
脉搏	,~{style [color=red]（数值型）},	次/分钟,○~{style [FONTFAMILY='Times New Roman Uni']1} ○~{style [FONTFAMILY='Times New Roman Uni']2} ○~{style [FONTFAMILY='Times New Roman Uni']3} ○~{style [FONTFAMILY='Times New Roman Uni']4}
收缩压,~{style [color=red]（数值型）},~{style [FONTFAMILY='Times New Roman Uni']mmHg},○~{style [FONTFAMILY='Times New Roman Uni']1} ○~{style [FONTFAMILY='Times New Roman Uni']2} ○~{style [FONTFAMILY='Times New Roman Uni']3} ○~{style [FONTFAMILY='Times New Roman Uni']4}
舒张压,~{style [color=red]（数值型）},~{style [FONTFAMILY='Times New Roman Uni']mmHg},○~{style [FONTFAMILY='Times New Roman Uni']1} ○~{style [FONTFAMILY='Times New Roman Uni']2} ○~{style [FONTFAMILY='Times New Roman Uni']3} ○~{style [FONTFAMILY='Times New Roman Uni']4}
;
%macro crf3;
ods text='  ';
proc odstext PAGEBREAK= No contents="" ;
h2 "生命体征" / style={just=l color=black  fontweight=bold FONTFAMILY='宋体' fontsize=0.50cm  };
run ;
data _null_; 
  set 生命体征 end=last; 
    if _N_ = 1 then do; 
      dcl odsout obj(); 
      obj.table_start(); 
    end;
  ** row for every obs;
      obj.row_start(); 
  if _n_=1 then  obj.format_cell(data: a,column_span:7, style_attr:"fontweight=bold FONTFAMILY='宋体' fontsize=0.50cm background=graybb just=l" ); 
    else if _n_ in (2 3) then do;
      obj.format_cell(data: a, column_span:2, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm  vjust=m just=l  "); 
      obj.format_cell(data: b, column_span:5, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m  just=l   "); 
	end;
    else if _n_=4 then do;
      obj.format_cell(data: a, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m  just=c cellpadding=0 background=graybb cellwidth=1.9cm"); 
      obj.format_cell(data: b, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m  just=c cellpadding=0 background=graybb cellwidth=1.9cm "); 
	  obj.format_cell(data: c, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m  just=c cellpadding=0 background=graybb cellwidth=1.2cm "); 
      obj.format_cell(data: d, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m  just=c cellpadding=0 background=graybb cellwidth=1.9cm"); 
      obj.format_cell(data: e, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m  just=c cellpadding=0 background=graybb cellwidth=1.4cm"); 
      obj.format_cell(data: f, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m  just=c cellpadding=0 background=graybb cellwidth=1.4cm"); 
      obj.format_cell(data: g, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m  just=c cellpadding=0 background=graybb cellwidth=1.2cm"); 
	end;
	else do;
      obj.format_cell(data: a, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m  just=l  "); 
      obj.format_cell(data: b, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m  just=l"); 
	  obj.format_cell(data: c, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m  just=l "); 
      obj.format_cell(data: d, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m  just=l "); 
      obj.format_cell(data: e, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m  just=l "); 
      obj.format_cell(data: f, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m  just=l  " ); 
      obj.format_cell(data: g, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm  vjust=m just=l "); 
	end;
      obj.row_end(); 
   
  if last then do; 
      obj.table_end(); 
    end; 
run;
ods text="~{style [cellpadding=0 fontsize=0.38cm fontfamily='宋体']备注：临床意义：~{style [FONTFAMILY='Times New Roman Uni']1}：正常 ~{style [FONTFAMILY='Times New Roman Uni']2}：异常无临床意义 ~{style [FONTFAMILY='Times New Roman Uni']3}：异常有临床意义 ~{style [FONTFAMILY='Times New Roman Uni']4}：未查}";
/*
ods text="~{style [cellpadding=0 fontsize=0.38cm fontfamily='宋体']备注：临床意义：1：正常 2：异常无临床意义 3：异常有临床意义 4：未查}";
*/
%mend;


/*********************************/
data 既往病史;
infile cards truncover dlm=',';
input a :$30. b:$200. c :$80. d $80.;
cards;
既往病史
有无既往病史,○无   ○有（若有，请填写下表）~{style [color=white FONTFAMILY='Times New Roman Uni']x}
疾病名称,开始日期（年/月/日）,是否持续 ~{style [color=graybb FONTFAMILY='Times New Roman Uni']x},结束日期（年/月/日）
.,.,○否   ○是, ~{style [color=white FONTFAMILY='Times New Roman Uni']x}
.,.,○否   ○是, ~{style [color=white FONTFAMILY='Times New Roman Uni']x}
.,.,○否   ○是, ~{style [color=white FONTFAMILY='Times New Roman Uni']x}
;
%macro crf4;

ods text='  ';
proc odstext PAGEBREAK= No contents="" ;
h2 "既往病史" / style={just=l color=black  fontweight=bold FONTFAMILY='宋体' fontsize=0.50cm  };
run ;
data _null_; 
  set 既往病史 end=last; 
    if _N_ = 1 then do; 
      dcl odsout obj(); 
      obj.table_start(); 
    end;
  ** row for every obs;
      obj.row_start(); 
  if _n_=1 then  obj.format_cell(data: a,column_span:4, style_attr:"fontweight=bold FONTFAMILY='宋体' fontsize=0.50cm background=graybb just=l" ); 
    else if _n_=2 then do;
      obj.format_cell(data: a, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m just=l "); 
      obj.format_cell(data: b, column_span:3, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m just=l  "); 
	end;
	else if _n_=3 then do;
      obj.format_cell(data: a, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m just=c background=graybb"); 
      obj.format_cell(data: b, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m just=c background=graybb "); 
	  obj.format_cell(data: c, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m just=c background=graybb"); 
      obj.format_cell(data: d, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m just=c background=graybb "); 
	end;
    else do;
      obj.format_cell(data: a, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m just=c cellwidth=1.9cm"); 
      obj.format_cell(data: b, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m just=c cellwidth=2.5cm "); 
	  obj.format_cell(data: c, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m just=c cellwidth=2.5cm"); 
      obj.format_cell(data: d, column_span:1, style_attr:"FONTFAMILY='宋体' fontsize=0.38cm vjust=m just=c cellwidth=2.5cm "); 
	end;
      obj.row_end(); 
   
  if last then do; 
      obj.table_end(); 
    end; 
run;
%mend;




/******************************/
ods escapechar='~';
ods _all_ close;
ods word file="&path." style=_journal startpage=no;
proc odstext PAGEBREAK= No contents="" ;
h1 "访视 1-筛选期" / style={just=l color=black  fontweight=bold FONTFAMILY='宋体' fontsize=0.50cm  };
run ;

%crf1
%crf2
%crf3
%crf4


ods word close;

topic How to create report with varying column and row numbers using proc report? in SAS Programming

How to create report with varying column and row numbers using proc report?

Re: How to create report with varying column and row numbers using proc report?

Re: How to create report with varying column and row numbers using proc report?