ODS RTF Proc Report with multiple dividing groups?

[mcook]

Using ODS RTF (or Tagsets.RTF) I am trying to create this Table from this Dataset.

This Table was not created in SAS, so I don't know if it is possible to match exactly.  But any suggestions on how to get close would be extremely helpful.  

[mcook]

the code for the dataset is here

data Temp1;
length Groups SubGroups $ 20;
input Groups $ SubGroups $ nCount Var1 Var2 Var3 Var4 Var5;
datalines;
Group1 SubGroup1A 27 22.67 9.80 -0.70 0.90 70.73
Group1 SubGroup1B 27 23.67 77.27 0.00 0.93 77.70
Group2 SubGroup2A 10 -- -- 0.38 0.93 77.77
Group2 SubGroup2B 10 -- -- -0.89 0.89 9.60
Group2 SubGroup2C 10 79.70 9.77 -0.56 -- --
Group2 SubGroup2D 10 -- -- -0.79 0.99 72.03
Group2 SubGroup2E 14 29.20 70.92 0.00 0.92 70.89
Group3 SubGroup3A 25 23.78 70.59 0.00 0.92 77.06
Group3 SubGroup3B 29 -- -- -0.63 -- --
Total Total 54 45.03 33.00 99.04 43.33 12.01
;
run;

[Reeza]

Yes, this is doable in SAS quite easily. Look into PROC REPORT. 

You can match the format probably as exactly as shown. 

Here's a reference to get you started:

https://support.sas.com/resources/papers/proceedings/pdfs/sgf2008/031-2008.pdf

This paper covers more of the styling options:

https://www.lexjansen.com/sesug/2017/RIV-31.pdf

---

@mcook wrote:

Using ODS RTF (or Tagsets.RTF) I am trying to create this Table from this Dataset.

This Table was not created in SAS, so I don't know if it is possible to match exactly.  But any suggestions on how to get close would be extremely helpful.  

---

[Ksharp]

It is a piece of cake for sas.

data Temp1;
length Groups SubGroups $ 20;
input Groups $ SubGroups $ (nCount Var1 Var2 Var3 Var4 Var5) (?? :best.);
datalines;
Group1 SubGroup1A 27 22.67 9.80 -0.70 0.90 70.73
Group1 SubGroup1B 27 23.67 77.27 0.00 0.93 77.70
Group2 SubGroup2A 10 -- -- 0.38 0.93 77.77
Group2 SubGroup2B 10 -- -- -0.89 0.89 9.60
Group2 SubGroup2C 10 79.70 9.77 -0.56 -- --
Group2 SubGroup2D 10 -- -- -0.79 0.99 72.03
Group2 SubGroup2E 14 29.20 70.92 0.00 0.92 70.89
Group3 SubGroup3A 25 23.78 70.59 0.00 0.92 77.06
Group3 SubGroup3B 29 -- -- -0.63 -- --
Total Total 54 45.03 33.00 99.04 43.33 12.01
;
run;

proc format;
value fmt
.='--';
run;;


data have;
 set temp1;
 if Groups='Total' then Groups='  '||Groups;
run;

ods rtf file='c:\temp\temp.rtf' style=journal bodytitle;
proc report data=have nowd  
style(header)={background=verylightgray fontstyle=roman fontweight=bold just=c borderbottomcolor=black borderbottomwidth=2}
style(report)={rules=cols outputwidth=100% } 
style(lines)={just=l bordertopcolor=black bordertopwidth=2 borderbottomcolor=black borderbottomwidth=2 background=grayee fontweight=bold};
define groups/group noprint;
define SubGroups/group style={just=r } style(header)={just=c} 'Groups';
define nCount/sum style={just=c} format=fmt.;
define var1-var5/sum style={just=c}  format=fmt.;

compute before groups;
if strip(Groups)='Total' then len=0;
 else len=20;
line groups $varying20. len;
endcomp;
run;
ods rtf close;

 

[Jianmin]

Hi @Ksharp and @mcook , 

It's a really delicious piece of cake.  I would like to ask @Ksharp for a little bit more difficult table: If I have more data, the table will take a few pages.  What SAS can do to make the Total row on the top of each page, the the group header row also follows the data.  I made a few copies of the data and paste, so the table will have two pages.  See the code, using your program.  The attached file is an example of what I want, and it was created by a SAS macro and data _null_. 

