Re: Sas Program Ods excel options

Tecla1

Good Afternoon, I'm trying to output files in excel format but I'm not able, the file have ever the same wrong format. I need font Arial and fixed row height. Tnks

proc template;
   define style mystyle;
   parent = styles.normal;
   Style data /
   font_face= 'Microsoft Arial'
   fontsize=10pt
   borderbottomcolor=black
   verticalign=middle;
   end;
run;

ods excel file="/sas_share/NAS_SDR_814/EX_824/Attivita/prova.xlsx" style=mystyle options(
   sheet_name="Dati"
    embedded_titles='yes'
	GRIDLINES= 'ON'
    autofilter='all'
	AUTOFIT_HEIGHT = "OFF"
    frozen_headers='yes'
    tab_color='blue'
	flow='tables'    /*comando per togliere a capo*/
);

Tom

You did not share any code that would actually write anything to the ODS EXCEL destination.

ballardw

You would need one or more procedures that write output such as Proc Print, Proc Report, a data step with the correct options, a modeling proc, something.

Then there would have to be an ODS EXCEL CLOSE; after the procedures so SAS knows when you are done writing to the file.

Ksharp

'Microsoft Arial' is not valid font name for sas , try 'Arial' :

proc template;
   define style mystyle;
   parent = styles.normal;
   Style data /
   font_face= 'Arial'
   fontsize=10pt
   borderbottomcolor=black
   ;
   end;
run;

ods excel file="c:\temp\prova.xlsx" style=mystyle options(
   sheet_name="Dati"
    embedded_titles='yes'
	GRIDLINES= 'ON'
	AUTOFIT_HEIGHT = "OFF"
    frozen_headers='yes'
    tab_color='blue'
	flow='tables'    /*comando per togliere a capo*/
);

proc report data=sashelp.class nowd;
run;
ods excel close;

Tecla1

Good morning Ksharp, thi is my "proc report", Is it possible to set a fixed height for Excel rows? I looked in SAS help but didn't find it.... Many thnks for your kindly help!

proc template;
   define style mystyle;
   parent = styles.normal;
   Style data /
   font_face= 'Arial'
   fontsize=10pt
   borderbottomcolor=black
   ;
   end;
run;

ods excel file="/OUTPUT/prova.xlsx" style=mystyle options(
	
   sheet_name="Dati"
    embedded_titles='yes'
	GRIDLINES= 'ON'
    autofilter='all'
	AUTOFIT_HEIGHT = "OFF"
    frozen_headers='yes'
    tab_color='blue'
	flow='tables'    
);

proc report data=new.&nome.  nowd
    style(report)=[frame=void rules=none width=50%]; 
	/*style(column)={tagattr='wraptext:no' width=100%};
	style(header)=[background=cx4472C4 foreground=white font_weight=bold];  
    define AGGIORNAMENTI / style(header)=[background=#009597 foreground=white font_weight=bold]; 
	
run;

ods excel close;

Tecla1

My output is only in 'Helvetica'.... I'm not able to find a solution.... In attached my output.

Ksharp

If your template is not working for you , you could try PROC REPORT 's style:

ods excel file="c:\temp\prova.xlsx"  options(
   sheet_name="Dati"
    embedded_titles='yes'
	GRIDLINES= 'ON'
	AUTOFIT_HEIGHT = "OFF"
    frozen_headers='yes'
    tab_color='blue'
	flow='tables'    /*comando per togliere a capo*/
);

proc report data=sashelp.class nowd;
define _all_/style(column)={font_face='Arial'};
run;
ods excel close;

Ksharp

proc template;
   define style mystyle;
   parent = styles.normal;
   Style data /
   font_face= 'Arial'
   fontsize=10pt
   borderbottomcolor=black
   ;
   end;
run;

ods excel file="c:\temp\prova.xlsx" style=mystyle options(
 Row_Heights='20,80,0,0,0,0,0'
   sheet_name="Dati"
    embedded_titles='yes'
	GRIDLINES= 'ON'
	AUTOFIT_HEIGHT = "OFF"
    frozen_headers='yes'
    tab_color='blue'
	flow='tables'    /*comando per togliere a capo*/
);

proc report data=sashelp.class nowd;
run;
ods excel close;

Tecla1

My output is ever in 'Helvetica...

Ksharp

I don't know what to say.

Even I used English version SAS to run code and also could get 'Arial' font.

Maybe your sas did not register this font ? Could you try other font ?

Using the following code to check what fonts your sas have? If you don't have it ,you could register it .

/*
https://communities.sas.com/t5/ODS-and-Base-Reporting/Arial-narrow-font-in-RTF/m-p/934392#M26626
*/

/*Check the fonts have been registered*/
proc registry startat="\CORE\PRINTING\FREETYPE\FONTS" list;
run;

/*Register a font*/
proc fontreg mode=all msglevel=verbose;
fontfile "c:\windows\fonts\arialn.ttf";
run;

proc template;
   define style mystyle;
   parent = styles.normal;
   Style data /
   font_face= 'Arial'
   fontsize=10pt
   borderbottomcolor=black
   ;
   end;
run;

ods excel file="c:\temp\prova.xlsx" style=mystyle options(
 Row_Heights='20,80,0,0,0,0,0'
   sheet_name="Dati"
    embedded_titles='yes'
	GRIDLINES= 'ON'
	AUTOFIT_HEIGHT = "OFF"
    frozen_headers='yes'
    tab_color='blue'
	flow='tables'    /*comando per togliere a capo*/
);

proc report data=sashelp.class nowd;
run;
ods excel close;

Tecla1

The output of registry:

<ttf> Arial Symbol]
[ Attributes]
Family=int:2
Format="TRUETYPE"
Scale=int:1
Sizes="6,7,8,9,10,11,(12,73,2)"
Spacing=int:8
Type=int:0
Width=int:2
[ Character Sets]
Apple Roman8=LINK:"\CORE\PRINTING\FREETYPE\CHARACTER SETS\1\0"
Unicode=LINK:"\CORE\PRINTING\FREETYPE\CHARACTER SETS\3\1"
[ Code]
[ 104]