BookmarkSubscribeRSS Feed
CurtisER
Obsidian | Level 7

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

8 REPLIES 8
Cynthia_sas
SAS Super FREQ
Hi:
Why do you have an ODS PDF statement in the middle of your code?
cynthia
CurtisER
Obsidian | Level 7

@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.

Cynthia_sas
SAS Super FREQ

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

CurtisER
Obsidian | Level 7
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.
Cynthia_sas
SAS Super FREQ

Hi:

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

cynthia

with_split.png

Ksharp
Super User
try cellheight=


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


CurtisER
Obsidian | Level 7

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.

Cynthia_sas
SAS Super FREQ

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 4076 views
  • 0 likes
  • 3 in conversation