Writing Excel File to Server without Displaying

[DavidPhillips2]

I’m trying to write a tabulate query to Excel without displaying it on the screen because the number of columns is 1000+.

Similar code to the below worked for me in the past.  This code writes an Excel look alike file to a server but when I try to open it the file is corrupt.  Please advise.

%macro temp();
     RV=%sysfunc(appsrv_header(Content-type,application/vnd.ms-excel));
%mend;
%temp;
%LET _ODSSTYLE=Normal;

ods excel file="server path/matrix.xlsx";

proc tabulate data=matrix;
class COURSE_REFERENCE_NUMBER instructor_primary_id;
table COURSE_REFERENCE_NUMBER, instructor_primary_id;
run;
ods excel close;

[ballardw]

If you don't want Excel (or which ever spreadsheet might open the file by default) in base SAS you would change the SAS preferences:

Tools>Options>Preferences, on the RESULTS table uncheck the "View results as they are generated".

Unfortunately I can't suggest any code that would set that and then unset that behavior as needed. (Yet)

[DavidPhillips2]

Ballard,

My goal is to only download an Excel file.  The display output would take forever to load on a screen.

[DavidPhillips2]

This makes an excel file but the file is corrupt and I cannot open it.

ods excel file = "serverpath/Maxtrix.xlsx";
proc tabulate data=AlliedHealthMatrixBase;
class COURSE_REFERENCE_NUMBER instructor_primary_id;
table COURSE_REFERENCE_NUMBER, instructor_primary_id;
run;
ods excel close;

[Tom]

Make sure that your PROC is actually generating some output.

Make sure that you have moved the XLSX file to where ever you are trying to open it as a BINARY file so that it is not getting corrupted in transit.

[DavidPhillips2]

Tom,

The proc is generating output.  What do you mean by binary file?  Do i set a setting in SAS?

[Tom]

Is SAS running on the same machine as where you are running Excel?

If not then how did you get the file from where SAS wrote it to a location that Excel can read it?  

If you use FTP or so other file transfer protocol it might have thought the file was a text file and attempted to the insert CR characters thinking that it needed to adjust for difference in how Unix and Windows normally write text files.

[Tom]

What is with the APPSRV_HEADER call?

Are you running SAS as a stored process or other web based interface?

Also what version of SAS are you using?  ODS EXCEL was initially introduced as experimental so if you are using an old version of SAS it might still be buggy.

[Reeza]

I unfortunately believe ODS EXCEL is still buggy with a lot of columns and that's part of the issue.

[DavidPhillips2]

I ended up spending too much time on this item and moved on.