topic Re: How do I expand the height of one header row in tagsets.ExcelXP? in ODS and Base Reporting

How do I expand the height of one header row in tagsets.ExcelXP?

CurtisER — Thu, 01 Dec 2016 15:36:33 GMT

Hello,

I'm a little stumped. I have an issue with one particular row that was created with tagsets.ExcelXP in SAS 9.2. This row is collapsed and only a portion of the line is visible. How can I expand the height of this particular header row? This header row is created using the COLUMNS statement. I used the ^S for underlining for the first header. Should I use another ^S attribute to control the height of this particular row (second header)?

This is what it looks like (see row 4 where it shows "Oct. 2015 to" only):

This is the desired output in row 4 (it shows all 3 lines):

This is a code snippet:

   options nodate orientation = landscape pageno = 1
           topmargin = 0.4in bottommargin = 0.4in
           leftmargin = 0.4in rightmargin = 0.4in;

   ods listing close;
   ods escapechar = '^';

   title1 h=8pt j=l f='Courier New' "TABLE 9. Aggregate weekly hours and payrolls of production and nonsupervisory employees, seasonally adjusted (in thousands)";

   footnote h=8pt j=l f='Courier New';

   ods tagsets.ExcelXP file = "&ariesx.table9.xml" style=MONOSPACE
                       OPTIONS(EMBEDDED_TITLES = 'YES' EMBEDDED_FOOTNOTES = 'YES' EMBED_TITLES_ONCE = 'YES'
                               ASCII_DOTS = 'NO' AUTOFIT_HEIGHT = 'YES' SHEET_NAME = 'Table 9');

   proc report data = output_table_9 nowd ls = 200 list headskip headline style = monospace nocenter
               style(report) = {fontfamily='Courier New' font_size = 8pt}
               style(header) = {fontfamily='Courier New' font_size = 8pt just = C}
               style(column) = {fontfamily='Courier New' font_size = 8pt just = C};

      columns 
              ('^S={TEXTDECORATION=underline}Aggregate weekly hours^S={}' ind_name WHc ("Change from/&MMMYYp. to/&MMMYYc." OTMCc_wh OTMPCcwh)
                                                                                       ("Change from/&MMMYYp. to/&MMMYYc." OTYCc_wh OTYPCcwh));

      define ind_name / "/ Industry /" display left STYLE(COLUMN) = {JUST = L CELLWIDTH = 3in};
      define WHc / "Aggregate/weekly/hours" display format = 9. STYLE(COLUMN) = {JUST = C CELLWIDTH = 1in};
      define OTMCc_WH / "Number" display format = 8. STYLE(COLUMN) = {JUST = C CELLWIDTH = 1in};
      define OTMPCcWH / "Percent" display format = 5.1 STYLE(COLUMN) = {JUST = C CELLWIDTH = 1in};
      define OTYCc_WH / "Number" display format = 8. STYLE(COLUMN) = {JUST = C CELLWIDTH = 1in};
      define OTYPCcWH / "Percent" display format = 5.1 STYLE(COLUMN) = {JUST = C CELLWIDTH = 1in};
   run;

   ods pdf startpage = no;

   proc report data = output_table_9 nowd ls = 200 list headskip headline style = monospace nocenter
               style(report) = {fontfamily='Courier New' font_size = 8pt}
               style(header) = {fontfamily='Courier New' font_size = 8pt just = C}
               style(column) = {fontfamily='Courier New' font_size = 8pt just = C};

      columns 
              ('^S={TEXTDECORATION=underline}Aggregate weekly payrolls^S={}' ind_name PRc ("Change from/&MMMYYp. to/&MMMYYc." OTMCc_PR OTMPCcPR)
                                                                                          ("Change from/&MMMYYp. to/&MMMYYc." OTYCc_PR OTYPCcPR));

      define ind_name / "/ Industry /" display left STYLE(COLUMN) = {JUST = L CELLWIDTH = 3in};
      define PRc / "Aggregate/weekly/payrolls" display format = 9. STYLE(COLUMN) = {JUST = C CELLWIDTH = 1in};
      define OTMCc_PR / "Number" display format = 8. STYLE(COLUMN) = {JUST = C CELLWIDTH = 1in};
      define OTMPCcPR / "Percent" display format = 5.1 STYLE(COLUMN) = {JUST = C CELLWIDTH = 1in};
      define OTYCc_PR / "Number" display format = 8. STYLE(COLUMN) = {JUST = C CELLWIDTH = 1in};
      define OTYPCcPR / "Percent" display format = 5.1 STYLE(COLUMN) = {JUST = C CELLWIDTH = 1in};
   run;

   ods tagsets.ExcelXP close;
   ods listing;

   title;
   footnote;

