Exporting SAS Data into FTP using SAS

Question

I wanted to export SAS dataset from SAS into FTP. I can export csv file (or txt file) using the following command:

%macro export_to_ftp(dsn= ,outfile_name= );

Filename MyFTP ftp "&outfile_name." 
HOST='ftp.site.com'
                 cd= "&DATA_STRM/QC" 
                  USER=&ftp_user.
                  PASS=&ftp_pass.;

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

%export_to_ftp(dsn=lib1.dataset ,outfile_name=dataset.csv);

But couldn't use it to export SAS dataset. Can anybody please help me.

Thank you!

Answer 1

PROC EXPORT is not used to export SAS datasets, it's used to convert SAS datasets to other formats. You normally wouldn't use the FTP filename method to transfer SAS datasets; you would either use SAS/CONNECT if you are intending to transfer from one SAS machine to another (if you license SAS/CONNECT and want help with this, please say so), or use normal (OS) FTP processes to transfer the file. It is technically possible to use the FTP filename method to transfer a SAS file (as a binary file, reading then writing byte-by-byte) but that's error-prone and overly complicated.

The best method if you're using SAS to drive the process is to write a FTP script in your OS, and call that using x or %sysmd, passing the filename as an argument. If you include information about your operating system, something could be easily drawn up to help you out.

Note: if you're on a server, you need to verify that you have 'x' permission; that's often locked down. If you do not, you may not be able to run this entirely from SAS.

Answer 2

As Joe says, you do not use PROC EXPORT to create a file to be transferred using FTP. The safest way to exchange SAS datasets is to use PROC CPORT to create a transport file. Here is a modified version of your original macro:

%macro export_to_ftp(dsn= ,outfile_name= );

%let DBMS=%UPCASE(%SCAN(&outfile_name.,2,.));

%if &DBMS ne CSV and &DBMS ne TXT and &DBMS ne CPT %then %do;
   %put &DBMS is not supported.;
   %goto getout;
   %end;

%if &DBMS=CPT %then %do;
filename MyFTP ftp "&outfile_name." 
   HOST='ftp.site.com'
   cd= "&DATA_STRM/QC" 
   USER=&ftp_user.
   PASS=&ftp_pass.
   rcmd='binary';
PROC CPORT DATA= &dsn. 
     FILE = MyFTP;
RUN;
%end;

%else %do;
filename MyFTP ftp "&outfile_name." 
   HOST='ftp.site.com'
   cd= "&DATA_STRM/QC" 
   USER=&ftp_user.
   PASS=&ftp_pass.
   rcmd='ascii';

PROC EXPORT DATA= &dsn. 
     OUTFILE= MyFTP 
     DBMS= &dbms REPLACE; 
RUN; 
%end;

filename MyFTP clear;

%getout:

%mend;

%export_to_ftp(dsn=lib1.dataset ,outfile_name=dataset.csv);
%export_to_ftp(dsn=lib1.dataset ,outfile_name=dataset.cpt);

By convention, this will use a file extension of cpt to identify that you want a SAS transport file created. Whoever receives the file would use PROC CIMPORT to convert the file back to a SAS dataset:

filename xpt 'path-to-transport-file';
proc cimport data=dataset infile=xpt;
run;
filename xpt clear;

Note that SAS transport files should be transferred as binary files; the other two formats are text files; hence the different filename statements.

One of many advantages of using PROC CPORT is that the entire data set is copied, including any indexes that may exist. Also, you are protected against problems related to using the data set on operating systems different from the one that created it.

Answer 3

What OP requires is a Binary transfer to the FTP server. That can be done via a data step.

filename ftpput ftp "<full name of your file with ext>" cd='<DIR>' user='<username>' pass='<password>' host='<ftp host>' recfm=s debug; /*ftp dir stream connection*/
filename myfile '/path/to/your/library/dsfile.sas7bdat' recfm=n; /*local file*/

/*Binary Transfer -- recfm=n*/
data _null_;
n=1;
infile myfile nbyte=n;
input;
file ftpput;
put _infile_ @@;
run;

You can tweak the input/put statements to your specifications. But otherwise, this works for me.

I guess, you can also try the fcopy function with the above filenames. That should work too, with binary transfers.

options nonotes; /*do not want the data step or ftp server notes*/
data _null_;
fcop=fcopy("myfile","ftpput");
if fcop = 0 /*Success code*/ then do;
    put '|Successfully copied src file to FTP!|';
end;
else do;
    msg=sysmsg();
    put fcop= msg=;
end;
run;
options notes;

Answer 4

Looking at your code, you seem to have forgotten the quotes (") around &ftp_user. and &ftp_pass. Otherwise your code looks okay to me.

If that does not do the trick, some error message would come in handy.

Also note that your use of scan to determine the dbms is tricky: what if a future filename has (multiple) dots in it? you are better of putting -1 (part after last dot) instead of 2 (part after second dot) as a parameter to your scan function.

Answer 5

I would actually do it the other way around Export your file locally and then upload it with the FTP program something like this: (note I used CSV.. but please use what ever file format) You may need to edit that a little more but the base logic is there

%macro export_to_ftp(dsn= ,outfile_name= );
PROC EXPORT DATA= &dsn. OUTFILE= &file_to_FTP..CSV DBMS=%SCAN(&outfile_name.,2,.) REPLACE; 
RUN; 

Filename MyFTP ftp  "c:\FTP_command.bat"
put "ftp &ftp_user.:&[email protected] "
              put   "cd   &DATA_STRM./QC" 
                 put "c:\&file_to_FTP..CSV ";

 x  "c:\FTP_command.bat";
%mend;

%export_to_ftp(dsn=lib1.dataset ,outfile_name=dataset.csv);