This tutorial explains how to send SAS results (output) to Excel.
Example 1 :
Create a new sheet for each unique value in the grouping variable (By Group)
Example 2 :
Define names of sheets manually
Example 1 :
Create a new sheet for each unique value in the grouping variable (By Group)
ods tagsets.excelxp file="C:\Users\Deepanshu\test.xls"
options(embedded_titles="yes"
autofilter="1-3"
frozen_headers="3"
frozen_rowheaders="1"
absolute_column_width="8.5,11,7,9,8,8"
autofit_height="yes"
sheet_interval="bygroup"sheet_label=" "
suppress_bylines="yes") style=normal;
proc print data=sashelp.shoes noobs;
title "Detail of Region #byval(region)";
by region;
run;
ods tagsets.excelxp close;
Create Multi-Sheet Excel File |
The SHEET_INTERVAL= option is used to define the interval in which to create new worksheets.
Example 2 :
Define names of sheets manually
ods tagsets.excelxp file='C:\Users\Deepanshu\Documents\multitable.xls' style=STATISTICAL
options(sheet_name='Summary' skip_space='1,0,0,0,1' EMBEDDED_TITLES='yes' sheet_interval='none');
Title " First File";
proc freq data = sashelp.class;
table sex;
run;
Title " Second File";
proc print data = sashelp.cars;
run;
ods tagsets.excelxp options(sheet_name='FREQ' skip_space='1,0,0,0,1' EMBEDDED_TITLES='yes' sheet_interval='none');
Title " Third File";
proc freq data = sashelp.cars;
table make;
run;
ods tagsets.excelxp close;
Example 3 :
Apply Custom Format of Excel
Important NoteApply Custom Format of Excel
data temp;
pct= 0.75;
number= -45;
run;
ods tagsets.excelxp file="C:\Users\Deepanshu\temp.xls";
proc print data=temp noobs;
var pct;
var number / style(data)={tagattr="format:$#,##0_);[Red]($#,##0)"};
format pct percent5.2;
run;
ods tagsets.excelxp close;
Excel's Custom Format via SAS |
ODS TAGSETS.EXCELXP does not support graphs (charts). From SAS 9.4, SAS added new ODS called ODS EXCEL that supports both graphs and tables.
ODS EXCEL
ods excel file="c:\test.xlsx"ODS Excel- PROC MSChart
options(start_at="B5“
tab_color="red"
absolute_row_height="15"
embedded_titles="yes");
ods text="Sales report for company X";
proc print data=sashelp.orsales;
title "Sample title showing new
features";
run;
ods excel close;
proc sql;
create table summary as
select(region), sum(sales) format=dollar14.2 as sales
from sashelp.shoes
group by region;
run;
quit;
ods excel file="c:\temp.xlsx";
title "Sales by Region";
proc mschart data=work.summary category=region width=4in position="$D$1";
where region in("Africa","Asia","Canada","Pacific","United States");
vcolumn sales;
run;
ods excel close;
Please provide the explanation also.
ReplyDeletedataset contains 10K+ records, could somebody help to modify the code to print properly.
ReplyDeleteods excel file="testing.xlsx" style=plateau
options(sheet_name='test' frozen_headers='3' embedded_titles='yes' autofilter='all' merge_titles_footnotes='yes');
proc print data=xx split="*" noobs ;
var client_id ;
title j=center height=16pt "xxx ";
format client_id ;
label client_id = 'Clients';
footnote ' ';
run;
ods excel close;