I appreciate your assistance in advance.

Curtis

Re: How do I expand the height of one header row in tagsets.ExcelXP?

Cynthia_sas — Thu, 01 Dec 2016 17:35:28 GMT

Hi:
Why do you have an ODS PDF statement in the middle of your code?
cynthia

Re: How do I expand the height of one header row in tagsets.ExcelXP?

CurtisER — Thu, 01 Dec 2016 17:40:25 GMT

@Cynthia_sas wrote:
Hi:
Why do you have an ODS PDF statement in the middle of your code?
cynthia

I'll just say that's an oopsie. 🙂 Please ignore that statement. Good catch, though!

The PDF version worked as expected but the spreadsheet is what I am working on it.

Thanks.

Re: How do I expand the height of one header row in tagsets.ExcelXP?

Cynthia_sas — Thu, 01 Dec 2016 18:11:37 GMT

Hi:

... Thanks for the info. Options for PROC REPORT like HEADLINE, HEADSKIP, LS are not supported except for the LISTING destination which you have closed.

Debugging will be hard without some data to test with.

Also, your use of STYLE=MONOSPACE in the PROC REPORT statement, while it does not produce any error message is probably not doing anything for you in this code. the STYLE=MONOSPACE in the ODS TAGSETS.EXCELXP statement is the one that is being followed. I found that the font size seems to be 8pt Courier for the style=monospace, so I am not sure why you are respecifying the font size or the font family.

The simple test below worked for me.

cynthia

Re: How do I expand the height of one header row in tagsets.ExcelXP?

CurtisER — Thu, 01 Dec 2016 20:07:41 GMT

Thanks for the tip, Cynthia! The user wanted line breaks in "Change from/MMM YYYY to/MMM YYYY" where / is the line break so it's a 3-line header. I'll try your example and see if I can accomplish this.

Re: How do I expand the height of one header row in tagsets.ExcelXP?

Cynthia_sas — Thu, 01 Dec 2016 21:59:30 GMT

Hi:

Well, it worked for me using this syntax. The heights were OK without doing anything special.

cynthia

Re: How do I expand the height of one header row in tagsets.ExcelXP?

Ksharp — Fri, 02 Dec 2016 12:03:25 GMT

try cellheight=


style(header) = {fontfamily='Courier New' font_size = 8pt just = C   cellheight=8cm}

Re: How do I expand the height of one header row in tagsets.ExcelXP?

CurtisER — Fri, 02 Dec 2016 19:23:45 GMT

I tried AUTOFIT_HEIGHT = 'NO' and

columns
('^S={TEXTDECORATION=underline}Aggregate weekly hours^S={}' ind_name WHc ("^S={CELLHEIGHT=0.25IN CELLWIDTH = 0.5IN}Change from/&MMMYYp. to/&MMMYYc.^S={}" OTMCc_wh OTMPCcwh)
("^S={CELLHEIGHT=0.25IN CELLWIDTH = 0.5IN}Change from/&MMMYYp. to/&MMMYYc.^S={}" OTYCc_wh OTYPCcwh));

define ind_name / "Industry" display left STYLE(COLUMN) = {JUST = L CELLWIDTH = 5IN};

It produced the desired height as I wanted. However,

the CELLWIDTH for define IND_NAME isn't working and the width is smaller than the length of the value.

Re: How do I expand the height of one header row in tagsets.ExcelXP?

Cynthia_sas — Fri, 02 Dec 2016 21:07:40 GMT

Hi:

I found that I had some issues when I had a default set in the PROC REPORT statement, but once I got rid of that in favor of specifying a size on every DEFINE, then it worked for me-- in SAS 9.4 M3 without adjusting the height.

cynthia