topic Re: How to save an XLSX file to an FTP server in SAS Programming

How to save an XLSX file to an FTP server

sas_student1 — Tue, 11 May 2021 01:32:15 GMT

Hello,

I am trying to use SAS to FTP an XLSX file that I create via SAS.

I have a SAS code that I use to prepare a dataset that I then save and excel (XLSX) file. I need to FTP these file over every week, I want to set up an automated process that runs the data every week (this I already have set up) and in the automated process I would like to use SAS to FTP the XLSX file it prepares.

I have the host, user and pass. There is a folder (mailbox path) as well that I would like to use.

I tried using the below code that I found in the forum (the user, host, pass is censored, so is the 'mailboxpath') but the below didn't work. There was no log error either for me to troubleshoot the error.

Suggestions on what other code I can use (if the below code is not the way to go)

%let user=XXXX;
%let host=YYYY;
%let pass=ZZZZZ;
filename outdir ftp 'mailboxpath' dir new host="&host" user="&user" pass="&pass" ;

data _null_;
  set X.data ;
  file outdir('C:\Users\name\toexport.xlsx') xlsx ;
run;

Re: How to save an XLSX file to an FTP server

Tom — Tue, 11 May 2021 02:42:37 GMT

I thought you want to move the XLSX file. Your data step is reading a SAS dataset and trying to write a text file. Note that the FILE statement does not support the XLSX engine. The XLSX engine only works when defining a LIBNAME.

What is the name of the existing XLSX file on the SAS host that you want to move to the remote host using FTP protocol?

Do you really want to use the FTP protocol? Most hosts now require you to use the more secure SFTP protocol instead.

Re: How to save an XLSX file to an FTP server

japelin — Tue, 11 May 2021 02:53:04 GMT

Please check if you are able to access the site successfully via FTP.
You should be able to login and get the root directory with the following code.

If you are using a router, don't forget to specify passive mode.

filename dir ftp '' ls user="&user"  host="&host"  pass="&pass";
data _null_;
   infile dir;
   input;
   put _INFILE_;
run;

If you simply want to upload the file to ftp, you can use command prompt or powershell script to upload and run it with x command.

Re: How to save an XLSX file to an FTP server

Reeza — Tue, 11 May 2021 03:54:18 GMT

1. Export your data set to your XLSX file to somewhere you have access. If you don't need to keep it, the work library is a good location.
2. Move the file using an appropriate method, FCOPY() but you probably want to use something more similar to this
https://communities.sas.com/t5/SAS-Programming/Move-files-between-server-using-SFTP/td-p/350673