topic Re: Proc Export to Excel result "corrupted" in SAS Procedures

Proc Export to Excel result "corrupted"

Bill — Mon, 20 Mar 2017 14:29:25 GMT

Using this code in SAS 9.4 TS1M0 to produce a large xl file ... (56 columns, 48,000 rows)

proc export data=GQBPlus_BU (where=(put(OwnerOpCd,$BUCd.)="&BUCode"))

Outfile= "\GQBPlus\&BuCode.\GQBPlus_&BUCode" /*path abbreviated*/

DBMS=xlsx Replace;

Sheet="Local&BUCode";

Upon opening the excel file, this message comes up:

We found a problem with some content in ... Full message attached as a jpg file. The file opens upon a Yes answer and Excel produces an uninformative log.

What needs to be changed to avoid the excel message?

Thank you,
Bill

Re: Proc Export to Excel result "corrupted"

ballardw — Mon, 20 Mar 2017 14:57:06 GMT

I suspect the only way to not get a message from Excel is to ensure the resultant file doesn't violate any of Excel's expectations.

What is the value of &bucode? You may be trying to create a sheet name excel doesn't like.

Another option would be to export to CSV and open that with Excel. At least with CSV you can see what your exported data looks like with any file viewer that supports plain text.

There may also be a problem if none of your records match your where criteria.

You might try making an actual subset of the data and not using a where dataset option.

any diagnostics from the Proc Export like 0 records written?

Re: Proc Export to Excel result "corrupted"

Bill — Mon, 20 Mar 2017 15:54:35 GMT

Thank you ballardw for your response.

An xl file is "successfully" produced with the code used. I don't see a problem with the file, but it is disconcerting for users to see the message. Hence I'd like to avoid the situation.

The value of &BUcode is PCR - xl should like that!

Tried the csv route and it works well. Users will be delighted!

Thanks,
Bill