data Temp1;
length Groups SubGroups $ 20;
input Groups $ SubGroups $ (nCount Var1 Var2 Var3 Var4 Var5) (?? :best.);
datalines;
Group1 SubGroup1A 27 22.67 9.80 -0.70 0.90 70.73
Group1 SubGroup1B 27 23.67 77.27 0.00 0.93 77.70
Group2 SubGroup2A 10 -- -- 0.38 0.93 77.77
Group2 SubGroup2B 10 -- -- -0.89 0.89 9.60
Group2 SubGroup2C 10 79.70 9.77 -0.56 -- --
Group2 SubGroup2D 10 -- -- -0.79 0.99 72.03
Group2 SubGroup2E 14 29.20 70.92 0.00 0.92 70.89
Group3 SubGroup3A 25 23.78 70.59 0.00 0.92 77.06
Group3 SubGroup3B 29 -- -- -0.63 -- --
Group4 SubGroup1A 27 22.67 9.80 -0.70 0.90 70.73
Group4 SubGroup1B 27 23.67 77.27 0.00 0.93 77.70
Group5 SubGroup2A 10 -- -- 0.38 0.93 77.77
Group5 SubGroup2B 10 -- -- -0.89 0.89 9.60
Group5 SubGroup2C 10 79.70 9.77 -0.56 -- --
Group5 SubGroup2D 10 -- -- -0.79 0.99 72.03
Group5 SubGroup2E 14 29.20 70.92 0.00 0.92 70.89
Group6 SubGroup3A 25 23.78 70.59 0.00 0.92 77.06
Group6 SubGroup3B 29 -- -- -0.63 -- --
Group7 SubGroup1A 27 22.67 9.80 -0.70 0.90 70.73
Group7 SubGroup1B 27 23.67 77.27 0.00 0.93 77.70
Group8 SubGroup2A 10 -- -- 0.38 0.93 77.77
Group8 SubGroup2B 10 -- -- -0.89 0.89 9.60
Group8 SubGroup2C 10 79.70 9.77 -0.56 -- --
Group8 SubGroup2D 10 -- -- -0.79 0.99 72.03
Group8 SubGroup2E 14 29.20 70.92 0.00 0.92 70.89
Group9 SubGroup3A 25 23.78 70.59 0.00 0.92 77.06
Group9 SubGroup3B 29 -- -- -0.63 -- --
Group10 SubGroup1A 27 22.67 9.80 -0.70 0.90 70.73
Group10 SubGroup1B 27 23.67 77.27 0.00 0.93 77.70
Group11 SubGroup2A 10 -- -- 0.38 0.93 77.77
Group11 SubGroup2B 10 -- -- -0.89 0.89 9.60
Group12 SubGroup2C 10 79.70 9.77 -0.56 -- --
Group12 SubGroup2D 10 -- -- -0.79 0.99 72.03
Group12 SubGroup2E 14 29.20 70.92 0.00 0.92 70.89
Group13 SubGroup3A 25 23.78 70.59 0.00 0.92 77.06
Group13 SubGroup3B 29 -- -- -0.63 -- --
Total Total 54 45.03 33.00 99.04 43.33 12.01
;
run;

proc format;
value fmt
.='--';
run;;


data have;
 set temp1;
 if Groups='Total' then Groups='  '||Groups;
run;

ods rtf file='~/sasout/KsSharp.rtf' style=journal bodytitle;
proc report data=have nowd  
style(header)={background=verylightgray fontstyle=roman fontweight=bold just=c borderbottomcolor=black borderbottomwidth=2}
style(report)={rules=cols outputwidth=100% } 
style(lines)={just=l bordertopcolor=black bordertopwidth=2 borderbottomcolor=black borderbottomwidth=2 background=grayee fontweight=bold};
define groups/group noprint;
define SubGroups/group style={just=r } style(header)={just=c} 'Groups';
define nCount/sum style={just=c} format=fmt.;
define var1-var5/sum style={just=c}  format=fmt.;

compute before groups;
if strip(Groups)='Total' then len=0;
 else len=20;
line groups $varying20. len;
endcomp;
run;
ods rtf close;

Many Thanks

Jianmin  

[Ksharp]

