topic Re: PROC Report - Exporting with ODS EXCEL changes format of text in SAS Enterprise Guide

PROC Report - Exporting with ODS EXCEL changes format of text

elsfy — Tue, 10 May 2022 15:56:11 GMT

Hi,

This is a problem i already wrote but couldn't find an answer so i am trying to be more clear on what i want.

I did a proc report adding headers above columns. The HTML output shown in SAS Entreprise Guide gives me this :

When exporting it with ODS EXCEL, Excel does whatever it wants and displays it like this :

While i want an output looking like this :

Here is part of my code :

ods excel file = "" 
    options(sheet_interval='none' embedded_titles='yes') ; 

ods escapechar = '^'  ;
options missing = 0 orientation=landscape center ; 

proc report data = a split = '-' 
                                style(header)={background=white borderwidth=1 bordercolor=black width=150 color=black just=c textalign=c} 
                                style(report)={borderwidth=1 bordercolor=black just=c}
                                style(column)= {borderwidth=1 bordercolor=black just=r color=black tagattr='format:###,###,###,###,###0'}
                                style(summary)= [just=c textalign=c];
;

column ( '^{style[color=red]Total}'  ('Category (before change/extension) - Performing time'
        var1 var2 var3 );

define var1 / group; 
define var2 / analysis sum ; 
define var3 / analysis sum ; 

run ;

Re: PROC Report - Exporting with ODS EXCEL changes format of text

Ksharp — Wed, 11 May 2022 03:43:00 GMT

Adding these three styles .

ods excel file = "c:\temp\temp.xlsx" 
    options(sheet_interval='none' embedded_titles='yes') ; 

ods escapechar = '^'  ;
options missing = 0 orientation=landscape center ; 

proc report data =sashelp.class nowd split = '-' 
                                style(header)={tagattr="wrap:no" vjust=m asis=on background=white borderwidth=1 bordercolor=black width=150 color=black just=c textalign=c} 
                                style(report)={borderwidth=1 bordercolor=black just=c}
                                style(column)= {borderwidth=1 bordercolor=black just=r color=black tagattr='format:###,###,###,###,###0'}
                                style(summary)= [just=c textalign=c];
;

column ( '^{style[color=red]Total}'  ('Category (before change/extension) - Performing time'
        sex weight height ));

define sex / group; 
define weight / analysis sum ; 
define height / analysis sum ; 

run ; 

ods excel close;

Re: PROC Report - Exporting with ODS EXCEL changes format of text

Ksharp — Wed, 11 May 2022 03:44:03 GMT

tagattr="wrap:no" vjust=m asis=on

Re: PROC Report - Exporting with ODS EXCEL changes format of text

elsfy — Wed, 11 May 2022 08:01:22 GMT

Thank you, really close to what i want. Is it possible to put 'Performing time' under 'Category(before change/extension)' ?

Re: PROC Report - Exporting with ODS EXCEL changes format of text

Ksharp — Wed, 11 May 2022 09:55:53 GMT

Ha. That is really uneasy.

ods excel file = "c:\temp\temp.xlsx" 
    options(sheet_interval='none' embedded_titles='yes') ; 

ods escapechar = '^'  ;
options missing = 0 orientation=landscape center ; 

proc report data =sashelp.class nowd split = '-' 
                                style(header)={tagattr="wrap:no" background=white 
bordertopwidth=1 bordercolor=black 
borderrightwidth=1 borderrightcolor=black 
color=black just=c textalign=c} 
                                style(report)={borderwidth=1 bordercolor=black just=c}
                                style(column)= {borderwidth=1 bordercolor=black just=r color=black tagattr='format:###,###,###,###,###0'}
                                style(summary)= [just=c textalign=c];
;

column ( '^{style[color=red]Total}'  
       ('^{style[borderwidth=1 bordercolor=white] Category (before change/extension)' 
       ( '^{style[borderwidth=1 bordercolor=white] Performingtime'
        sex weight height )));

define sex / group; 
define weight / analysis sum ; 
define height / analysis sum ; 
run ; 

ods excel close;

Re: PROC Report - Exporting with ODS EXCEL changes format of text

Ksharp — Fri, 20 May 2022 13:55:56 GMT

I just learn this option : flow="tables"

ods excel file = "c:\temp\temp.xlsx" 
    options(sheet_interval='none' embedded_titles='yes' flow="tables") ; 

ods escapechar = '^'  ;
options missing = 0 orientation=landscape center ; 

proc report data =sashelp.class nowd split = '-' 
                                style(header)={tagattr="wrap:no" background=white 
bordertopwidth=1 bordercolor=black 
borderrightwidth=1 borderrightcolor=black 
color=black just=c textalign=c} 
                                style(report)={borderwidth=1 bordercolor=black just=c}
                                style(column)= {borderwidth=1 bordercolor=black just=r color=black tagattr='format:###,###,###,###,###0'}
                                style(summary)= [just=c textalign=c];
;

column ( '^{style[color=red]Total}'  
       ('^{style[borderwidth=1 bordercolor=white ] Category (before change/extension) -  Performingtime'
        sex weight height ) );

define sex / group ; 
define weight / analysis sum ; 
define height / analysis sum ; 
run ; 

ods excel close;