Juletip #15 - SAS and advanced Excel

[OleSteen]

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

[MichelleHomes]

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. This is a strong contender!

Share your favourite with @GertNissen at https://www.linkedin.com/pulse/more-sas-tips-which-your-favorite-gert-nissen