Learning SAS? Welcome to the exclusive online community for all SAS learners.

Proc print to an Excel file produces corrupt file

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

Proc print to an Excel file produces corrupt file

I'm sorry to ask what will inevitably be a dumb newbie's question, but I have checked the documentation and searched for a solution in the "Analytics U community" without success.  Here's the code:

ods select none;
ods excel file="/folders/myshortcuts/Will's_Documents/Projects/_VU Melbourne/Kristal Hammond/CP parameters.xlsx";

proc print data=estwide noobs;
run;
ods excel close;
ods select all;

 

Here's the relevant log:

 NOTE: There were 81 observations read from the data set WORK.ESTWIDE.
 NOTE: PROCEDURE PRINT used (Total process time):
       real time           0.00 seconds
       cpu time            0.01 seconds
       
 
 63         ods excel close;
 NOTE: Writing EXCEL file: /folders/myshortcuts/Will's_Documents/Projects/_VU Melbourne/Kristal Hammond/CP parameters.xlsx

But when I try to open the spreadsheet (Windows 7, Office 2010) I get this:

 

Excel found unreadable content... Do you want to recover the contents...

I clicked Yes and got:

The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt.

 

What could be simpler?  Why am I so stupid?

 

Will


Accepted Solutions
Solution
‎02-09-2017 04:16 PM
Super User
Super User
Posts: 6,502

Re: Proc print to an Excel file produces corrupt file

There is no need to use PROC PRINT to copy data to an Excel worksheet.  That will just introduce extra complexity to a simple task.

Try just makeing the Excel sheet from the data.

libname out xlsx "/folders/myshortcuts/Will's_Documents/Projects/_VU Melbourne/Kristal Hammond/CP parameters.xlsx";

data out.estwide;
  set estwide ;
run;

libname out clear ;

View solution in original post


All Replies
SAS Super FREQ
Posts: 361

Re: Proc print to an Excel file produces corrupt file

@WillTheKiwi, does the data set have more than 26 columns/variables?

 

http://support.sas.com/kb/47/105.html

 

Randy

Contributor
Posts: 65

Re: Proc print to an Excel file produces corrupt file

11 variables, about 100 observations. Here's the first few lines of the
proc print:


Parameter

Group

Athlete

T0

T1

T2

T3

T4

T5

T6

T7


AnCap

ALT

ANLI

-1.918E9

15660.7

-2.651E9

11819.8

-4.17E13

13729.1

9455.8

14541.5


AnCap

ALT

JULE

8852.8

13943.3

15114.9

15917.8

14101.5

13506.4

18345.4

17557.3


AnCap

ALT

LABA

19413.0

2.5035E9

1.593E10

20087.7

36320.2

40188.3

81321.9

47278.2



Some of the values are absurd, because some of the data have too much noise
for Proc Nlin to work properly. But that's not the issue here. I just want
to export to an excel sheet without having to copy and paste it.

Will(theKiwi)

##- Please type your reply above this line. Simple formatting, no
attachments. -##
SAS Super FREQ
Posts: 361

Re: Proc print to an Excel file produces corrupt file

Well, @WillTheKiwi, unfortunately, I gave it a shot but don't have any more ideas at this point. I'm sure some of our other fine contributors will come up with something!

 

Randy

Solution
‎02-09-2017 04:16 PM
Super User
Super User
Posts: 6,502

Re: Proc print to an Excel file produces corrupt file

There is no need to use PROC PRINT to copy data to an Excel worksheet.  That will just introduce extra complexity to a simple task.

Try just makeing the Excel sheet from the data.

libname out xlsx "/folders/myshortcuts/Will's_Documents/Projects/_VU Melbourne/Kristal Hammond/CP parameters.xlsx";

data out.estwide;
  set estwide ;
run;

libname out clear ;
Contributor
Posts: 65

Re: Proc print to an Excel file produces corrupt file

Wow, that worked, thank you. But I would still like to know why a seemingly simple bit of code apparently consistent with the documentation ended up producing a corrupt file. If no further enlightenment is forthcoming, I will accept this solution.

 

By the way, I never did get a satisfactory answer as to why the ods powerpoint file= etc command followed by proc gplot did not produce an editable vector graphic.

https://communities.sas.com/t5/SAS-GRAPH-and-ODS-Graphics/Best-way-to-get-graphs-into-Powerpoint-for...

 

