topic Re: export dataset to ftp in SAS Programming

export dataset to ftp

pbrookha — Thu, 27 Jun 2019 15:45:40 GMT

I am having a difficult time figuring out how to export a SAS dataset into a specific directory on an sfs site. I am currently attempting to use filename ftp logic but am unsuccessful.

Does anyone have an example of successful logic?

Thanks

Re: export dataset to ftp

ballardw — Thu, 27 Jun 2019 15:55:56 GMT

Can you share the code and messages from the log for an attempt? XXXX out such sensitive things as user and password, if used.

"Unsuccessful" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

You may want to just use an X command or an operating system command to send things using your FTP program and a FILENAME has a bit more overhead involved than just sending a file.

Re: export dataset to ftp

pbrookha — Thu, 27 Jun 2019 16:07:15 GMT

%let mywk=%sysfunc(getoption(WORK));
%let target_folder=/###/####/; 
%let source_folder=/sas/shared/#####/;
%let source=ARRIVALS.xls; 

data _null_;
            file "&mywk/get_aff.cmd";
            put "user #####";
            put "cd &target_folder." ;
            put "lcd &source_folder" ;
            put "put &source.";
            put "ls -l";
            put "quit";

filename doftp pipe "sftp  -v  sfs.company.com  < &mywk/get_aff.cmd";
;
run;

The corresponding log if here:

NOTE: <<< 230 User ###### logged in.
NOTE: >>> OPTS UTF8 ON
NOTE: <<< 200 OPTS command successful
NOTE: >>> OPTS UTF8 NLST
NOTE: <<< 501 OPTS: parameters are incorrect
NOTE: >>> OPTS UTF-8 NLST
NOTE: <<< 501 OPTS: parameters are incorrect
NOTE: >>> PASV
NOTE: <<< 227 Entering Passive Mode (######)
NOTE: >>> TYPE A
NOTE: <<< 200 TYPE command successful
NOTE: >>> CWD /Distribution/WVR_Associate_Sites/OVI
NOTE: <<< 250 CWD command successful
NOTE: >>> PWD
NOTE: <<< 257 "/####/#####/" is current directory
NOTE: >>> PBSZ 0
NOTE: <<< 200 PBSZ command successful
NOTE: >>> PROT P
NOTE: <<< 200 PROT command successful
NOTE: >>> RETR /sas/shared/####/ARRIVALS.xls
NOTE: <<< 550 RETR failed: File could not be found
ERROR: Physical file does not exist, /sas/shared/####/ARRIVALS.xls.

Re: export dataset to ftp

pbrookha — Thu, 27 Jun 2019 16:09:25 GMT

filename Table ftp '/sas/shared/#####/ARRIVALS.xls' new cd=&directory 
user="#####" pass="#####" host=&host AUTHTLS recfm=v debug passive; 

data WORK.ARRIVALS;
infile Table truncover;
input filename $char1000.;
put _infile_;
run;
quit;

I have also tried this code after reading up on some forums from SAS and this ran successful but did not produce any results or log notes.

Re: export dataset to ftp

Kurt_Bremser — Thu, 27 Jun 2019 16:16:35 GMT

This data step

data WORK.ARRIVALS;
infile Table truncover;
input filename $char1000.;
put _infile_;
run;

tries to read data from the ftp file reference, store it in variable filename in dataset arrivals, and also writes the contents of each input line to the SAS log.

Since fileref table refers to an Excel file, this won't do you any good, since Excel files are not text files.

Re: export dataset to ftp

ballardw — Thu, 27 Jun 2019 17:39:27 GMT

@pbrookha wrote:
%let mywk=%sysfunc(getoption(WORK));
%let target_folder=/###/####/; 
%let source_folder=/sas/shared/#####/;
%let source=ARRIVALS.xls; 

data _null_;
            file "&mywk/get_aff.cmd";
            put "user #####";
            put "cd &target_folder." ;
            put "lcd &source_folder" ;
            put "put &source.";
            put "ls -l";
            put "quit";

filename doftp pipe "sftp  -v  sfs.company.com  < &mywk/get_aff.cmd";
;
run;
The corresponding log if here:

NOTE: <<< 230 User ###### logged in.
NOTE: >>> OPTS UTF8 ON
NOTE: <<< 200 OPTS command successful
NOTE: >>> OPTS UTF8 NLST
NOTE: <<< 501 OPTS: parameters are incorrect
NOTE: >>> OPTS UTF-8 NLST
NOTE: <<< 501 OPTS: parameters are incorrect
NOTE: >>> PASV
NOTE: <<< 227 Entering Passive Mode (######)
NOTE: >>> TYPE A
NOTE: <<< 200 TYPE command successful
NOTE: >>> CWD /Distribution/WVR_Associate_Sites/OVI
NOTE: <<< 250 CWD command successful
NOTE: >>> PWD
NOTE: <<< 257 "/####/#####/" is current directory
NOTE: >>> PBSZ 0
NOTE: <<< 200 PBSZ command successful
NOTE: >>> PROT P
NOTE: <<< 200 PROT command successful
NOTE: >>> RETR /sas/shared/####/ARRIVALS.xls
NOTE: <<< 550 RETR failed: File could not be found
ERROR: Physical file does not exist, /sas/shared/####/ARRIVALS.xls.

That line in red is pretty much it.

I would suggest that your SOURCE_FOLDER macro variable should be a fully qualified path from a disk mount point. The default starting with /sas/ probably meant the FTP program was using its default execution location and that does not have a subfolder named /sas/.