Issues and Questions about Reports using ODSOUT Objects

FriedEgg — Fri, 20 Jan 2012 22:23:19 GMT

Hi,

I have a report that currently uses msoffice2k_x to output a multisheet workbook. There are a few issues I have.

1) If I try to use excelxp output instead or msoffice2k_x the XML contains no data when using the ODSOUT object. The structure is there, but the data is missing.

2) The file when opened in excel (using msoffice2k_x) has a few issues.

a. The spacing for the image isn't perfect, what can be done to improve this?

b. The second tab for the workbook reports and error, it gets recovered and the data is present, however the sheet name is missing ('Male')

3) If the below is ran through EG the Enterprise Guide with crash upon completion. The job runs fine and outputs are created, no crash occurs when using SAS interactivly through Linux.

4) The color styling applied through the BMI format in the GKPI Proc is not applied to the Label as would be expected.

I have created the following code as a example:

ods listing close;

filename tmp '/temp';

ods tagsets.msoffice2k_x path=tmp file='temp.html' newfile=output style=listing options(graph_height='74' graph_width='180');

ods escapechar='~';

goptions reset=all device=javaimg xpixels=180 ypixels=74;

proc format;

value $gndr

'F'='F - Female'

'M'='M - Male';

picture bmi (round fuzz=.09)

low - 18.4 = '00.0)' (prefix='~S={color=black}Underweight (')

18.5 - 24.9 = '00.0)' (prefix='~S={color=green}Normal (')

25 - 29.9 = '00.0)' (prefix='~S={color=orange}Overweight (')

30 - high = '00.0)' (prefix='~S={color=red}Obese (');

run;

proc sql;

create view class as

select * from sashelp.class order by sex;

quit;

data _null_;

if _n_=1 then do;

declare odsout o();

call execute('ods tagsets.msoffice2k_x close;');

call execute('ods html file="/temp/gkpi.html" gpath=tmp style=listing;');

call execute('proc gkpi mode=raised;');

end;

set class end=eof;

by sex;

if first.sex then do;

tot_ht=0;

tot_wt=0;

cnt=0;

o.table_start();

o.head_start();

o.row_start();

o.format_cell(data:'This is a spanned header',column_span:5,overrides:'font_weight=bold font_size=16pt just=c');

o.row_end();

o.row_start();

o.format_cell(data:'Name',overrides:'just=l fontweight=bold');

o.format_cell(data:'Age',overrides:'just=r fontweight=bold');

o.format_cell(data:'Height',overrides:'just=r fontweight=bold');

o.format_cell(data:'Weight',overrides:'just=r fontweight=bold');

o.format_cell(data:'BMI',overrides:'just=c fontweight=bold cellwidth=1.9in');

o.head_end();

end;

o.row_start();

tot_ht+height;

tot_wt+weight;

cnt+1;

if sex='M' then do;

o.format_cell(data:name,overrides:'just=l color=blue');

end;

else do;

o.format_cell(data:name,overrides:'just=l color=purple');

end;

o.format_cell(data:age,overrides:'just=r');

o.format_cell(data:height,overrides:'just=r');

o.format_cell(data:weight,overrides:'just=r');

bmi=(weight*703)/(height**2);

call execute('slider actual=' || put(bmi,5.2) || ' bounds=(0 18.5 25 30 40) / noavalue target=21.7 colors=(white yellow green red) name="' || strip(name) || '" label="' || put(bmi,bmi.) || '"; run;');

o.cell_start(overrides:'vjust=c just=c cellwidth=1.9in cellheight=.8in');

o.image(text:cats(name,'.png'));

o.cell_end();

o.row_end();

if last.sex then do;

o.row_start();

o.format_cell(data:'Average',column_span:2,overrides:'just=r fontweight=bold');

o.format_cell(data:floor(tot_ht/cnt),overrides:'just=r');

o.format_cell(data:floor(tot_wt/cnt),overrides:'just=r');

call execute('slider actual=' || put(bmi,5.2) || ' bounds=(0 18.5 25 30 40) / noavalue target=21.7 colors=(white yellow green red) name="average" label="' || put(bmi,bmi.) || '"; run;');

o.cell_start(overrides:'vjust=c just=c cellwidth=1.9in cellheight=.8in');

o.image(text:'average.png');

o.cell_end();

o.row_end();

o.body_end();

o.foot_start();

o.row_start();

o.format_cell(data:'This is a spanned footer',column_span:5,overrides:'just=c');

o.row_end();

o.row_start();

o.format_cell(data:'Here is some other information:',column_span:5,overrides:'just=c color=white background=black font_size=16pt');

o.row_end();

o.row_start();

o.format_cell(data:"~S={URL='http:\\www.sas.com'}I Made This With SAS",column_span:5);

o.row_end();

o.foot_end();

o.table_end();

end;

if eof then do;

call execute('quit;');

call execute('ods html close;');

stop;

end;

run;

ods tagsets.msoffice2k_x file='/temp/temp.xls'

options( worksheet_source="Female#temp.html,Male#temp1.html" );

ods tagsets.msoffice2k_x close;

I have attached a copy of the final output, as I see it, converted to XLS in Excel after opening the temp.xls file produced in the final step above (to avoid uploading the 19 image files, 2 html files and msoffice2k_x generated xls file).

Issues and Questions about Reports using ODSOUT Objects

Cynthia_sas — Sat, 21 Jan 2012 05:40:08 GMT

Hi:

A few clarifications...not about everything...but about a few things.

By Microsoft design, Spreadsheet ML 2003 does not allow the inclusion of images into the XML file. So your choice to use TAGSETS.MSOFFICE2_K is a good one.

ODS ESCAPECHAR syntax does NOT work with SAS/GRAPH. The only place it will work is in TITLES/FOOTNOTES and only if you use the NOGTITLE/NOGFOOTNOTE options to allow ODS and the destination to control the TITLES/FOOTNOTES. So I would not expect your attempt to use ODS ESCAPECHAR with the labels to work for PROC GKPI (or any SAS/GRAPH.

I have put images into MSOFFICE2K files and am generally not pleased with the image placement. For example, if I have a table and a graph, it is entirely possible for the graph to not be placed or spaced entirely correctly.

As for the dot or object syntax...I don't use it often, since it is still experimental, so I can't help you there.

You might want to try Tech Support.

cynthia

Issues and Questions about Reports using ODSOUT Objects

FriedEgg — Sat, 21 Jan 2012 05:55:56 GMT

Thanks Cynthia, it is good to know about the XML restriction of image links.

I really like the object syntax for ODS. I find it most handy when creating documents like client invoices or customized newsletters, etc. It works best with PDF output in my experience. The only template that has ever given me issues is TAGSETS.EXCELXP. Typicall for a rather straight forward report this this one I still find proc report to be a more simplistic and reliable solution, but I wanted to post an example I could throw together quickly.

As far as the EG crashing is concerned I do plan to contact tech support, it is not often I see a hard fault from this application.

topic Issues and Questions about Reports using ODSOUT Objects in ODS and Base Reporting

Issues and Questions about Reports using ODSOUT Objects

Issues and Questions about Reports using ODSOUT Objects

Issues and Questions about Reports using ODSOUT Objects