Exporting SAS dataset to Excel file that doesn't already exist

GeorgeBonanza — Fri, 15 Dec 2017 23:58:50 GMT

I want to export a SAS dataset from UNIX server to LAN as an Excel file but I want the Excel workbook to be created if it doesn't already exist.

The first export works and the ".xls" file is created when it doesn't exist.

However, I am concerned that the dataset might exceed the ~ 65000 rows in a ".xls" file so I would prefer to be able to export the data as ".xlsx".

The second export does not work when I try to export as ".xlsx" file unless I manually create a blank workbook called "Want.xlsx" before running the export.

I am working in SAS Enterprise Guide 6.1. Any help would be greatly appreciated. Thanks in advance.

PROC EXPORT DATA= work.HAVE
	OUTFILE= "\\LAN\WANT.xls"
	DBMS= EXCELCS REPLACE;
	SERVER=SERVERNAME; PORT=0001;
	SERVERUSER="USRDOM\&USER"; SERVERPASS="&PW";
	SHEET= "OUTPUT";
RUN;

PROC EXPORT DATA= work.HAVE
	OUTFILE= "\\LAN\WANT.xlsx"
	DBMS= EXCELCS REPLACE;
	SERVER=SERVERNAME; PORT=0001;
	SERVERUSER="USRDOM\&USER"; SERVERPASS="&PW";
	SHEET= "OUTPUT";
RUN;

Re: Exporting SAS dataset to Excel file that doesn't already exist

ballardw — Sat, 16 Dec 2017 00:11:32 GMT

Try DBMS=EXCEL or DBMX=XLSX instead of DBMS=EXCELCS. EXCELCS expects to write to XLS not XLSX.

Since your EG is a couple of versions behind your server may be as well. I don't remember exactly when XLSX became available so not sure which should work.

topic Re: Exporting SAS dataset to Excel file that doesn't already exist in SAS Procedures

Exporting SAS dataset to Excel file that doesn't already exist

Re: Exporting SAS dataset to Excel file that doesn't already exist