topic Re: Upload SAS Dataset from SAS to FTP as csv in SAS Programming

Upload SAS Dataset from SAS to FTP as csv

djbateman — Thu, 24 Sep 2015 14:48:06 GMT

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?

Re: Upload SAS Dataset from SAS to FTP as csv

ballardw — Thu, 24 Sep 2015 15:25:36 GMT

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.

Re: Upload SAS Dataset from SAS to FTP as csv

djbateman — Thu, 24 Sep 2015 18:10:41 GMT

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.

Re: Upload SAS Dataset from SAS to FTP as csv

ballardw — Thu, 24 Sep 2015 19:45:36 GMT

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.

Re: Upload SAS Dataset from SAS to FTP as csv

djbateman — Fri, 25 Sep 2015 12:11:44 GMT

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.

Re: Upload SAS Dataset from SAS to FTP as csv

michelle_delaurentis_sas_com — Fri, 25 Sep 2015 17:24:44 GMT

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

Re: Upload SAS Dataset from SAS to FTP as csv

djbateman — Fri, 25 Sep 2015 17:26:56 GMT

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?

Re: Upload SAS Dataset from SAS to FTP as csv

ballardw — Fri, 25 Sep 2015 17:29:57 GMT

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.

Re: Upload SAS Dataset from SAS to FTP as csv

djbateman — Fri, 25 Sep 2015 17:33:10 GMT

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.

Re: Upload SAS Dataset from SAS to FTP as csv

djbateman — Fri, 25 Sep 2015 17:34:09 GMT

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.