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
@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.
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
Hi:
Well, it worked for me using this syntax. The heights were OK without doing anything special.
cynthia
try cellheight= style(header) = {fontfamily='Courier New' font_size = 8pt just = C cellheight=8cm}
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.