Can't wait to send this "Juletip" - so here you are
Did you know that it's possible to create quite powerfull Excel-files using SAS. In the below example I have tried some of the functionalities.
First I create a sample-data, based on the wellknown SASHELP.CLASS, and then I use a simple ODS EXCEL statement to create a Sheet with the content of this table; Just to show that it is possible to use formulas, I have the height shown as both digits, and as bars using a REPT-funktion in Excel.
Then I create a new Sheet using the new PROC MSCHART procedure, to show that it is possible to create true Excel charts from SAS.
Finally I use the tableEditor-tagset to create a JavaScript that automatically generates an Excel Pivot-table based on the first Sheet
Try it out, and you might be surprised of the funtionality, and might be inspired to try more by yourself.
Read more about tableEditor-tagset here: http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/
Happy holiday.
%let Path = C:\Temp; /* Path for results */
%let File = Test; /* Name of both XLSX and JS file */
/* Don't automatically open results when generated */
ods noresults;
/* Create sample data based on SASHELP.CLASS */
/* Just to have more columns to deal with and see how formulas can be used */
data sample;
set sashelp.class;
select (sex);
when ("F") do; Teacher = "Ms. Sophie Johnson"; Sport = "Handball"; end;
when ("M") do; Teacher = "Mr. John Scmidt"; Sport = "Soccer"; end;
otherwise;
end;
Formula = cats('=REPT("|", F',put(_N_+1,3.-L),')');
Height = round(Height*2.54);
run;
/* Create the excelfile with data */
ods excel file="&path.\&file..xlsx"
style=HTMLBlue
options (absolute_row_height='20'
absolute_column_width='20,10,10,10,10,10,30'
sheet_name='Data' /* This name is used in the pivot-definition below */
frozen_headers='on');
proc print data = sample noobs label;
var teacher sport sex age name height;
var formula / style={foreground=lightblue font_face='playbill'}
style(header)=Header;
label formula = "Height";
run;
ods excel options(sheet_name='Picture' frozen_headers='off');
proc sql;
create table sample1 as
select age, avg(height) as height format=comma8.
from sample
group by 1;
quit;
title "Average height by Age";
proc mschart data=sample1 category=age width=4in position="$C$1";
vcolumn height;
run;
title;
ods excel close;
/* Get template for tableEditor from support, and save in ODS path */
/* This could be done only once, by setting up ods path to a permanent library */
Filename tpl url "http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/tableeditor.tpl";
ods path(prepend) work.templates(update);
%include tpl;
/* Use tagset tableEditor, to create JavaScript which generates the Pivot-table */
ods tagsets.tableeditor file="&path.\&file..js"
/* \ (backslash) converted to \\ because of JavaScript */
options(update_target="%sysfunc(tranwrd(&path,\,\\))\\&file..xlsx"
default_file ="&path.\&file..xlsx"
output_type="script"
sheet_name="Data" /* Sheet-name from Excel */
pivot_sheet_name="Pivot" /* Sheet-name for Pivot-table */
pivotpage="Teacher,Sport"
pivotrow="Age,Name"
pivotcol="Sex"
pivotdata="Height"
pivotdata_stats="average"
pivotdata_fmt="#,##0"
pivot_subtotal="no"
doc="help" /* Notice the documentation in the SAS-log */
/* Could be omitted in production */
);
/* Dummy-code for generating the JavaScript */
data _null_;
file print;
put "test";
run;
ods tagsets.tableeditor close;
/* Open the JavaScript-file, which generates the Pivot-table */
/* If running this code in Enterprise Guide, your administrator must set ALLOWXCMD
or you can get System Command Task for Enterprise Guide from support.sas.com */
x "&path.\&file..js";
Attached the generated Excel-file
... View more
