PROC REPORT - Spanning Header loses spaces and looks wrapped in excel output

[Zatere]

Hi All,

Could you please help on the following.

I’m using PROC REPORT to create a report with spanning headers and exporting it to Excel via ODS EXCEL.

One of my spanning headers is:

Product TRXS Value & Volume (all series)

However, in the Excel output, the header text appears with missing spaces and looks like it is wrapped:

Product TRXSValue & Volume(all series)

I am using the following code:

data HAVE;
    length 
        ID 8
        TYPE $10
        SALE_DATE 8
        Customer_Name $20
        South 8
        North 8
        West 8
        East 8
        HAS_COMPLETED $1
        Is_From_The_Same_Area $1
        TOTAL_Product_VAL_26 8
        TOTAL_Product_VOL_26 8
        ProductA_VAL_26 8
        ProductA_VOL_26 8
        ProductB_VAL_26 8
        ProductB_VOL_26 8
        ProductC_VAL_26 8
        ProductC_VOL_26 8
        ProductD_VAL_26 8
        ProductD_VOL_26 8
        ProductE_VAL_26 8
        ProductE_VOL_26 8
        Avg_VAL_Product_26 8
        PRODUCT_UNIQ_AREA_12Months 8
        Product_12mnths_VAL 8
        PRODUCT_PRIME_12_MONTHS 8
        PRIME_Total_SERIES_Aggregated 8
    ;

    format SALE_DATE date9.;
    informat SALE_DATE date9.;

    infile datalines dlm='|' dsd truncover;

    input
        ID
        TYPE :$10.
        SALE_DATE :date9.
        Customer_Name :$20.
        South North East West
        HAS_COMPLETED :$1.
        Is_From_The_Same_Area :$1.
        TOTAL_Product_VAL_26 TOTAL_Product_VOL_26
        ProductA_VAL_26 ProductA_VOL_26 
		ProductB_VAL_26 ProductB_VOL_26
        ProductC_VAL_26 ProductC_VOL_26
		ProductD_VAL_26 ProductD_VOL_26
        ProductE_VAL_26 ProductE_VOL_26 
		Avg_VAL_Product_26
        PRODUCT_UNIQ_AREA_12Months
        Product_12mnths_VAL
        PRODUCT_PRIME_12_MONTHS
        PRIME_Total_SERIES_Aggregated
    ;

datalines;
1|Existing|20-Nov-86|AAAAAAAAAA|0|0|0|0|N|N|2610|9|0|0|0|0|2610|9|2610|9|0|0|290|1|6245|0|6245
2|Existing|11-Feb-94|BBBBBBBBBB|0|0|0|0|N|N|180|1|0|0|0|0|180|1|180|1|0|0|180|1|180|0|180
3|Existing|20-Jun-01|CCCCCCCCCC|0|0|0|0|N|N|500|2|0|0|0|0|500|2|500|2|0|0|250|3|540|0|540
4|New|01-Jan-65|DDDDDDDDD|0|0|0|0|N|N|1820|5|0|0|0|0|1820|5|1820|5|0|0|364|3|3720|0|3720
5|Existing|14-Sep-99|EEEEEEEEEE|0|0|0|0|N|N|35|1|0|0|0|0|35|1|35|1|0|0|35|1|35|0|35
6|Existing|30-Jun-00|FFFFFFFFFF|0|0|0|0|N|N|2000|6|0|0|1000|1|1000|5|1000|5|0|0|200|2|5360|0|5360
7|Existing|04-Sep-00|GGGGGGGGGG|0|0|0|0|N|N|150|2|0|0|0|0|150|2|150|2|0|0|75|1|270|0|270
8|Existing|28-May-02|HHHHHHHHHH|0|0|0|0|N|N|2095|4|0|0|0|0|2095|4|2095|4|0|0|523.75|1|3890|0|3890
9|Existing|15-Apr-04|IIIIIIIIII|0|0|0|0|N|N|9000|4|0|0|0|0|9000|4|9000|4|0|0|2250|2|9000|0|9000
10|Existing|29-Dec-05|KKKKKKKKKK|0|0|0|0|N|N|100|2|0|0|0|0|100|2|50|1|50|1|50|1|100|0|100
;
run;


proc format;
value $hdfmt
'Other Info & Details' = 'cx002060'
'Product TRXS Value & Volume (all series)' = 'cx7030A0'
'Region / Area' = 'cxFF0000'
'Customer Information' = 'cx00B050'
;
run;

ODS EXCEL 
FILE = FILEOUT
;

proc report data=HAVE nowd split='|'
	style(header)={background=$hdfmt. foreground=white 
	tagattr="wrap:no" 
	vjust=middle}
