topic Re: Writing Excel File to Server without Displaying in New SAS User

Writing Excel File to Server without Displaying

DavidPhillips2 — Fri, 03 May 2019 14:59:18 GMT

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;

Re: Writing Excel File to Server without Displaying

ballardw — Fri, 03 May 2019 15:09:23 GMT

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)

Re: Writing Excel File to Server without Displaying

DavidPhillips2 — Fri, 03 May 2019 15:14:56 GMT

Ballard,

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

Re: Writing Excel File to Server without Displaying

DavidPhillips2 — Fri, 03 May 2019 15:38:57 GMT

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;

Re: Writing Excel File to Server without Displaying

Tom — Fri, 03 May 2019 15:52:24 GMT

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.

Re: Writing Excel File to Server without Displaying

DavidPhillips2 — Fri, 03 May 2019 15:55:17 GMT

Tom,

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

Re: Writing Excel File to Server without Displaying

Tom — Fri, 03 May 2019 16:03:33 GMT

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.

Re: Writing Excel File to Server without Displaying

Tom — Fri, 03 May 2019 16:06:01 GMT

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.

Re: Writing Excel File to Server without Displaying

Reeza — Fri, 03 May 2019 17:21:23 GMT

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

Re: Writing Excel File to Server without Displaying

DavidPhillips2 — Mon, 06 May 2019 18:02:02 GMT

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