The SAS Output Delivery System and reporting techniques

excelxp: proc report header cell height problem

Reply
Regular Contributor
Posts: 196

excelxp: proc report header cell height problem

Hi,

I'm currently using ods excelxp with proc report.

Here's the code I'm using for the header:

style(header)=[font_weight=bold background=cxDCE6F1 cellheight=1.5in];

font_weight and background are working fine, but cellheight is not.

Is there a different option I can use to change the header's cell height?

Thank you.

SAS Super FREQ
Posts: 8,744

Re: excelxp: proc report header cell height problem

Hi:

   Since you did not show all your code, including ODS TAGSETS.EXCELXP suboptions, it is hard to comment on your question. However, if you are seeing issues with row heights and TAGSETS.EXCELXP, you might want to open a track with Tech Support -- because the method you use may depend on the version of SAS and the version of TAGSETS.EXCELXP -and- the version of Excel.

cynthia

These both worked for me (SAS 9.3, Windows 7, Excel 2013, ExcelXP Tagset version v1.127) .. .one example using a very, very long text string and the other using using HEIGHT explicitly.

ods tagsets.excelxp file='c:\temp\report1.xml' style=analysis

    options(doc='Help' Autofit_height='yes');

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

  style(header)=[font_weight=bold background=cxDCE6F1 ];

  define name / '1 This is the /Very Very /Very/Very very/ Long Name';

run;

ods tagsets.excelxp close;

  

ods tagsets.excelxp file='c:\temp\report2.xml' style=analysis

    options(doc='Help');

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

  style(header)=[font_weight=bold background=cxDCE6F1 height=1.5in];

  define name / '2 This is the /Very Very /Very/Very very/ Long Name';

run;

ods tagsets.excelxp close;

Regular Contributor
Posts: 196

Re: excelxp: proc report header cell height problem

I'm only interested in changing the height of the first row. All other rows are good.

I'm using SAS 9.3 with Excel 2010.

ods tagsets.excelxp file="C:\Performance.xml"
style=journal
options(frozen_headers='1'
             autofit_height='yes'
             autofilter='all'
             absolute_column_width='23,28,8,9,10')
;

proc report data=rate2 nowd contents="Claims" out=test
style(header)=[font_weight=bold background=cxDCE6F1 height=1.5in /*cellheight=1.5in*/];
options missing='0';

column bu rate1;

define bu / order=data;

define rate1 / 'rate';
ods tagsets.excelxp close;

SAS Super FREQ
Posts: 8,744

Re: excelxp: proc report header cell height problem

Hi,

  I'm not sure what Excel will do if you don't have a long text string anyplace. It might ignore the height. As I explained above, both of the code examples I posted work for me. If neither of them work for you, that would be something to work on with Tech Support.

cynthia

Regular Contributor
Posts: 196

Re: excelxp: proc report header cell height problem

Height isn't working for me, but split='/' is good enough for creating a taller 1st row.

I would give your reply a "Helpful Answer", but I can't find the button.

SAS Super FREQ
Posts: 8,744

Re: excelxp: proc report header cell height problem

Hi:

  Are you still using SAS 9.1.3 by any chance? If so, then CELLHEIGHT was the attribute back then. HEIGHT, as an alias for CELLHEIGHT was introduced in 9.2, so try that if you are 9.1.3 . But, as you can see by my first example, HEIGHT/CELLHEIGHT isn't really necessary if you use the SPLIT option and autofit_height='yes'. Now, if SPLIT doesn't work for you, then you would really want to work with Tech Support.

cynthia

Super Contributor
Posts: 265

Re: excelxp: proc report header cell height problem

Hi ,

i am facing challange with "line" height. its been ver lapping as i have 2-3 lines.

another problem is , if i m using ~{super 1} to add superscript 1, then that header is not being bold.

is there any option to overcome this problem?

i have tried height / cellhieght, but didnt work. i m using autofit option , but its working for header fine but not for line.

what i used is:

above i used:

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

               fontsize=1 just=l  ]

compute after ;

line "(*ESC*)S={ textalign= left Font_weight=bold}xyz i need it in bold (*ESC*)S={}";

line "(*ESC*)S={indent=1in height=30in}its very long string about 2-3 line. (*ESC*)S={}";

line "(*ESC*)S={indent=1in } its again long (*ESC*)S={}";

line "(*ESC*)S={indent=1in } its last line(*ESC*)S={}";

   endcomp;

can u please suggest.

SAS Super FREQ
Posts: 8,744

Re: excelxp: proc report header cell height problem

