DATA Step, Macro, Functions and more

Load csv file from SAS table to FTP

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Load csv file from SAS table to FTP

[ Edited ]

Hi there,

 

I tried to load a csv file from SAS to FTP, I used the code below:


filename FtpStg ftp "'Test.csv'" debug
host='ftp://XXXXXXXXXX'
user="Username" pass="password" RCMD='SITE RDW NOWRAPrecord READTAPEFORMAT=V' debug;


proc export data=Test
outfile=FtpStg
dbms=csv
replace;
run;

 

I got this message:

NOTE: Unable to connect to host ftp://XXXXXX Check validity of host name.
ERROR: Open failed for file FTPSTG  
ERROR: Host name ftp://XXX not found.
NOTE: The SAS System stopped processing this step because of errors.

 

 

Could someone help me on this?

 

Thank you in advance.

 

Best,

Salvatore


Accepted Solutions
Solution
‎04-14-2017 09:51 AM
Respected Advisor
Posts: 3,895

Re: Read csv file from FTP

@SAS_BI

The aim here, it is to create a data set via SAS and after to load the data set to a location via FTP

 

That's where I'm coming from. Isn't the aim simply to copy a .csv from one location to another location. Why is it an aim to load it also into an intermediary SAS dataset?

 

Now the question is: Can your FTP server access the original source location or do you first need SAS (or a process running on the SAS server) to copy the .csv to a location accessible to the FTP server?

 

View solution in original post


All Replies
Super User
Super User
Posts: 7,407

Re: Read csv file from FTP

Sorry, are you writing a file out from SAS or readin to SAS as this: "I tried to load a csv file from FTP to SAS" implies you are reading a file in, but in your code you use proc export, which is a procedure to write a file out from SAS?  As for your error:

ERROR: Host name ftp://XXX not found.

Kind of tells you all there is, host is not found?  I assume you blanked something out, but if you can't show it then you need to resolve it with your IT.

SAS Employee
Posts: 9

Re: Read csv file from FTP

Try taking the "ftp://" string out of the hostname (it should be just the name, ie host='server.company.com')

 

Some FTP examples:  http://support.sas.com/kb/43/962.html

Occasional Contributor
Posts: 12

Re: Read csv file from FTP

I am sorry RW9, I have update the message, actually I want upload a SAS table to FTP and save it as CSV file.

 

Thank you again

Occasional Contributor
Posts: 12

Re: Read csv file from FTP

Thank you DaveHorne, I have tried it but still I have the same issue.

 

Super User
Posts: 10,516

Re: Read csv file from FTP

First please confirm that you have been able to login to the FTP site and upload another small file using an FTP or command line.

If that works, export an example SAS data set to a local file and then upload it to the same site using FTP commands. If that works make sure that the FILENAME parameters include the commands you give to manually upload.

 

Are you sure that your FTP login has all of RWD permissions? That might cause a failure if you only have RW and will fail with only R.

 

I am a little concerned about your parameters NOWRAPrecord READTAPEFORMAT=V. I would not expect the text file SAS creates with export to need such though I would worry about text vs binary file transfers. The options you use almost look like you expect an application to be reading the file as SAS writes it which may have all sorts of issues.

Occasional Contributor
Posts: 12

Re: Read csv file from FTP

Thanks for your message.

 

I have tried to updalod a file via FTP command line and it works fine as you said.

 

After that I tried the following code:

 


filename server ftp 'XXXX.csv'
CD='XXXX'
HOST='XXXXXX'
user='XXXXX' pass='XXXX' ;

data _null_;
infile 'XXXX.csv' recfm=n ;
input x $char1. @@;
file server recfm=s ;
put x $char1. @@;
run;

 

I did the last example of from this link http://support.sas.com/kb/43/962.html

 

When I run the code above sas is freezing and I have to stop the process via Task Manager.

 

Do you know if there is any issue on my code?

 

Thank you again

Super User
Super User
Posts: 6,502

Re: Read csv file from FTP

CSV files are text files so no need to get too cute in the transfer.

 

filename server ftp 'XXXX.csv' CD='XXXX'
  HOST='XXXXXX' user='XXXXX' pass='XXXX'
;

data _null_;
  infile 'XXXX.csv' ;
  file server;
  input;
  put _infile_;
run;

If that works then you can write your SAS dataset directly to the FTP file.

proc export dbms=csv file=server 
  data=x.y
;
run;
Respected Advisor
Posts: 3,895

Re: Read csv file from FTP

@SAS_BI

 

Ehhmm...? You want to copy a .csv from one location to another location via FTP. Why exactly want you have this intermediary step loading the data first into a SAS table? Why can't you just issue an FTP command for this (eventually called out of a SAS session via X command)?

Occasional Contributor
Posts: 12

Re: Read csv file from FTP

Thanks Tom,

 

I have tried your code but unfortunately SAS is running and never stop.

 

@Patrick

The aim here, it is to create a data set via SAS and after to load the data set to a location via FTP. However I ma happy as well to save the data set as csv file in my local pc and after that load it to another location via FTP.

 

 

Solution
‎04-14-2017 09:51 AM
Respected Advisor
Posts: 3,895

Re: Read csv file from FTP

@SAS_BI

The aim here, it is to create a data set via SAS and after to load the data set to a location via FTP

 

That's where I'm coming from. Isn't the aim simply to copy a .csv from one location to another location. Why is it an aim to load it also into an intermediary SAS dataset?

 

Now the question is: Can your FTP server access the original source location or do you first need SAS (or a process running on the SAS server) to copy the .csv to a location accessible to the FTP server?

 

Occasional Contributor
Posts: 12

Re: Read csv file from FTP

Hi Patrick, thanks for your reply.

Indeed the fast way it is to copy the .csv from one location to anothe location (FTP).

I have asked ICT to create a job to do this every day.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 288 views
  • 0 likes
  • 6 in conversation