topic Re: Exporting from SAS 9.3 creates a corrupted Excel file in SAS Programming

Exporting from SAS 9.3 creates a corrupted Excel file

MarkRoberts — Mon, 21 Jan 2013 14:06:35 GMT

I'm running SAS 9.3 and Excel 2007 on Windows XP - and have recently encountered a problem.

I have a number of overnight jobs that creates simple output for various members of the business - and have traditionally out put this to an excel file that is then sent to the relevant parties as part of the SAS job.

Recently a number of people have complained that the file is corrupt - however I can open the file no problems and have been copying the output into another file and emailing it to them once I get into work.

Its not a valid long term solution as its a painful task for me and relies on me being in work to be able to do this.

After some searching around - it appeared to be a problem with other members running Windows 7 and some issue with Excel 2007.

The solution seemed to be to uninstall and install SAS PC server - I did this to no avail - also from a recent test I did with a colleague (non SAS literate) they could open the file and they are running Windows 7.

Does anyone have any experience or help that might shed light on this?

Thanks in advance

Re: Exporting from SAS 9.3 creates a corrupted Excel file

mfisher — Mon, 21 Jan 2013 15:11:53 GMT

I've not encountered any problems, either.

Given that you can open the file, could it be that SAS is exporting it as .xlsx, but the others can only read .xls?

Re: Exporting from SAS 9.3 creates a corrupted Excel file

MarkRoberts — Mon, 21 Jan 2013 15:34:16 GMT

I don't think so - the file is saved on my machine as .xls and we are all using Office 2007 - the only difference is my machine hasn't been upgraged to Windows 7 yet and is still running on XP.

Re: Exporting from SAS 9.3 creates a corrupted Excel file

OS2Rules — Mon, 21 Jan 2013 15:46:36 GMT

Are you using the ExcelXP tagset to write the Excel report? Remember that the tagset writes the output as .xml, not as .xls no matter which extension you specify on your output. The Windoze 7 machines may have a problem with the xml.

You could try converting the xml file to a real xls file before you send them using this trick: 43496 - Convert files created using an ODS destination to native Excel files

At least you won't have to do the conversion manually.

Re: Exporting from SAS 9.3 creates a corrupted Excel file

newbie_ari — Mon, 21 Jan 2013 16:47:20 GMT

Hi,

We also generally have to output our sas data, and then share with other people. We export it in csv. If we have consolidate all the outputs & create a report, then we have vba macros to do that. This way we generally bypass the issues you mentioned. Not a exact answer to your question, but just tried to give an option.

Rgds,

Re: Exporting from SAS 9.3 creates a corrupted Excel file

MarkRoberts — Tue, 22 Jan 2013 10:54:56 GMT

I ended up just using a CSV file.

Thanks for the suggestions