Hi:

  It's not a good idea to add a new post to a year old posting. If you want to refer to an older post, you should start a new post and put a link to the older post that you want to reference. As I told the person a year ago...it is not helpful to post only part of your code and not show the ODS DESTINATION statements. One might infer that you were asking about TAGSETS.EXCELXP, but since the earlier post specifically referred to using suboptions for TAGSETS.EXCELXP and you don't mention anything other than "using autofit" about using suboptions, it is hard to make any kind of constructive comment.

  But overall, the only general comment I have is that I do not believe that INDENT= is respected by Excel when you use TAGSETS.EXCELXP. It sticks in my mind that INDENT= is an RTF/PDF style attribute. You'd have to check in the doc or with Tech Support. Next, HEIGHT=30in seems an unreasonable height to want in a workbook. Third, it looks like you're mixing original form of ESCAPECHAR syntax with some of the newer style attributes and you are not clear which version of SAS you are using.

  My suggestion is that you work with Tech Support. You can show them ALL your code, including ALL your ODS statements, you can send them your data and you can tell them what version of SAS and Excel you're using and they can help you with a resolution.

cynthia

Super Contributor
Posts: 265

Re: excelxp: proc report header cell height problem

My sincere apology for wrong approach for solution.

and where can i get list of all related "options"...

Thanks

about full code : code is below , still facing line overlapping problem

I have only two point: 1 - column header with superscript is not being bold 2- line overlapping.

ods noresults;

ODS listing close;

ods tagsets.excelxp file="I:\anuj\temp folder\tables.xls" ;

ods tagsets.excelxp options(sheet_name="Table A States"  Autofit_height='yes');

Proc report data= region_data nowd headline split="#"

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

style(header)=[color=black

               fontsize =3 font_weight=bold fontfamily = 'Times New Roman']

style(column)=[color=black

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

style(lines)=[color=black

              fontfamily='times new roman' just=l fontsize=1 height=10pt ]

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

                fontfamily=helvetica fontsize=3 textalign=r];

column ("National/Regional sales and comm (Levels),."

(areaname

("sale~{super 1}#(Thousands)"  (sale&Lastyrb sale&prevmob sale&currmob))

("M-O-M#Changes#(Thousands)" (mom_sale))

("sale1~{Super 2}#(Thousands)"(sale1&Lastyrb sale1&prevmob sale1&currmob))

("M-O-M # Changes#(Thousands)"(mom_sale1))));

define areaname/ display "Location~{super 3}" style(Header)=[just=center cellwidth=1.7 in] style(column)=[just=left];

define sale&lastyrb/display "&lastyrn" format=comma8.1 style(Header)=[just=center textalign=center cellwidth=.7 in] style(column)=[just=center];

define sale&prevmob/display "&prevmon" format=comma8.1 style(Header)=[just=center cellwidth=.7 in] style(column)=[just=center];

define sale&currmob/display "&currmon" format=comma8.1 style(Header)=[just=center cellwidth=.7 in] style(column)=[just=center];

define mom_sale/ display "&CURRMON.-&PREVMON." format=comma8.1 style(Header)=[just=center cellwidth=1 in] style(column)=[just=center];

define sale1&lastyrb/display "&lastyrn" format=comma8.1 style(Header)=[just=center cellwidth=.7 in] style(column)=[just=center];

define sale1&prevmob/display "&prevmon" format=comma8.1 style(Header)=[just=center cellwidth=.7 in] style(column)=[just=center];

define sale1&currmob/display "&currmon" format=comma8.1 style(Header)=[just=center cellwidth=.7 in] style(column)=[just=center];

define mom_sale1/ display "&CURRMON.-&PREVMON." format=comma8.1 style(Header)=[just=center cellwidth=1 in] style(column)=[just=center];

compute areaname;

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

      call define(_row_, "style",

                  "style=[backgroundcolor=cxaeadd9

                          fontfamily=helvetica

                          fontweight=bold]");

endcomp;

compute after ;

line "~S={ textalign= left height=10pt Font_weight=bold fontfamily='times new roman' fontsize=1}long string ~S={}";

line "~S={ fontfamily='times new roman' fontsize=1 height=10pt }1. long string   ~S={}";

line "~S={ fontfamily='times new roman' fontsize=1 height=10pt }2. long string ~S={}";

line "~S={fontfamily='times new roman' fontsize=1 height=10pt }3.long string ~S={}";

line "~S={ fontfamily='times new roman' fontsize=1height=10pt }long string ~S={}";

endcomp;

run;

quit;

ods tagsets.excelxp close;

ODS listing;

can u please help me to how to reach tech support

thanks

Super Contributor
Posts: 394

Re: excelxp: proc report header cell height problem

You can report a problem to Tech Support by clicking the link "submit a problem report" at the bottom of this page.

Super User
Posts: 3,115

Re: excelxp: proc report header cell height problem

A couple of thoughts:

1) Make sure you are using the latest version of the Excel XP tagset V1.130. Check your SAS log to confirm which version you are using. If you don't have the latest version download it here:

http://support.sas.com/rnd/base/ods/odsmarkup/index.html

2) Try different values of the row_height_fudge option (the default value is 4). I found this useful reducing cell height for long text lines but it could be used to add height space as well.

Ask a Question
Discussion stats
  • 10 replies
  • 3462 views
  • 0 likes
  • 5 in conversation