The SAS Output Delivery System and reporting techniques

Excel output in .xlsx format using SAS

Reply
New Contributor
Posts: 4

Excel output in .xlsx format using SAS


Hi,

I want to directly produce an excel output, specifically in .xlsx format. I don't want to have an xml output, to be opened in Excel application.

Could you please suggest some tagset or ODS options to achieve the same.

Regards,

Parasar

Super User
Posts: 19,860

Re: Excel output in .xlsx format using SAS

It depends on what type of formatting you need. If you don't need any formatted cells then proc export can export straight to XLSX based on the version of SAS installed.

If you need formatting then export using ExcelXP, which is XML and then convert the file to XLSX.

If you need images then export to HTML or MS_OFFICE and then convert the file to XLSX.

New Contributor
Posts: 4

Re: Excel output in .xlsx format using SAS

Thanks Reeza for your reply. I am using Proc Report for the output with colored header and byline titles. So putting it in a csv or some other format and then using proc export is not a feasible option for me. And I thought directly producing the .XLSX output will be faster and the output file size will be quite smaller. Using ExcelXP creates huge file in XML and converting it to .XSLX will be an overhead. Do you have any idea on any tagset available by SAS, we can directly produce in .xlsx with desired formatting.

Thanks Again for your response.

Valued Guide
Posts: 2,177

Re: Excel output in .xlsx format using SAS

have you seen these usage notes

http://support.sas.com/kb/37/040.html which uses dde

and

http://support.sas.com/kb/43/496.html which uses vbs

Super User
Posts: 19,860

Re: Excel output in .xlsx format using SAS

It does create overhead and a bigger file, but its automated, The links in Peter C. post, show you the way to automatically convert to XLSX. You can use straight DDE but the extra programming time to do so is overhead of a programmers time versus computer time with Tagsets/VBS. Your preference.

AFAIK, there is no tagset that directly creates XLSX and allows formatting.

New Contributor
Posts: 4

Re: Excel output in .xlsx format using SAS

Thanks Reeza and Peter for your valuable suggestions here. I am currently trying to generate this .xlsx using SAS Stored Process on Unix Machine. The DDS and VBS options are really good options for batch jobs and also with programs that stores the output in some permanent locations. But since the file that needs to be converted to ".xlsx", has to be stored in some permanent location. In SAS Stored Process, we can run the report online (and get the output on the fly).

Could you please guide me, if the above options can be applied to SAS Stored Process.

Thanks again for your help so far.

Valued Guide
Posts: 2,177

Re: Excel output in .xlsx format using SAS

DDE would be unsuitable for such a process.

Have a look through the Stored Processes Community where your problem might already have been solved.

good luck

peterC

Ask a Question
Discussion stats
  • 6 replies
  • 2854 views
  • 3 likes
  • 3 in conversation