Blogs

Blogs

Discovering the power of ODS ExcelXP tagsets

2
By

The ODS ExcelXP tagset is pretty powerful stuff. Hidding columns, changing the print layout to landscape, updating the spreadsheet names, defining the default column width and row height, etc - it's all available using options within the ExcelXP setup.

So the best thing to do is grab some documentation. Fortunetly, there is a doc provided directly in your SAS log file by adding the option (doc="help") within the tagset statement. An example of using this option directly from Enterprise Guide or Base SAS is below:

ods tagsets.ExcelXP path="c:\temp" file="exporttest.xls" style=sasweb options(doc="help);

Are there any Stored Process Creators in the house?

There are a couple notes for you.
1. You must estabilish these options BEFORE the %stpbegin; statement.
For example by including these %lets:
%let _odsdest = tagsets.ExcelXP;
%let _odsoptions = options=(doc="help");

2. If you are expecting the user to receive this file via the Web Interface, you will still need to include stpsrv_header options - also before the %stpbegin; statement.

data _null_;
rc=stpsrv_header('Content-type', 'application/vnd.ms-excel');
rc=stpsrv_header('Content-disposition', 'attachment; filename=Excel_Export.xls');
run;

Additional Information on the ExcelXP Tagsets for ODS:
Tags business intelligence ods Stored Processes
Share

About Author

Angela Hall

Senior Technical Architect

Angela offers tips on using the SAS Business Intelligence solutions. She manages a team of SAS Fraud Framework implementers within the SAS Solutions On-Demand organization. Angela also has co-written two books, 'Building BI using SAS, Content Development Examples' & 'The 50 Keys to Learning SAS Stored Processes'.

2 Comments

  1. Bharat Thakkar on

    Angela,
    I currently use the ods tagsets.ExcelXP to generate standard reports with multiple worksheets and it works wonderfully. My challange is that I have 6 static worksheets that are stored seperatly as template and I need append these 6 worksheets and the worksheets generated by the ods.tagset.ExcelXP. Is there SAS solution or do I need to some VB code? Please advise.

    Thanks
    Bharat Thakkar
    Bharat.Thakkar@va.gov

  2. Electronic signature, E-sign on

    Very informative post! I am using ExcelXP tagset to create a multi sheet Excel file. I have a dataset that contains records with user IDs and I want to create separate worksheet for each user record and use the username as the worksheet name.

Back to Top