topic Re: Exporting to a FTP site in SAS Procedures

Exporting to a FTP site

deleted_user — Thu, 25 Sep 2008 15:53:19 GMT

Hi

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'
host='ftp.example.com'
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

thanks

Re: Exporting to a FTP site

sbb — Thu, 25 Sep 2008 21:00:54 GMT

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.

http://support.sas.com/documentation/cdl/en/acpcref/59619/HTML/default/a003102702.htm

Re: Exporting to a FTP site

LinusH — Fri, 26 Sep 2008 06:42:44 GMT

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

/Linus

Re: Exporting to a FTP site

deleted_user — Fri, 26 Sep 2008 09:08:30 GMT

Sas log:
28 filename create ftp 'test01.xls'
29 host='ftp.example.com'
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.

Re: Exporting to a FTP site

deleted_user — Fri, 26 Sep 2008 09:40:02 GMT

sorry im using SAS 9.1.3 SP4
and im on XP_pro

Re: Exporting to a FTP site

LinusH — Fri, 26 Sep 2008 10:10:59 GMT

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 ftp.example.com really exist? I'm behind a firewall that doesn't allow me to ping ftp sites outside.

/Linus

Re: Exporting to a FTP site

sbb — Fri, 26 Sep 2008 12:55:12 GMT

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 http://support.sas.com/ for reference.

Scott Barry
SBBWorks, Inc.

Re: Exporting to a FTP site

Andre — Mon, 29 Sep 2008 15:55:49 GMT

Spud,
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='marg.rech.ine.fr' rcmd='type i'
user='andre' pass='xxxxxxx' recfm=v debug;
proc export data=sashelp.class outfile=create dbms=EXCEL replace;
SHEET = "data1" ;run;
quit;
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: >>> PASS XXXXXXXX
NOTE: <<< 230 Login successful.
NOTE: >>> PORT 173,16,20,79,11,32
NOTE: <<< 200 PORT command successful. Consider using PASV.
NOTE: >>> TYPE A
NOTE: <<< 200 Switching to ASCII mode.
NOTE: >>> PWD
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 marg.rech.ine.fr .

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: >>> QUIT
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

Andre