The SAS Output Delivery System and reporting techniques

Export XLSX to shared drive from SAS EG

Reply
New Contributor
Posts: 2

Export XLSX to shared drive from SAS EG

I am looking to export a XLSX file to the shared driver from SAS EG (server). I am able to export an XLS file, but it fails when I try to export an XLSX file. Do you know what I might have wrong with my syntax? Also, what would be the best method to export a XLSX with formatting (e.g. bold headers)? It seems that ODS is the answer, but I don't see any options to include server information. Thanks!

 

PROC EXPORT DATA=work.tmp
     OUTFILE="\\corp\Share$\test.xls" 
     DBMS=EXCELCS REPLACE;
     SHEET="Sheet1"; 
	 SERVER='exampleserver.com'; 
	 /* SERVER_NAME=&_CLIENTMACHINE;  */
 	 serveruser='nt_a\exampleusername';
	 serverpass='examplepassword';
     PORT=####;
RUN;



PROC EXPORT DATA=work.tmp
     OUTFILE="\\corp\Share$\test.xlsx" 
     DBMS=XLSX REPLACE;
     SHEET="Sheet1"; 
	 SERVER='exampleserver.com'; 
	 /* SERVER_NAME=&_CLIENTMACHINE;  */
 	 serveruser='nt_a\exampleusername';
	 serverpass='examplepassword';
     PORT=####;
RUN;

23 PROC EXPORT DATA=work.tmp
24 OUTFILE="\\corp\Share$\examplefilepath\test.xlsx"
25 DBMS=XLSX REPLACE;
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
26 SHEET="Sheet1";
NOTE: The previous statement has been deleted.
27 SERVER='exampleserver.com';
______
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

28 /* SERVER_NAME=&_CLIENTMACHINE; */
29 serveruser='nt_a\exampleusername';
__________
180
30 serverpass='examplepassword';
__________
180
31 PORT=####;
____
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

32 RUN;

Super User
Super User
Posts: 7,074

Re: Export XLSX to shared drive from SAS EG

[ Edited ]

The XLSX engine does not require a server and so it is complaining about those options.

Did you try just changing the extension?

 

General Enhancements
General enhancements found in this or earlier releases include the following.
Starting in the fourth maintenance release, 
SAS/ACCESS 9.4 Interface to PC Files supports creating new .xlsx output files when using 
the EXPORT procedure with DBMS=EXCELCS or when using the PCFILES LIBNAME engine. 

Files with .xlsm extension are no longer supported with PROC EXPORT DBMS=EXCELCS or the PCFILES LIBNAME engine.

 

New Contributor
Posts: 2

Re: Export XLSX to shared drive from SAS EG

Hey Tom,

 

I tried the two options you suggested and both failed. Do I just need to tweak these slightly? Do you have any alternative ideas?

 

Option 1 - failed because the file wouldn't open

PROC EXPORT DATA=work.tmp
     OUTFILE="\\corp\Share$\test.xlsx" 
     DBMS=EXCELCS REPLACE;
     SHEET="Sheet1"; 
	 SERVER='exampleserver.com'; 
	 /* SERVER_NAME=&_CLIENTMACHINE;  */
 	 serveruser='nt_a\exampleusername';
	 serverpass='examplepassword';
     PORT=####;
RUN;

Option 2 - failed because the filepath was incorrect

PROC EXPORT DATA=work.tmp
     OUTFILE="\\corp\Share$\test.xlsx" 
     DBMS=XLSX REPLACE;
     SHEET="Sheet1"; 
RUN;

 

 

23 PROC EXPORT DATA=work.tmp
24 OUTFILE="\\corp\Share$\test.xlsx"
25 DBMS=XLSX REPLACE;
26 SHEET="Sheet1";
27 RUN;

ERROR: Temporary file for XLSX file can not be created ->
/sas/foundation/sasconfig_grid/Lev1/SASGrid/\\corp\Share$\/test.$$1. Make sure the
path name is correct and that you have write permission.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


opening xslx error.PNG
Super User
Super User
Posts: 7,074

Re: Export XLSX to shared drive from SAS EG

I would check that your SAS and PC FILES Server software and Excel software running on the WIndows machine are using most recent versions.  Then check with SAS support.

 

You could also try using the second format to write the file on the SAS host (say in the WORK directory) and then using an EG task to download the files from the SAS host. That is assuming that your PC that is running EG can access the network location that you want to write to.

Super User
Posts: 7,832

Re: Export XLSX to shared drive from SAS EG

This:

/sas/foundation/sasconfig_grid/Lev1/SASGrid/\\corp\Share$\/test.$$1

points to the root of your problem. Your SAS executes on a UNIX server/environment, and you try to use Windows filename syntax.

Since your file/pathname does not start with a forward slash (/), the system assumes a relative path and appends your path to the current working directory of the SAS server process, which is /sas/foundation/sasconfig_grid/Lev1/SASGrid/.

 

- Make sure that your file share server is also mounted on your UNIX

- Use the correct pathname for the mount to store your file (ask your system/network administrator)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 4 replies
  • 335 views
  • 0 likes
  • 3 in conversation