It is a piece of cake for sas.

data Temp1;
length Groups SubGroups $ 20;
input Groups $ SubGroups $ (nCount Var1 Var2 Var3 Var4 Var5) (?? :best.);
datalines;
Group1 SubGroup1A 27 22.67 9.80 -0.70 0.90 70.73
Group1 SubGroup1B 27 23.67 77.27 0.00 0.93 77.70
Group2 SubGroup2A 10 -- -- 0.38 0.93 77.77
Group2 SubGroup2B 10 -- -- -0.89 0.89 9.60
Group2 SubGroup2C 10 79.70 9.77 -0.56 -- --
Group2 SubGroup2D 10 -- -- -0.79 0.99 72.03
Group2 SubGroup2E 14 29.20 70.92 0.00 0.92 70.89
Group3 SubGroup3A 25 23.78 70.59 0.00 0.92 77.06
Group3 SubGroup3B 29 -- -- -0.63 -- --
Group4 SubGroup1A 27 22.67 9.80 -0.70 0.90 70.73
Group4 SubGroup1B 27 23.67 77.27 0.00 0.93 77.70
Group5 SubGroup2A 10 -- -- 0.38 0.93 77.77
Group5 SubGroup2B 10 -- -- -0.89 0.89 9.60
Group5 SubGroup2C 10 79.70 9.77 -0.56 -- --
Group5 SubGroup2D 10 -- -- -0.79 0.99 72.03
Group5 SubGroup2E 14 29.20 70.92 0.00 0.92 70.89
Group6 SubGroup3A 25 23.78 70.59 0.00 0.92 77.06
Group6 SubGroup3B 29 -- -- -0.63 -- --
Group7 SubGroup1A 27 22.67 9.80 -0.70 0.90 70.73
Group7 SubGroup1B 27 23.67 77.27 0.00 0.93 77.70
Group8 SubGroup2A 10 -- -- 0.38 0.93 77.77
Group8 SubGroup2B 10 -- -- -0.89 0.89 9.60
Group8 SubGroup2C 10 79.70 9.77 -0.56 -- --
Group8 SubGroup2D 10 -- -- -0.79 0.99 72.03
Group8 SubGroup2E 14 29.20 70.92 0.00 0.92 70.89
Group9 SubGroup3A 25 23.78 70.59 0.00 0.92 77.06
Group9 SubGroup3B 29 -- -- -0.63 -- --
Group10 SubGroup1A 27 22.67 9.80 -0.70 0.90 70.73
Group10 SubGroup1B 27 23.67 77.27 0.00 0.93 77.70
Group11 SubGroup2A 10 -- -- 0.38 0.93 77.77
Group11 SubGroup2B 10 -- -- -0.89 0.89 9.60
Group12 SubGroup2C 10 79.70 9.77 -0.56 -- --
Group12 SubGroup2D 10 -- -- -0.79 0.99 72.03
Group12 SubGroup2E 14 29.20 70.92 0.00 0.92 70.89
Group13 SubGroup3A 25 23.78 70.59 0.00 0.92 77.06
Group13 SubGroup3B 29 -- -- -0.63 -- --
Total Total 54 45.03 33.00 99.04 43.33 12.01
;
run;

proc format;
value fmt
.='--';
run;;


data have label;
 set temp1;
 if Groups='Total' then output label;
  else output have;
run;
proc transpose data=label out=label1;
var _all_;
run;
proc sql noprint;
select catt(_NAME_,"='",col1,"'") into :label separated by ' '
 from label1;
quit;
proc datasets library=work nolist nodetails;
modify have;
label &label. ;
quit;


title;
ods rtf file='c:\temp\temp.rtf' style=journal bodytitle;
proc report data=have nowd  spanrows
style(header)={background=verylightgray fontstyle=roman fontweight=bold just=c borderbottomcolor=black borderbottomwidth=2}
style(report)={rules=cols outputwidth=100% } 
style(lines)={just=l bordertopcolor=black bordertopwidth=2 borderbottomcolor=black borderbottomwidth=2 background=grayee foreground=black fontweight=bold};
columns ('Groups' Groups) ('SubGroups' SubGroups) ('nCount' nCount)
('Var1' Var1) ('Var2' Var2) ('Var3' Var3) ('Var4' Var4) ('Var5' Var5);

