Interact, learn and grow

Join Now
This is an open group. Sign in and click the "Join Group" button to become a group member and start posting.
SAS Employee
Posts: 19
Juletip #15 - SAS and advanced Excel

Can't wait to send this "Juletip" - so here you are Smiley Very Happy


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:


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;
  Formula = cats('=REPT("|", F',put(_N_+1,3.-L),')');
	Height = round(Height*2.54);

/* Create the excelfile with data */
ods excel file="&path.\&file..xlsx" 
    options (absolute_row_height='20' 
    sheet_name='Data' /* This name is used in the pivot-definition below */

proc print data = sample noobs label;
var teacher sport sex age name height;
var formula / style={foreground=lightblue font_face='playbill'} 
label formula = "Height";

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;
title "Average height by Age";
proc mschart data=sample1 category=age width=4in position="$C$1";
vcolumn height;

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 "";
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 */
	  default_file ="&path.\&file..xlsx"
    sheet_name="Data" /* Sheet-name from Excel */
    pivot_sheet_name="Pivot" /* Sheet-name for Pivot-table */
	  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";

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 */
x "&path.\&file..js";

Attached the generated Excel-file

Trusted Advisor
Posts: 1,367
Re: Juletip #15 - SAS and advanced Excel

A very cool tip! I wasn't aware of all the features of the tableEditor-tagset. Thanks @OleSteen, you've just made it harder for me to pick my favourite Juletip. Smiley Wink This is a strong contender!


Share your favourite with @GertNissen at