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 (483 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 (431 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 (406 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-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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