Proc report behavior in Add-in for Office

[SandSand]

Hello

I am writing a proc report in a stored process to be opened via Add-in for MS Office in excel.

However can't get the split= option to work properly in the AMO. It works fine in the output when using EG.

But when opened in EG the rowis not expanding to the desired three lines of row height, only two are shown. As if the blank line in the first row is discarded, and it seems that it is the first column that "sets the standard" so to speak.

Code example:

title1 "this is the report title";

proc report data=sashelp.class style(header)=[background=white] split='*';

column ( ' *line2*line3'( sex)) ( ( name))

('Stats super'

  ('Stats *sub *extra sub' age height weight )

);

define sex / group 'NAME' style(header)=[just=l];

define name / group 'SEX' style(header)=[just=l];

define age / analysis 'AGE';

define height / analysis 'HEIGHT';

define weight / analysis 'WEIGHT';

break after sex / summarize style=[font_weight=bold font_style=roman];

compute after sex;

sex = "";

name = "Total";

endcomp;

rbreak after / summarize style=[font_weight=bold font_style=roman];

compute after;

sex = "Total";

endcomp;

run;

[Cynthia_sas]

Hi:

  When I use a stored process and PROC REPORT, I do not use the simple split='*'  option to split headers. Instead, I either use ODS ESCAPECHAR to insert a new line, but sometimes not all the clients will "respect" the height of the cell. Or, I just use multiple parentheses. The code below represents 2 different methods and the 2 screenshots show how the stored process results were opened in Excel. I am only posting the 2 COLUMN statements here. Note that your code had NAME as the label for the SEX variable and vice versa. I didn't bother to correct your code.

  The issue that I see with method 1 is that the cellheight does not "grow" automatically because of the "newline" instruction. That is true whether I use a stored process or not. So I went to Method 2, which explicitly forces a separate line for every string. It's not exactly the same (because I put an extra line in and used the parentheses in a different order to get the strings above the first column, but not above the 2nd column.

  If you continue to have issues, then I would recommend that you work with Tech Support on this. But the bottom line is that the split='*' might not work as you expect and so you will have to change to one of the other techniques.

cynthia

Method 1:

Use ODS ESCAPECHAR

ods escapechar='^';

proc report...

column ( ' ^{newline 1}line2^{newline 1}line3'( sex)) ( ( name))

('Stats super'

  ('Stats ^{newline 1}sub ^{newline 1}extra sub' age height weight )

);

Method 2:

Use single strings in parentheses

proc report...

column (('line' (' line2'('line3' sex))) name)

('Stats super'   ('Stats' ('sub' ('extra sub' age height weight ))));

[SandSand]

Hi Cynthia

Thanks for the reply.

I have tested the solutions you proposed.

1. Yes it works if you want text in the first column header, which I don't. Inserting blanks gives me the same result as initially; the header row height does not change based on the 'super' headers.

I then tried to use some style formatting to change the color of the text in the first header column (would have been a nice hack). But style formatting apparently does not work in the Add-In.

2. This works. But it doesn't really look that good with the header cells divided like that.

I guess I will have to settle with option 2. or just live with they way it initially looked in the AMO.

Thank you.