Help using Base SAS procedures

Exporting to a FTP site

Posts: 0

Exporting to a FTP site


I have stumbled across another problem....i have found the correct code to upload my file to a FTP site but i can only upload this as csv is it possible to export as xls?
my code:

filename create ftp 'test01.xls'
user='usre' pass='pass' recfm=v;
proc export data=Fred01outfile=create dbms=csv replace; run;

i have tried to change the dbms to EXCEL but alas it does not work.

please help

Super Contributor
Super Contributor
Posts: 3,174

Re: Exporting to a FTP site

Please provide SASLOG output with the exact error condition you are receiving, as well as your code. Also, your SAS version info (include service pack level) would be helpful, as well as the "local" and "foreign" operating platform would be most helpful.

Scott Barry
SBBWorks, Inc.
Super User
Posts: 5,260

Re: Exporting to a FTP site

Do you have a license for SAS /ACCESS for PC File Formats? I believe you need that when using Excel as output format.

Data never sleeps
Posts: 0

Re: Exporting to a FTP site

Sas log:
28 filename create ftp 'test01.xls'
29 host=''
30 user='user' pass=XXXXXXXXXX recfm=v;
31 proc export data=Fred01outfile=create dbms=EXCEL replace;
31 ! SHEET = "data1" ;run;

NOTE: "data1" was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.25 seconds
cpu time 0.04 seconds

So there is no error but no file created

And yes we do have a license for SAS /ACCESS for PC File Formats.
I can export to excel on to my c drive.
Super Contributor
Super Contributor
Posts: 3,174

Re: Exporting to a FTP site

Have a look at the supplied SAS DOC link - there are special considerations using DBMS=EXCEL to consider (LIBNAME statement, not FILENAME). Also, one will assume that there is at least one blank character between "Fred01" and "outfile=".
Lastly, you may want to start simple with a DATA step to "output" a sequential file to the remote/foreign FTP destination -- just to verify whether the problem is related to your FTP authority (at the remote host) or some issue with PROC EXPORT. First though, you need to review the DOC and find/resolve suitable compatibility issues with your code. Also, there are technotes and technical papers (SUGI, SGF) at the SAS support website for reference.

Scott Barry
SBBWorks, Inc.
Posts: 0

Re: Exporting to a FTP site

sorry im using SAS 9.1.3 SP4
and im on XP_pro
Super User
Posts: 5,260

Re: Exporting to a FTP site

Have you tried to create the excel file in the SAS current directory, and then do the ftp from a command prompt with same parameter as you use in the code?
Does really exist? I'm behind a firewall that doesn't allow me to ping ftp sites outside.

Data never sleeps
Super Contributor
Posts: 273

Re: Exporting to a FTP site

i don't think you may write directly on another server an Excel file

i submit
options msglevel=i;
filename create ftp 'testftp.xls'
host='' rcmd='type i'
user='andre' pass='xxxxxxx' recfm=v debug;
proc export data=sashelp.class outfile=create dbms=EXCEL replace;
SHEET = "data1" ;run;
and the result give

NOTE: 220 Service de transfert de fichiers (FTP)
NOTE: <<< 220 Service de transfert de fichiers (FTP)
NOTE: >>> USER andre
NOTE: <<< 331 Please specify the password.
NOTE: <<< 230 Login successful.
NOTE: >>> PORT 173,16,20,79,11,32
NOTE: <<< 200 PORT command successful. Consider using PASV.
NOTE: <<< 200 Switching to ASCII mode.
NOTE: <<< 257 "/home/users/andre"
NOTE: >>> type i
NOTE: <<< 200 Switching to Binary mode.
NOTE: >>> RETR testftp.xls
NOTE: <<< 550 Failed to open file.
ERROR: Physical file does not exist, testftp.xls.

there is a gap between the internal production of SAS which is ok
and the link between the pc and the server for an excel file

as it is working perfect for a cport file

NOTE: User andre has connected to FTP server on Host .

NOTE: Proc CPORT begins to transport data set SASHELP.CLASS
NOTE: The data set contains 5 variables and 19 observations.
Logical record length is 40.
NOTE: <<< 226 File receive OK.
NOTE: PROCEDURE CPORT used (Total process time):
real time 0.57 seconds
cpu time 0.03 second

Try another method and forget this one

Before sp4, was Sas Institute constructing a special solution for exporting excel
file to a pc from a linux or unix and the rules were special.
This indicate that it was not easy to threat the binary pf Microsoft

Ask a Question
Discussion stats
  • 7 replies
  • 4 in conversation