topic Re: PROC PRINT - prevent column headings from spilling onto second row in SAS Procedures

PROC PRINT - prevent column headings from spilling onto second row

cbal324 — Mon, 27 Apr 2015 16:02:41 GMT

Hello,

I am wondering if anyone can provide guidance on an issue I have encountered when using PROC PRINT to print tables into an Excel format (via ODS). It pertains to the header rows. I am running SAS 9.2.

On occasion, some of my headers spill onto a second row, as observed when exporting to Excel, despite the fact that I am NOT specifying a split= character. As one example, I have some SAS output (exported to Excel) in which the label for the last column is split between two rows. For purposes of copying and pasting this data into a separate standard delivery template, it would be preferable for all labels to always fit within a single row without wrapping.

My PROC PRINT code is as follows - pretty basic.

proc print data = final label noobs;

format distpercentchange countpercentchange percentn10.1 count1 count2 comma10.;

var rankcirc mfrname distpercentchange count1 count2 countpercentchange;

label rankcirc = 'Rank'

mfrname = 'Manufacturer'

distpercentchange = "&period YOY % Change"

count1 = "&lastyear Offer Count"

count2 = "&thisyear Offer Count"

countpercentchange = "Offer Count YOY % Change";

run;

Are there any thoughts as to whether there are any options or alternative methods that I can consider using to force my headings onto a single row? Is there any other information that I can provide?

Thanks very much!

Chris

Re: PROC PRINT - prevent column headings from spilling onto second row

ballardw — Mon, 27 Apr 2015 16:55:25 GMT

How are you exporting to Excel? Tagsets.Excelxp or HTML and opening in Excel or something else?

The ods settings do have an impact on choices.

Re: PROC PRINT - prevent column headings from spilling onto second row

cbal324 — Mon, 27 Apr 2015 17:57:56 GMT

Thank you for the response! Good question - for this, I am simply using HTML and opening in Excel.

ods html file = "G:\path\filename.xls";

* proc print, etc.;

ods html close;

Re: PROC PRINT - prevent column headings from spilling onto second row

Tom — Mon, 27 Apr 2015 21:43:44 GMT

I can recreate your problem using SASHELP.CLASS.

proc print data=sashelp.class label noobs ;

label name='This label is longer than the column width';

run;

Why not just use PROC EXPORT to write an XLS (or better XLSX) file directly from the data instead of using PROC PRINT?

Or write a real (or at least closer to real) Excel file by using ODS tagsets.excelxp or the new ODS Excel .

Or use a proc like PROC REPORT that will let you specify the column width and the data format separately.

Re: PROC PRINT - prevent column headings from spilling onto second row

Cynthia_sas — Mon, 27 Apr 2015 22:56:18 GMT

Hi:

The main issue is that Excel will not like the column widths if you use ODS and will "pick" a general column width. And, I believe it does this with the EXPORT method too. I do have some other suggestions:

1) if you are creating output for Excel, I recommend using ODS MSOFFICE2K as the destination. Microsoft did not agree with the standard HTML 4.0 specification (which is what ODS HTML creates) and the ODS MSOFFICE2K destination creates the kind of HTML tags that conform to Microsoft HTML

2) You can explicitly change the width of the cells so that the header has more room:

proc print data = xxxxx.yyyyy label noobs

style(data)={width=2in};

var onevar twovar;

var redvar bluevar / style(data)={width=1.5in};

run;

proc report data = xxxxx.yyyyy nowd

style(column)={width=2in};

column onevar twovar redvar bluevar;

define redvar / style(column)={width=1.5in};

define bluevar / style(column)={width=1.5in};

run;

3) I have more about overriding Excel defaults in this paper:https://support.sas.com/resources/papers/proceedings11/266-2011.pdf (covers leading zeroes, column width, decimal points, percent signs, etc)

cynthia

Re: PROC PRINT - prevent column headings from spilling onto second row

cbal324 — Tue, 28 Apr 2015 16:31:14 GMT

All -

Thank you for your assistance on this! I found that in my particular case, explicitly changing the cell widths within PROC PRINT still did not result in the desired output. Even after shrinking the font size for good measure, one of my headings insisted on wrapping onto two rows despite having an abundance of space to fit onto one.

It turns out that for my specific needs, PROC REPORT provides a little more versatility with regard to customizing my output. After simply converting my PROC PRINT step into a PROC REPORT step, the headings appear exactly as desired while the data itself is shown exactly as it was before. So my very quick and easy solution will be to use PROC REPORT for my exports to Excel.

proc report nowd data = final;

column rankcirc mfrname distpercentchange count1 count2 countpercentchange;

define rankcirc / "Rank";

define mfrname / "Manufacturer";

define distpercentchange / format=percentn10.1 "&period YOY % Change";

define count1 / format=comma10. "&lastyear Offer Count";

define count2 / format=comma10. "&thisyear Offer Count";

define countpercentchange / format=percentn10.1 "Offer Count YOY % Change";

run;

Thanks again for all of the great suggestions!

Chris