topic Re: Problem in line height of excel output in ODS and Base Reporting

Problem in line height of excel output

Aman4SAS — Wed, 06 Aug 2014 12:30:24 GMT

Hi i m looking to incresing height of line, becaz my lines are long and its been wrap and overlap by next line. {In case of line font size also not working, if i will remove"(*ESC*)S" and i will face issue to make first line bold}

-Issue:

-Not able to change fontsize for lines

-Not able to set height of lines accordingly

-Headers where i used super scripts that are not being bold.

My code is below:

proc report data = TableA nowd headline headskip split= "*"

style(report)=[cellspacing=2 borderwidth=1 bordercolor=blue]

style(header)=[color=black

fontsize =3 textalign=l fontfamily = 'Times New Roman' fontweight=bold]

style(column)=[color=black

fontfamily='Times New Roman' fontsize=2 just=center]

style(lines)=[color=Black backgroundcolor=grey

fontstyle=italic fontweight=bold fontsize=1 just =l]

style(summary)=[color=cx3e3d73 backgroundcolor=cxaeadd9

fontfamily=helvetica fontsize=3 textalign=r];

column ("Heading line" (Area Totalsale Mom sdrates));

DEFINE AREA / " * * LOCATION" style(Header)=[just=center cellwidth=2 in] style(column)=[just=l];

define totalsale/ "Total ads~{super 1}*(Thousands)*&currmon" style(Header)=[just=center cellwidth=1 in] style(column)=[tagattr='format:#,##0.0'];

define mom/ "M-O-M*Change*&currmon - &prevmon" style(Header)=[just=center cellwidth=1.2 in] style(column)=[tagattr='format:#,##0.0'];

Define sdrates/"Supply/*Demand Rates~{super 2}*&msta" style(Header)=[just=center cellwidth=1.4 in] style(column)=[tagattr='format:##.00'];

compute Area;

if Area in ("NORTHEAST","SOUTH","MIDWEST","WEST") and _break_=' ' then

call define(_row_, "style",

"style=[backgroundcolor=yellow

fontfamily='Times New Roman'

fontweight=bold]");

if Area = "United States" and _break_=' ' then

call define(_row_, "style",

"style=[backgroundcolor=cxaeadd9

fontfamily='Times New Roman'

fontweight=bold]");

endcomp;

compute after ;

line "(*ESC*)S={ textalign= left Font_weight=bold font_size=1 fontfamily='times new roman'}line 1 (*ESC*)S={}";

line "(*ESC*)S={ font_size=1 fontfamily='times new roman'}line 2 (*ESC*)S={}";

line "(*ESC*)S={ font_size=1 fontfamily='times new roman'}Line 3 (*ESC*)S={}";

endcomp;

run;

Re: Problem in line height of excel output

Cynthia_sas — Fri, 08 Aug 2014 03:11:24 GMT

Hi:

I don't think you can use STYLE overrides with TAGSETS.EXCELXP in a LINE statement. You will have to check with Tech Support for sure. Here are some possibly relevant notes:

http://support.sas.com/kb/36/442.html

http://support.sas.com/kb/34/828.html

I used some test code much simpler than yours and the style overrides for the LINE statements worked in PDF output and MSOFFICE2K HTML output, but a STYLE override would not work with TAGSETS.EXCELXP. If the line statement used an ESCAPECHAR style override of any kind, the file would not get created correctly. The good news is that if you create an HTML file (using MSOFFICE2K) and open the HTML file with Excel, the change of style on each LINE statement does seem to be respected (probably because Excel doesn't have as much finicky-ness with HTML as with the XML).

See the attached code, the HTML file created by MSOFFICE2K will open in Excel and show the different colors on the line, and seems to wrap OK and the height seems to be OK. I didn't bother with the other color coding since the focus was on the LINE statement.

Cynthia

options noquotelenmax topmargin=.25in bottommargin=.25in rightmargin=.25in leftmargin=.25in;

%let l1 = abcdefg hijklmnop qrstuv wxyz;

%let l2 = Twas brillig and the slithy toves did gyre and gimble in the wabe;

%let currmon = Aug;

%let prevmon = July;

%let msta = xyz;

ods _all_ close;

ods pdf file='c:\temp\show_line.pdf';

ods msoffice2k file='c:\temp\show_line_mso2k.xls' style=analysis;

ods escapechar='~';

title 'Note different style for each line in PDF and HTML (open with Excel)';

proc report data=sashelp.class nowd split='*'

style(header)={font_weight=bold}

style(lines)=notecontent{just=l font_weight=bold font_size=8pt fontfamily='Times New Roman'} ;

column ("Averages Report" age sex height weight);

DEFINE age / group " * * LOCATION"

style(Header)=[just=center width=2in font_weight=bold] style(column)=[just=l width=2in];

define sex/ group "Total ads~{super 1}*(Thousands)*&currmon"

style(Header)=[just=center width=1.5in font_weight=bold] style(column)=[ width=1.5in ];

define height/ mean "M-O-M*Change*&currmon - &prevmon"

style(Header)=[just=center width=1.5in font_weight=bold] style(column)=[ width=1.5in tagattr='format:#,##0.0'];

Define weight/mean "Supply/*Demand Rates~{super 2}*&msta"

style(Header)=[just=center width=1.5in font_weight=bold] style(column)=[ width=1.5in tagattr='format:##.00'];

rbreak after / summarize;

compute after ;

line "~{style[color=purple fontfamily='Courier New']Line 1 &l1 &l1 &l1 (END1)}";

line "~{style[color=red font_style=roman fontfamily='Helvetica']Line 2 &l2 &l2 &l2 (END2)}";

endcomp;

run;

ods _all_ close;

Re: Problem in line height of excel output

Aman4SAS — Fri, 08 Aug 2014 06:59:59 GMT

Hi,

Thanks a lot to clear my doubts .

But when i use msoffice2k i faces issues when i try to work for multiple sheet .

Will msoffice2k work for multiple sheet?

as i tried option but its not taking that.

ods msoffice2k file='I:\anuj\temp folder\show_line_mso2k.xls' style=analysis options(sheet_name="Table A States" Autofit_height='yes');

as i heard that msoffice2k doesnt support multiple sheet.

Thanks.

Re: Problem in line height of excel output

Cynthia_sas — Fri, 08 Aug 2014 15:02:01 GMT

Hi:

You are correct -- ODS MSOFFICE2K does not support multiple sheets, but you can use ODS MSOFFICE2K_X to get multiple sheets -- and the up side is that you can also do graphs with these type of HTML files that Excel can open.

Do not assume that TAGSETS.EXCELXP suboptions will also work for ODS MSOFFICE2K -- they could have different suboptions and most likely do, because TAGSETS.EXCELXP produces an XML file and MSOFFICE2K produces an HTML file. You can ALWAYS find out what suboptions work with a destination by:

ods msoffice2k file='I:\anuj\temp folder\show_line_mso2k.xls' style=analysis options(doc='Help');

and compare it to

ods tagsets.msoffice2k_x file='I:\anuj\temp folder\show_line_mso2kx.xls' style=analysis options(doc='Help');

and compare it to

ods tagsets.excelxp file='I:\anuj\temp folder\show_line_xp.xml' style=analysis options(doc='Help');

I am pretty sure you will find that autofit_height is an Excelxp suboption that may not be available in the other destinations. I found for example, when I ran my code, that the height did autoadjust for me in the resulting file. Did you run my code and see the results?

cynthia