;

    column 
		("Customer Information"
            ID SALE_DATE Customer_Name TYPE					
        )

		("Region / Area"
			South North East West
        )
		
		("Product TRXS Value & Volume (all series)"
            Product_12mnths_VAL
			TOTAL_Product_VAL_26 TOTAL_Product_VOL_26
			ProductA_VAL_26 ProductA_VOL_26
			ProductB_VAL_26 ProductB_VOL_26 
			ProductD_VAL_26 ProductD_VOL_26 
			Avg_VAL_Product_26
			ProductC_VAL_26 ProductC_VOL_26 
			ProductE_VAL_26 ProductE_VOL_26 
        )

		("Other Info & Details"
			PRODUCT_UNIQ_AREA_12Months Is_From_The_Same_Area HAS_COMPLETED 
			PRODUCT_PRIME_12_MONTHS PRIME_Total_SERIES_Aggregated 
        )

  ;

    define ID / display "Cust ID|(ID)" 
        style(header)=[background=cx00B050 foreground=white tagattr="wrap:yes" vjust=middle]
        style(column)=[just=left cellwidth=0.80in];

    define SALE_DATE / display "Sale|Start Date" 
        style(header)=[background=cx00B050 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[just=left cellwidth=0.80in];

    define customer_name / display "Customer|Name" 
        style(header)=[background=cx00B050 foreground=white tagattr="wrap:yes" vjust=middle]
        style(column)=[just=center cellwidth=1.00in];

    define TYPE / display "Cust Type"
        style(header)=[background=cx00B050 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[just=left cellwidth=1.00in];
   
    define South / display "South|Area"
        style(header)=[background=cxFF0000 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[just=center cellwidth=0.50in];

    define North / display "North|Area" 
        style(header)=[background=cxFF0000 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[just=center cellwidth=0.55in];

    define West / display "West|Area" 
        style(header)=[background=cxFF0000 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[just=center cellwidth=0.75in];

    define East / display "East|Area" 
        style(header)=[background=cxFF0000 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[just=center cellwidth=0.62in];

	define Product_12mnths_VAL / "Overall|Product Val|last 12|months" 
	    style(header)=[background=cx7030A0 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[tagattr='format:£#,##0.00' just=center cellwidth=1.00in];

	define TOTAL_Product_VAL_26 / "Overall Product|Val|YTD"
		style(header)=[background=cx7030A0 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[tagattr='format:£#,##0.00' just=center cellwidth=1.00in];

	define TOTAL_Product_VOL_26 / "Overall Product|Vol|YTD"  
		style(header)=[background=cx7030A0 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[just=center cellwidth=1.00in];

	define ProductA_VAL_26 / "ProductA Val|YTD" 
	 	style(header)=[background=cx7030A0 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[tagattr='format:£#,##0.00' just=center cellwidth=0.9in];

	define ProductA_VOL_26 / "ProductA Vol|YTD" 
	 	style(header)=[background=cx7030A0 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[just=center cellwidth=0.75in];

	define ProductB_VAL_26 / "ProductB Val|YTD"  
	 	style(header)=[background=cx7030A0 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[tagattr='format:£#,##0.00' just=center cellwidth=0.75in];

	define ProductB_VOL_26 / "ProductB Vol|YTD" 
	 	style(header)=[background=cx7030A0 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[just=center cellwidth=0.8in];

	define ProductD_VAL_26 / "ProductD|Val|YTD" 
	 	style(header)=[background=cx7030A0 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[tagattr='format:£#,##0.00' just=center cellwidth=0.8in];

	define ProductD_VOL_26 / "ProductD|Vol|YTD" 
	 	style(header)=[background=cx7030A0 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[just=center cellwidth=0.8in];

	define Avg_VAL_Product_26 / "Product Overall|Avg Val|Dep" 
	 	style(header)=[background=cx7030A0 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[tagattr='format:£#,##0.00' just=center cellwidth=0.8in];

	define ProductC_VAL_26 / "ProductC|Val|YTD" 
	 	style(header)=[background=cx7030A0 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[tagattr='format:£#,##0.00' just=center cellwidth=0.8in];

	define ProductC_VOL_26 / "ProductC|Val|YTD" 
	 	style(header)=[background=cx7030A0 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[just=center cellwidth=0.8in];

	define ProductE_VAL_26 / "ProductE|Val|YTD"
		style(header)=[background=cx7030A0 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[tagattr='format:£#,##0.00' just=center cellwidth=0.8in];

	define ProductE_VOL_26 / "ProductE|Vol|YTD" 
		style(header)=[background=cx7030A0 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[just=center cellwidth=0.8in];

	define PRODUCT_UNIQ_AREA_12Months / "Product Unique Areas|Last 12 Mths"  
	    style(header)=[background=cx002060 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[just=center cellwidth=0.85in];

	define Is_From_The_Same_Area / "Same Area|As of Day YTD"  
	    style(header)=[background=cx002060 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[just=center cellwidth=0.85in];

	define HAS_COMPLETED / "LOGIN|Completed"  
	    style(header)=[background=cx002060 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[just=center cellwidth=0.85in];

	define PRODUCT_PRIME_12_MONTHS / "Product Overall prime last|12 months" 
	    style(header)=[background=cx002060 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[tagattr='format:£#,##0.00' just=center cellwidth=0.85in];

	define PRIME_Total_SERIES_Aggregated / "Prime & Overall Series|(last 12 mths)" width=12
	    style(header)=[background=cx002060 foreground=white tagattr="wrap:yes" vjust=middle]
		style(column)=[tagattr='format:£#,##0.00' just=center cellwidth=0.90in]; 

run;


ODS EXCEL CLOSE;

When exported in excel, it gives me the following output:

I have tried different things but nothing has worked.

Is there any way to make it display without missing spaces like the below?

Thanks in advance.

[Tom]

Make sure the spaces in your program file are spaces and not nulls or other invisible characters.

Try a simpler example.

filename _dataout temp;
ods excel file=_dataout;
proc report data=sashelp.class;
 column ('Header 1' ('Header 2' name sex) ('Header 3' age height weight) ) ;
run;
ods excel close;

%let _dataout_name=example.xlsx;
%let _dataout_mime_type=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet ;

[Tom]

SAS is mistakenly replacing some of the spaces with linefeeds to cause that wrapping.

Open a support ticket with SAS support.  It seems to be triggering some type of bug in their code (or possible in Excel's code).

It seems to be related to the number of columns that heading is spanning because if I split into two blocks and give each of them the same heading it prints fine.

   (
    "Product TRXS Value & Volume (all series)" 

      Product_12mnths_VAL
      TOTAL_Product_VAL_26 TOTAL_Product_VOL_26
      ProductA_VAL_26 ProductA_VOL_26
      ProductB_VAL_26 ProductB_VOL_26 
      ProductD_VAL_26 ProductD_VOL_26 
        )
        
        (
    "Product TRXS Value & Volume (all series)" 

      Avg_VAL_Product_26
      ProductC_VAL_26 ProductC_VOL_26 
      ProductE_VAL_26 ProductE_VOL_26 
        )    

[data_null__]

---

@Tom wrote:

SAS is mistakenly replacing some of the spaces with linefeeds to cause that wrapping.

Is the problem related to the FLOW option?  

(FLOW=<"cell-names" | "DATA" | "HEADERS" | "ROWHEADERS" | "TABLES" | "TEXT" | "NONE">)
specifies that a designated Worksheet area enables Wrap Text and disables newline character insertion. Excel wraps the text to the column width.
cell-names
enables Wrap Text for a single cell such as “A12” or a cell range such as “C1:E4”.
DATA
enables Wrap Text for table data cells.
HEADERS
enables Wrap Text in table column headers.
Alias	HEADER
ROWHEADERS
enables Wrap Text for table row headers.
Alias	ROWHEADER
TABLES
enables Wrap Text for all parts of a table: HEADER, ROWHEADER, and DATA.
Alias	TABLE
TEXT
makes ODS TEXT output work like titles, footnotes, PROC titles, and BY lines. The text is written into multiple merged cells and Wrap Text is enabled.
NONE
specifies that text wrapping is not enabled. This option can turn off the FLOW= option.
Default	NONE
Examples	 ods excel file="c:\file-path\test.xlsx" options(flow="A2");
ods excel file="c:\file-path\test.xlsx" options(flow="tables");
ods excel file="c:\file-path\test.xlsx" options(flow="text");

I tested it and OPTIONS(FLOW="HEADERS") fixed it.

 

[Tom]

That is interesting because telling it something like 

flow="A1:C9999" 

did not work.

But of course that was because I flipped rows and columns in my head.  Should have been something like A1:ZZ3 

[Ksharp · Accepted solution]

Use style 'asis=on' to keep blank as it was:

proc report data=HAVE nowd split='|'
	style(header)={background=$hdfmt. foreground=white  asis=on
	tagattr="wrap:no" 
	vjust=middle}
;

[Ksharp]

Style 'nobreakspace=on' also can solve this problem :

proc report data=HAVE nowd split='|'
	style(header)={background=$hdfmt. foreground=white  nobreakspace=on
	tagattr="wrap:no" 
	vjust=middle}
;

[Zatere]

I will try nobreakspace=on as well