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;
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.
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
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.
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)
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.