DATA Step, Macro, Functions and more

Upload SAS Dataset from SAS to FTP as csv

Reply
Regular Contributor
Posts: 220

Upload SAS Dataset from SAS to FTP as csv

I'm completely new to FTP.  I'm trying my best to figure this out on my own, but I'm having no luck.

 

I have created a SAS dataset that I want to export into a pipe-delimited ("|") csv file and upload that file to an FTP site.  I came across a code snippet that apparently shows how to do just that, but I can't seem to figure it out.  Here is the snippet:

 

%macro export_to_ftp(dsn=, outfile_name=);
	FILENAME MyFTP ftp "&outfile_name." 
		HOST="&host."
		USER="&usuer."
		PASS="&passwrd.";

	PROC EXPORT DATA=&dsn. OUTFILE=MyFTP DBMS=%SCAN(&outfile_name.,2,.) REPLACE;
		delimiter='|';
	RUN;
	filename MyFTP clear;
%mend;

%export_to_ftp(dsn=sashelp.class, outfile_name=CLASS.csv);

 

Every time I run this, I get the message:  "ERROR: Open failed for file MYFTP."  That is immediately followed by another error that the connection has timed out.

 

Can someone explain the components of the FILENAME statement to me?  I wonder if I'm missing something.  I know there is a CD= option, but I wasn't sure if that was the directory for where the file should be saved on the FTP site.  I have played around with it, but I still get the same errors.

 

In addition to uploading a file, is it possible to create a folder on an FTP site with SAS code?  Similar to the DCREATE() function?

Super User
Posts: 10,538

Re: Upload SAS Dataset from SAS to FTP as csv

Did you have a version of this code run that was not in a macro? That is the first step.

Did you run the code with option MPRINT so you can see what the macro is generating?

 

And just a suspicion, you have User="&usuer.".  Could that macro variable be misspelled?

And since you did not pass several of the macro variables as parameters you could be running into a scope issue.

Regular Contributor
Posts: 220

Re: Upload SAS Dataset from SAS to FTP as csv

It's not a macro issue.  The variables not passed through (like host, password, or user) were just place holders to mask my information.  I did run run it outside of the macro, and it didn't help any.

Super User
Posts: 10,538

Re: Upload SAS Dataset from SAS to FTP as csv

Try running the code without the macros and post the entire log. Change any sensitive values to XXXX or similar.

But there was an awful lot of macro code, such as the functions and such, that sure looked like this macro was written to do something and not demostrate a problem.

Regular Contributor
Posts: 220

Re: Upload SAS Dataset from SAS to FTP as csv

The SAS log is attached.  I'm still getting the same errors even when I take out the macros.

 

Thank you for taking your time to keep dealing with this.  I hope we can resolve this soon.

SAS Employee
Posts: 24

Re: Upload SAS Dataset from SAS to FTP as csv

The error is from the filename statement, there is an issue with the FTP.

If you are running SAS 9.2 TS1M0 there is an issue:

http://support.sas.com/kb/34098

 

 

Regular Contributor
Posts: 220

Re: Upload SAS Dataset from SAS to FTP as csv

I'm running SAS 9.3, and I'm even upgrading to SAS 9.4 hopefully by the end of the month.  Maybe that will make a difference?

Super User
Posts: 10,538

Re: Upload SAS Dataset from SAS to FTP as csv

If you manually login to the server can you then manually upload a file? You may not have privileges to write data to that location and need to provide a path to a directory on the server that allows writing.

 

I am assuming that the quotes got lost on the PASS option when anonymized for posting here.

 

 

Regular Contributor
Posts: 220

Re: Upload SAS Dataset from SAS to FTP as csv

Yes, I can manually upload files.  That's what I have been doing.  But they want me to start updating our reports daily, and I was hoping to be able to automate this so I don't have to login and transfer manually every morning.

 

And yes, the quotes must have been removed when anonymizing the output. My code has the quotes in there.

Regular Contributor
Posts: 220

Re: Upload SAS Dataset from SAS to FTP as csv

I didn't think about me not having the proper privileges to write to my location. I can look into that and see if that's the issue.
Ask a Question
Discussion stats
  • 9 replies
  • 398 views
  • 0 likes
  • 3 in conversation