BookmarkSubscribeRSS Feed
futbol10
Calcite | Level 5

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;

4 REPLIES 4
Tom
Super User Tom
Super User

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.

 

futbol10
Calcite | Level 5

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
Tom
Super User Tom
Super User

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.

Kurt_Bremser
Super User

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)

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 4027 views
  • 0 likes
  • 3 in conversation