Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

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

[ Edited ]

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;

 


Accepted Solutions
Solution
‎12-16-2017 08:16 AM
Super User
Posts: 13,512

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

Posted in reply to GeorgeBonanza

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.

View solution in original post


All Replies
Solution
‎12-16-2017 08:16 AM
Super User
Posts: 13,512

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

Posted in reply to GeorgeBonanza

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.

☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 320 views
  • 0 likes
  • 2 in conversation