Since its 4.2 release, SAS Enterprise Guide has been able to import Microsoft Excel 2007 and 2010 spreadsheet files (usually encountered as .XLSX files). But while SAS Enterprise Guide can export XLS files (which are compatible with all versions of Microsoft Excel), it does not have the ability to export to a native .XLSX file.
Customers see this as a limitation. The Microsoft Excel 2007 format allows for more rows and columns than traditional XLS files can support, and SAS Enterprise Guide does not offer a point-and-click method to create output results in this new format.
In SAS 9.2, you can use PROC EXPORT or the EXCEL LIBNAME engine to write data in this new format, but using PROC EXPORT requires the customer to write a custom SAS program, and both PROC EXPORT and the EXCEL LIBNAME engine require you to license and install SAS/ACCESS to PC Files.
New Custom Task: Export Data to Microsoft Excel 2007/2010
To help customers to work around this limitation, SAS R&D has created a custom task that focuses on one thing: export your SAS data to a Microsoft Excel 2007 (.XLSX) file. Here is an example screen shot of the task:
The task supports the following features/options:
- Works only with SAS Enterprise Guide 4.3
- Export to XLSX or XLS file
- Specify the name of the worksheet in the exported file
- Specify which columns to include in the exported file
- Specify the output format (as a SAS format) for each column. This is especially useful for applying special treatment to date and date-time values to preserve the correct format in the spreadsheet.
- Optionally place field headings on the first row of the exported sheet. You can also use labels instead of variable names.
- Optionally create multiple worksheets if the row count exceeds the maximum supported by Microsoft Excel
- Optionally create multiple worksheets if the column count exceeds the maximum supported by Microsoft Excel
Note: This task uses "native" technology to do its work: SAS/ACCESS to PC Files is not required. However, this also means that the task does not generate a SAS program that can be run independently outside of SAS Enterprise Guide or within a stored process.
Download the custom task from this location (ZIP file, 473KB). Read the README PDF file in the ZIP archive to learn how to install and use it with SAS Enterprise Guide 4.3.
Update: There is also a 5.1 version of this task available via SAS Note 41132.
32 Comments
Do you know if it's possible to export to multiple sheets with this add-in?
Yes, it does support multiple sheets for "overflow" situations.
It does not support adding sheets to an existing workbook, or breaking out the sheets by a BY value.
You said "It does not support adding sheets to an existing workbook".... do you know if it will soon or if there is another way to do that now? I have an Excel workbook (2010) with a lot of formulas and stuff in worksheets that all depends on the data that I want to update in another worksheet....thanks.
I don't think that will be added to this task in the near term, but soon SAS 9.3 will have better native support for XLSX files using PROC EXPORT, and this might be covered there.
Hi,
can we export sas data into existing excel file sheet which is having specific template. i know we can do in sas 9.2 using DDE but sas enterprise guide doesn't support DDE. so do you know any other way to do this task.
Hi Munna,
You have 3 way to export sas data into existing excel file sheet which is having specific template.
1. SAS 9.2 (DDE)
2. SAS enterprise guide ( submit SAS DDE program selected local, you still need to setup...)
3. Submit an application (you don't need to write SAS DDE), I am using it to replace SAS DDE.
Hi wenming,
What type of application do you submit with Enterprise Guide to replace the DDE.
Your feedback on these would be greatly appreciated.
Cheers!
Pingback: Export to Excel 2010 just got a little bit easier - The SAS Dummy
Chris, I use SAS on VMS instead of Unix to generate the reports. Currently reports comming in .XLS format and wanted to convert them into .XLSX format. It use ODS HTM to generate the report. I tried ODS EXCELXP to generate .XLSX report but go an error says improper use of function, any idea how to fix this.
Shaily, ODS tagsets.ExcelXP creates Office XML, which is not the same as an XLSX file. The only methods for creating native XLSX files are to use DBMS=XLSX and DBMS=EXCELCS. See this related post for the details. I don't think that these are supported on the VMS host though... See this paper for a matrix of what SAS/ACCESS approaches are supported on which hosts.
Is there any plans in SAS R&D to create custom import task for Excel 2007/2010 .xlsx files?
SAS Enterprise Guide 4.2 and later can already import XLSX files, and the import wizard provides a great deal of control over how the file is processed.
One of the problems I find using Enterprise Guide over base sas is its inability to export more than one table to a single spreadsheet in different sheet tabs or as tables in a database, Enterprise guide wants to create a new spreadsheet or database for each sas table exported
in base sas the export wizard gives you the option to append and existing file so you can store another dataset in an existing spreadsheet or database, are sas ever going to include the append option into its export tasks rather than just a replace option?.
Philip,
If you're willing to write your own code, of course you can accomplish these tasks by using PROC EXPORT and other means. When EG exports to Excel directly, it uses a different mechanism than SAS programs (and thus doesn't use/require SAS/ACCESS to PC Files).
Another method that provides even more control is to use the SAS Add-In for Microsoft Office within Excel itself. You can pull SAS data and results (such as from a SAS stored process) into any spreadsheet location, and also apply your custom formatting.
As to whether SAS will ever include an append option in a wizard/task form within EG...I agree this would be useful, but I'm not sure if it's on a roadmap. You can make your suggestion known formally by submitting it to Tech Support.
Hello Chris,
I'm using add-in SAS for MS Excel but i have a large table with 67 076 lines and i can't print it on excel because of an error. It is said that Add-in found an error and i must restart. I would like to know if it is the way of the length of my data.
Thank
Merl, this sounds like an issue that you should track with SAS Technical Support. Here's the link to begin a track -- the consultants will be happy to help you.
I do not understand how to export to excel 2007. I can not find the option in the menu of SAS. Please help me!
There are several methods, and which is best depends on your software, configuration, and needs. See How do I export to Excel for more details.
Thanks Chris for this Custom Task.
I see a small glitch in the Task when i export a dataset containing column having amount with $ symbol and decimal places. For eg while Exporting the dataset SASHELP.CARS using this task to an Excel 2010 Worksheet display the values in the excel as for eg : $36,945{.00} with the {} brackets instead of just $36,945.00. Can this be fixed?
Prashant - I suggest you track this with SAS Tech Support -- providing a good test case -- to see if it can be addressed.
Chris - How do i export to excel files using proc export inside a code in the process flow? I 'm running on sas eg 5.1 and have pcfiles installed and open while runnig the export step. Below is my code.
proc export data=abc
outfile="c:\test.xlsx"
dbms=excelcs
REPLACE;
sheet="sheet1";
Server= '123.000.00.000';
Port = XXXX;
run;
Thanks!
Hi Sandra,
See the methods here:
Export to Excel methods
And here:
Exporting and downloading any file from SAS Enterprise Guide
Hi Chris,
I have applied this and tried to export into .XLSX file using SAS EG 4.3 version.
but end with below error:
An internal error has occurred. The error details are:
Unspecified error: -2147417848(0x80010108)
Ram, that error code indicates (I think) that your SAS session was lost/disconnected. I suggest that you follow up with SAS Technical Support.
Hi all,
Can anyone please help me to fix this?
Being a beginner,I'm using SAS university edition and exporting a file to excel (.xlsx).
the syntax goes as below.
proc export data=sashelp.class
dbms=xlsx
outfile="C:\Users\dell\Desktop\immi.xlsx"
replace;
run;
error: xlsx file cannot be created, make sure the path is correct and that you have write permission.
I have msoffice 2010 installed in my computer.
thanks for helping me to fix this.
fani.
Fani, try changing the OUTFILE destination to:
The SAS University Edition uses a Linux VM under the covers, so you must express file paths using Linux/UNIX conventions.
Pingback: Sas Manual 9.2 Pdf - Queerspectives
Hi,
On Enterprise Guide5.1 , connected to local server and ran attached code and observed that image successfully inserted into excel report and find attached report(Excel Report.xls). But when connected to SASApp server on Enterprise Guide5.1, it is not inserted Image into excel report. In that I have placed image in different locations and ran code, it is not working. Please let me know , (ODS EXCEL) which does support images .If you say yes, please provide information(sample code or links) on the same.
Regards,
Baba.
ODS EXCEL does support images (produced by ODS Graphics or SAS/Graph) without any additional work. See my example here.
If you're using another method like TAGSETS.EXCELXP and a post-process to insert an image, remember that the image has to be located in a place where the SAS session can reach it/reference it into the spreadsheet.
Hi,
Currently the company where am working decided to move from Excel to SAS for reporting . The Existing excel workbook has a lot of formulas and the GRID will automatically change all the values upon over riding any main feild value . Am using EG 5.1 to create the same in SAS and give the user the same facility to override and generate the reports like it was before in Excel .
Can you please suggest me the best approach to achieve this ?
Thanks ,
Prashanth
If you're looking for a way to update an Excel workbook "in place" without modifying existing formulas, I would suggest using the SAS Add-In for Microsoft Office. This allows you to pull in the values you want from SAS into your existing sheet, instead of rewriting the workbook with a PROC EXPORT or other operation.
Pingback: Custom tasks for SAS Enterprise Guide: Q&A - The SAS Dummy