It seems that ods to the Windows platform is a work in progress.

 

Will

Trusted Advisor
Posts: 1,398

Re: Proc print to an Excel file produces corrupt file

@WillTheKiwi, it seems to me, that I have read somewhere that using ODS to create excel sheet

is fitting the xls format but not the xlsx format.

 

In your code you defined:

ods excel file="/folders/myshortcuts/Will's_Documents/Projects/_VU Melbourne/Kristal Hammond/CP parameters.xlsx";

 

 

Contributor
Posts: 65

Re: Proc print to an Excel file produces corrupt file

Thanks. Just tried that. Still corrupt.

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Super User
Super User
Posts: 6,502

Re: Proc print to an Excel file produces corrupt file

Since it appears to be reproducible why don't you post the miminal set of data and code that we can use to reproduce the error.

 

My assumption is that there is something about either your Office version or the content that you are trying to write that is confusing SAS (or Excel).  Perhaps your default titles or footnotes contain characters that SAS is not translating properly for Excel.  Perhaps your default Encoding settings are confusing SAS or Excel.

Contributor
Posts: 65

Re: Proc print to an Excel file produces corrupt file

Thanks.  I am using whatever defaults SAS Studio/UE opens with.  Here's some code that produces the same error:

ods select none;
ods excel file="/folders/myshortcuts/ExternalFiles/test.xlsx";

data dat1;
do n=1 to 3;
  Name="Whoever";
  x=10;
  output;
  end;
  
proc print data=dat1 noobs;
run;

ods excel close;
ods select all;

 

If I run the proc print, I get this when I paste the html into Excel. There are no titles or footnotes:

 

n Name x

1Whoever10
2Whoever10
3Whoever10

 

(The above should be a simple table, but it might not render as such in this message.)

 

Will

Super User
Super User
Posts: 6,502

Re: Proc print to an Excel file produces corrupt file

Works fine when I run in on Linux using SAS 9.4 TSM 3 and open it with Excel Version 14.0.7173.5000 (32-bit)

 

Contributor
Posts: 65

Re: Proc print to an Excel file produces corrupt file

Sorry, I didn't provide all the version information. Here's SAS's:

Release: 3.5 (Basic Edition)
Supported browsers:

  • Microsoft Internet Explorer 9, 10, 11
  • Mozilla Firefox 21+
  • Google Chrome 27+
  • Apple Safari 6.0+ (on Apple OS X)

Build date: 3 Feb 2016 10:49:31 PM
SAS Mid-tier release: 13 Jan 2016 7:00:00 PM
Java Version: 1.7.0_76
SAS release: 9.04.01M3P06242015
SAS platform: Linux LIN X64 2.6.32-573.26.1.el6.x86_64
Site name: UNIVERSITY EDITION 2.3 9.4M3
Site number: 70186327

 

I'm using Internet Explorer 11.0.9600.18449

Update 11.0.35.

 

I can't find the version of Office 2010 I am using, or that of the system (other than Windows 7 Enterprise).

 

Anyway, apparently it's incompatibillity somewhere that SAS developers have not encountered or bothered to debug.

SAS Super FREQ
Posts: 8,743

Re: Proc print to an Excel file produces corrupt file

Why do you have ODS SELECT NONE above your code? The way any ODS destination works is by taking your output and sending it to the FILE= path and file.

When I run your code WITHOUT the ODS SELECT NONE statement, everything works fine.

Without any output to receive from PROC PRINT (because of the ODS SELECT NONE), my guess is that ODS tries to create a file, but that is where the corrupt file message is coming from.

cynthia
Contributor
Posts: 65

Re: Proc print to an Excel file produces corrupt file

Thank you, it works! Now how do I turn off the output to the Output window?  I tried ods output close; well ahead of the proc print, without luck.

SAS Super FREQ
Posts: 8,743

Re: Proc print to an Excel file produces corrupt file

Hi,
Well, in SAS University Edition, I rarely do this, but ODS _ALL_ CLOSE; will close all the open destinations -- what you call the Output Window in SAS Studio. Generally, not a good idea, though, SAS University Edition REALLY wants to have a destination open. My suggestion is that since you have taken full control with the ODS EXCEL statements, just ignore the HTML window that pops up with the HTML version of your output. It will go away when you start a new program.

cynthia
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 29 replies
  • 798 views
  • 1 like
  • 5 in conversation