The SAS Output Delivery System and reporting techniques

MSOffice2K help

Reply
Contributor
Posts: 50

MSOffice2K help

Hello all,

I'm building some reports for excel and now i have a couple of questions.

1 - My report will need one image at the top for now i'm using preimage on the title. this image must be always in the same relative or absolute place, is possible to embed the image on the file?

2 - this reports always have charts, i try some devices but activex looks the one to chose. some advice about this point?

3 - how can i control my column size Smiley Sad i tried so many things that i found on the web and nothing. can someone help on this?

4 - how can i suppress the proc separator? (is putting a line between the procs) bad line Smiley Sad


to help i send a sample with the last 2 problems.

thanks in advance Smiley Happy




/* =========================== SAMPLE =================*/

%let path=d:\temp;

data a;
length col1-col3 $ 100;
label col1="Jan-09" col2="Feb-09" col3="Mar-09";
do i=1 to 10;
col1=ranuni(100);
col2=ranuni(100);
col3=ranuni(100);
output;
end;
run;

ods tagsets.MSOffice2K file="test.xls" path="&path." (url=none)
gpath="&path." (url=none)
style=styles.minimal;

proc report data=a nowd
style(report)={cellspacing=0 cellpadding=0 rules = all frame = void}
style(header)={foreground=#ffffff background=#808080 htmlstyle="mso-number-format:\@"}
style(column)={bordercolor=#000000 borderwidth=1px} ;

column col1-col3;
run;

proc report data=a nowd
style(report)={cellspacing=0 cellpadding=0 rules = all frame = void}
style(header)={foreground=#ffffff background=#808080 htmlstyle="mso-number-format:\@"}
style(column)={bordercolor=#000000 borderwidth=1px} ;

column col1-col3;

define col1-col3 / display
style(header)={htmlstyle="width:162pt"}
style(column)={htmlstyle="width:162pt"};
run;

ods tagsets.MSOffice2K close;
SAS Super FREQ
Posts: 8,743

Re: MSOffice2K help

Hi:
MSOFFICE2K is creating an HTML file that Excel can open. To answer your questions:
1) When you use an IMAGE in the preimage style attribute, ODS MSOFFICE2K builds an <IMG> tag. To the extent that Excel uses this tag, you can position the image. You would need to investigate in the Excel/Microsoft HTML doc how IMG tags are handled and whether absolute positioning with CSS style properties might be possible. If I use images in titles, however, I notice that Excel usually does not respect any height that I send from the SAS or ODS side. Perhaps Microsoft has an "mso-" style property to control row height.

2) Since ACTIVEX is Microsoft technology, this might be one good choice, however, ANYONE who is going to look at the HTML file that you create MUST have the SAS ACTIVEX controls installed LOCALLY. I'd recommend, instead the ACTXIMG device, which creates output that -looks like- ACTIVEX but without the interactivity and thus, without the need for the ACTIVEX controls.

3) If the standard CSS specification that you're using as the value for the HTMLSTYLE attribute do not work:
[pre]
htmlstyle="width:162pt"
[/pre]

Then you might investigate whether there is an "mso-width" just as you use the "mso-number-format" style property, you might find that a Microsoft specific width attribute would work better. (I don't know for sure whether there is one, but there is likely to be some equivalent ...I just don't know what the name of it would be.) However, I note that when I go into Excel and type some numbers into a spreadsheet and then make the columns wide and save the file as HTML , Microsoft writes this style= attribute into the resulting HTML file:
[pre]
style='mso-width-source:userset;mso-width-alt:8155;width:167pt'
[/pre]
...which implies to me that if SAS is out of the picture, Microsoft has some interesting ways to control width. For example, I don't know how the 167 pt converts to 8155 or what the mso-width-source means, but I do note that Microsoft put similar attributes into any HTML that it creates. So this bears some investigation in the Microsoft documentation.

4) To get rid of the horizontal rule between procedures, you will need to change the style template that use use with the output, as described in this Tech Support note: http://support.sas.com/kb/23/340.html
(You should find that this information applies to ODS MSOFFICE2K as well as ODS HTML).

cynthia

PS -- if you are going to use the subsequent code in a stored process, or as part of the SAS Enterprise Intelligence Platform, remember that not all client applications will use Microsoft specific HTML. In particular Web Report Studio and PowerPoint do not accept HTML results from a stored process.
Contributor
Posts: 50

Re: MSOffice2K help

Hi Smiley Happy

Thanks for the answers i will investigate more about some of the questions.

many thanks Smiley Happy
Ask a Question
Discussion stats
  • 2 replies
  • 278 views
  • 0 likes
  • 2 in conversation