define groups/group noprint;
define SubGroups/group style={just=r } style(header)={just=c} ;
define nCount/sum style={just=c} format=fmt.;
define var1-var5/sum style={just=c}  format=fmt.;

compute before groups;
line groups $100.;
endcomp;
run;
ods rtf close;

[Jianmin]

Hi @Ksharp , 

It's very clever to use the label statement to modify the dataset. I'm still dizzy to get around this concept, but I'll continue to study your code.  It's still a good piece of cake, but one thing you didn't do is that to get the group follows the data.   For example, the Group6 now is on two different pages, can we have Group6 also on the top of page 2?  

Many Thanks

Jianmin Long

[Ksharp]

It is a piece of cake for sas.

data Temp1;
length Groups SubGroups $ 20;
input Groups $ SubGroups $ (nCount Var1 Var2 Var3 Var4 Var5) (?? :best.);
datalines;
Group1 SubGroup1A 27 22.67 9.80 -0.70 0.90 70.73
Group1 SubGroup1B 27 23.67 77.27 0.00 0.93 77.70
Group2 SubGroup2A 10 -- -- 0.38 0.93 77.77
Group2 SubGroup2B 10 -- -- -0.89 0.89 9.60
Group2 SubGroup2C 10 79.70 9.77 -0.56 -- --
Group2 SubGroup2D 10 -- -- -0.79 0.99 72.03
Group2 SubGroup2E 14 29.20 70.92 0.00 0.92 70.89
Group3 SubGroup3A 25 23.78 70.59 0.00 0.92 77.06
Group3 SubGroup3B 29 -- -- -0.63 -- --
Group4 SubGroup1A 27 22.67 9.80 -0.70 0.90 70.73
Group4 SubGroup1B 27 23.67 77.27 0.00 0.93 77.70
Group5 SubGroup2A 10 -- -- 0.38 0.93 77.77
Group5 SubGroup2B 10 -- -- -0.89 0.89 9.60
Group5 SubGroup2C 10 79.70 9.77 -0.56 -- --
Group5 SubGroup2D 10 -- -- -0.79 0.99 72.03
Group5 SubGroup2E 14 29.20 70.92 0.00 0.92 70.89
Group6 SubGroup3A 25 23.78 70.59 0.00 0.92 77.06
Group6 SubGroup3B 29 -- -- -0.63 -- --
Group7 SubGroup1A 27 22.67 9.80 -0.70 0.90 70.73
Group7 SubGroup1B 27 23.67 77.27 0.00 0.93 77.70
Group8 SubGroup2A 10 -- -- 0.38 0.93 77.77
Group8 SubGroup2B 10 -- -- -0.89 0.89 9.60
Group8 SubGroup2C 10 79.70 9.77 -0.56 -- --
Group8 SubGroup2D 10 -- -- -0.79 0.99 72.03
Group8 SubGroup2E 14 29.20 70.92 0.00 0.92 70.89
Group9 SubGroup3A 25 23.78 70.59 0.00 0.92 77.06
Group9 SubGroup3B 29 -- -- -0.63 -- --
Group10 SubGroup1A 27 22.67 9.80 -0.70 0.90 70.73
Group10 SubGroup1B 27 23.67 77.27 0.00 0.93 77.70
Group11 SubGroup2A 10 -- -- 0.38 0.93 77.77
Group11 SubGroup2B 10 -- -- -0.89 0.89 9.60
Group12 SubGroup2C 10 79.70 9.77 -0.56 -- --
Group12 SubGroup2D 10 -- -- -0.79 0.99 72.03
Group12 SubGroup2E 14 29.20 70.92 0.00 0.92 70.89
Group13 SubGroup3A 25 23.78 70.59 0.00 0.92 77.06
Group13 SubGroup3B 29 -- -- -0.63 -- --
Total Total 54 45.03 33.00 99.04 43.33 12.01
;
run;

proc format;
value fmt
.='--';
run;;


data have label;
 set temp1;
 if Groups='Total' then output label;
  else output have;
run;
proc transpose data=label out=label1;
var _all_;
run;
proc sql noprint;
select catt(_NAME_,"='",col1,"'") into :label separated by ' ' from label1;
select catx(" ","('",_NAME_,"'",_NAME_,")") into :header separated by ' ' from label1;

