BookmarkSubscribeRSS Feed
parasar
Calcite | Level 5


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

6 REPLIES 6
Reeza
Super User

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.

parasar
Calcite | Level 5

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.

Peter_C
Rhodochrosite | Level 12

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

Reeza
Super User

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.

parasar
Calcite | Level 5

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.

Peter_C
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 4042 views
  • 3 likes
  • 3 in conversation