The SAS Output Delivery System and reporting techniques

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

Reply
Occasional Contributor
Posts: 17

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

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):

tab9_collapsed.JPG

 

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

tab9_correct.JPG

 

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

SAS Super FREQ
Posts: 8,720

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

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

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


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.  Smiley Happy  Please ignore that statement.  Good catch, though!

 

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

 

Thanks.

SAS Super FREQ
Posts: 8,720

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

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

 

spanning_text_fit.png

Occasional Contributor
Posts: 17

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

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.
SAS Super FREQ
Posts: 8,720

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

Hi:

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

cynthia

with_split.png

Grand Advisor
Posts: 9,578

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

try cellheight=


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


Occasional Contributor
Posts: 17

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

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.

SAS Super FREQ
Posts: 8,720

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

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

this_approach_worked_for_me.png

Ask a Question
Discussion stats
  • 8 replies
  • 320 views
  • 0 likes
  • 3 in conversation