create table have2 as
select *,count(*) as n from have group by groups;
quit;
data have3;
 set have2;
 by groups;
 if first.groups then do;
   rows+(n+1);
   if rows>34 then do; /*assuming a page only could have 34 rows*/
     break+1;rows=n+1;
   end;
 end;
run;
proc datasets library=work nolist nodetails;
modify have3;
label &label. ;
quit;








title ;
ods rtf file='c:\temp\temp.rtf' style=journal bodytitle;
proc report data=have3 nowd  spanrows
style(header)={background=verylightgray fontstyle=roman fontweight=bold just=c borderbottomcolor=black borderbottomwidth=2}
style(report)={rules=cols outputwidth=100% } 
style(lines)={just=l bordertopcolor=black bordertopwidth=2 borderbottomcolor=black borderbottomwidth=2 background=grayee foreground=black fontweight=bold};
columns break &header. ;

define break/group noprint;
define groups/group noprint;
define SubGroups/group style={just=r } style(header)={just=c} ;
define nCount/sum style={just=c} format=fmt.;
define var1-var5/sum style={just=c}  format=fmt.;
break after break/page;
compute before groups;
line groups $100.;
endcomp;
run;
ods rtf close;

[Jianmin]

Very impressive @Ksharp , it looks like you can do anything in SAS, but I still have question for your later. 

Hi @Reeza , I have seen Arthur L. Carpenter's writings and all he talked about is to do a table with n rows and m columns.   The table @mcook asked was a type of table I learned in the early time of my career, and Microsoft had a solution for that in 1990's.  The idea of a table is that:  a header row followed with a few rows of m columns.  Nowadays from the beginning of SAS tables, we don't really see this type of distinction in SAS tables.  

@Ksharp 's solution was really clever, and I haven't seen it in other writings.  In @Ksharp 's previous program, he did it right to make first two rows as header rows, you can see this in Word for table property for row: Repeated as header row at the top of each page.  In @Ksharp 's most recent program, he didn't make Group as a header row, instead he used a page break.   

In Word, it's really easy to make as many header rows as you like, but I don't know how SAS handles this one.

The Word doesn't use page break, and there are a lot of wrongs about using page break in SAS.  @Ksharp 's program suffers a minor defect, you still can break the code with a minor change in the code: 

data Temp1;
length Groups SubGroups $ 200;
input Groups $ SubGroups $  (nCount Var1 Var2 Var3 Var4 Var5) (?? :best.);
datalines;
Group1 SubGroup1A-SubGroup1A-SubGroup1A-SubGroup1A-SubGroup1A 27 22.67 9.80 -0.70 0.90 70.73
Group1 SubGroup1B-SubGroup1B-SubGroup1B-SubGroup1B-SubGroup1B 27 23.67 77.27 0.00 0.93 77.70
....

define SubGroups/group style={just=r width=0.5in} style(header)={just=c} ;

And other SPANROWS output heavily uses vertical space control methods, the resulting output suffers a lot of defects, and sometimes it is not even a Word document.  For example you can't do any editing, for example make a font larger, and hidden text in a row...

Many thanks for @Ksharp 's effort, and let me know it you know how to make Group as a header row.  

Jianmin Long

[mcook]

@Ksharp @Jianmin 

KSharp, thank you,  Using your code i was able to eventually get my output to look correct.  Though what is the purpose of the following data step? 

data have;
 set temp1;
 if Groups='Total' then Groups='  '||Groups;
run;

why the empty space before 'Total'?

Also,  why no column statement?

if i try to put in a column statement to change the order of the variables,  

for example

Column Groups nCount Var3 Var2 Var5 Var4 Var1;

It outputs a table with the correct ordering , but it drops the Groups column.  so i had to order the variables in a proc sql step prior to proc report.  

[Ksharp]

"why the empty space before 'Total'?"
I want make "Total" row in the first place . sas would order it by character . since blank is before any of A-Z in ASIIC code, so "Total" would be in the first row.

"Also, why no column statement?"
Yes. you could define COLUMN statement to order variables by
Column Groups SubGroup nCount Var3 Var2 Var5 Var4 Var1;
I didn't write it due to save the code.


"but it drops the Groups column."
Yes. Because I used NOPRINT option.
define groups/group noprint;