Proc report : Exporting to excel changes width/height of row/columns..

elsfy — Fri, 20 May 2022 14:30:56 GMT

Hi,

I couldn't find an appropriate solution to my problem and maybe i am confusing height/width options..

I am doing a proc report that i want to export to excel with ODS EXCEL.

Even if the results shown in the HTML output are fine, when exporting to excel, it's not giving the expected output.

I have headers, columns,... and some should have a different size than others in excel.

What i want to be displayed is something like this (due to confidentiality x corresponds to text and 999 999 to different numbers that can go from 0 to billions) :

If you pay attention, the columns don't have same size. I need the first 3 one (with text) to be longer than the ones with numbers. Plus, ROW 7 contains headers so i want the entire row to be bigger than cell with values.

I've tried many options, from playing with style(column)=[width...] to adding options to ODS EXCEL and here is the last thing that i did:


title1 color=blue bcolor=blue ''; 
title2 color=blue bcolor=blue '';
title3 color=blue bcolor=blue ''; 
 
ods excel file ="..xlsx" 
	options(embedded_titles='yes'  hidden_rows='4' flow="header,data,tables" blank_sheet="INFORMATION"
absolute_column_width='15,10,15,10,10,10,10,10' absolute_row_height='15' ) ;

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

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

column ('^{style[color=red]location xxxxxx}' var1 var2 var3 var4 var5 ...) ;

define var1 /  group width=132 ; 
define var2  / group style(column)=[width=2.5in]; 
define var3 / group ; 
define var4 / analysis sum ; 
define var5 / analysis sum ; 
.... /analysis sum ; 

run ;

What i get is this kind of output :

1) This the size i want for row values but not for headers in row 7 because we can't read the entire header. I think that in the ods excel instruction 'ABSOLUTE_ROW_HEIGHT = 15' is applied to the entire sheet.

2) I think that i am missing or misunderstanding some things : width, cellwidth , height options...

3) The best result that i would like is that excel adjust automatically each column and row to its content when exporting just like it is possible to do it so manually in excel.

Sorry if it's a bit confusing. I can't provide data or the entire code due to confidentiality.

Best,

Re: Proc report : Exporting to excel changes width/height of row/columns..

average_joe — Fri, 20 May 2022 21:05:41 GMT

This is an example of how I control the cell width when using ODS EXCEL:

define business_name / 'Business Name' style(column)=[cellwidth=1.25in];

Re: Proc report : Exporting to excel changes width/height of row/columns..

Ksharp — Sat, 21 May 2022 10:47:02 GMT


title1 color=blue bcolor=blue ''; 
title2 color=blue bcolor=blue '';
title3 color=blue bcolor=blue ''; 
ods escapechar= '^' ;
ods excel file='c:\temp\want.xlsx' options(sheet_name='class' embedded_titles='yes'  hidden_rows='4');
proc report data=sashelp.class nowd style(header)={cellheight=2cm};
column ('^{style[color=red]location xxxxxx}' name sex weight height) ;
define name/display style={cellwidth=2cm};
define sex/display style={cellwidth=2cm};
define weight/display style={cellwidth=1cm};
define height/display style={cellwidth=1cm};
run;
ods excel close;

topic Re: Proc report : Exporting to excel changes width/height of row/columns.. in SAS Programming

Proc report : Exporting to excel changes width/height of row/columns..

Re: Proc report : Exporting to excel changes width/height of row/columns..

Re: Proc report : Exporting to excel changes width/height of row/columns..