cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
jp2708
Calcite | Level 5 jp2708
Calcite | Level 5

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

Posted 12-14-2023 04:49 AM (816 views)

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?

 

jp2708_0-1702547362805.png

 


Thanks.

0 Likes
Reply
2 REPLIES 2
andreas_lds
Jade | Level 19 andreas_lds
Jade | Level 19

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

Posted 12-14-2023 06:50 AM (764 views)  |   In reply to jp2708

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

0 Likes
Reply
Ksharp
Super User Ksharp
Super User

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

Posted 12-14-2023 10:12 PM (739 views)  |   In reply to jp2708

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;

Ksharp_0-1702609863424.png

 

 

 

 

 

 

 

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

Ksharp_0-1702618315721.png

 

 

Reply

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • ‎12-14-2023 04:49 AM
  • 817 views
  • 1 like
  • 3 in conversation