topic Move files between server using SFTP in SAS Programming

Move files between server using SFTP

nedaha — Tue, 18 Apr 2017 20:18:04 GMT

Hi there,

I'm new to SAS and I need to export or some excel file on a remote server using sftp.

Currently I use the following command to create the files on a shared drive!

PROC EXPORT DATA = Final_data OUTFILE= "\\testfolder\DailyReport.xls"
DBMS=EXCEL REPLACE LABEL; SHEET="Data"; RUN;

However the new approach is using sftp for the security purposes.

My first question is, is it possible to use sftp to create files on the remote server?

If not, I'm thinking of creating files on local server and move them using sftp to the destination server. So my second question is what is the command to transfer all files of one folder to the destination server using sftp and private key.

Thanks!

Re: Move all files of one folder using sftp

andreas_lds — Tue, 18 Apr 2017 07:56:04 GMT

The filename statement [https://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003041481.htm] can establish a connection to a sftp-server.

filename narf sftp "DailyReport.xls" host="..." ...;

proc export outfile=narf ...;

Re: Move all files of one folder using sftp

nedaha — Tue, 18 Apr 2017 20:16:05 GMT

Thanks for the resources Ansreas.

I review the filename statement help and create the following code.

filename outfile sftp 'test.csv' CD ='/Class/' host="IPAddress" user="UserName" options='-p = 1000 -i = c:/PrivateKey.ppk'  DEBUG  ;
run;
PROC EXPORT 						
Data = info /* This is the dataset name */
	OUTFILE= outfile
	DBMS=csv REPLACE;
run;

After the code is ran the following error occured.

NOTE: psftp: unknown option "-p"
try typing "psftp -h" for help
NOTE: cd /Class/
ERROR: .

The version of sas is 9.3

The source os is windows and sftp server is unix.

I also tried -oport instead of -p but it didn't work!

If I remove the port option I'll see the following error!

NOTE: PuTTY Secure File Transfer (SFTP) client
Release 0.68
Usage: psftp [options]
      [user@]host
Options:
  -V        print version information and exit
  -pgpfp    print
      PGP key fingerprints and exit
  -b file   use specified batchfile
  -bc       output
      batchfile commands
  -be       don't stop batchfile processing if errors
  -v
      show verbose messages
  -load sessname  Load settings from saved session
  -l user
      connect with specified username
  -P port   connect to specified port
  -pw passw
      login with specified password
  -1 -2     force use of particular SSH protocol
      version
  -4 -6     force use of IPv4 or IPv6
  -C        enable compression
  -i key
         private key file for user authentication
  -noagent  disable use of Pageant

      -agent    enable use of Pageant
  -hostkey aa:bb:cc:...
            manually specify a
      host key (may be repeated)
  -batch    disable all interactive prompts
  -proxycmd
      command
            use 'command' as local proxy
  -sshlog file
  -sshrawlog file

               log protocol details to a file

ERROR: Public Key Authentication is required, Password validation is not supported.
ERROR: For further debugging assistance, insert the PSFTP verbose parameter "options="-v " and
the "debug" option on the Filename sftp statement and review the SAS log.
ERROR: .

Any suggestion?

Thanks so much!

Re: Move all files of one folder using sftp

nedaha — Mon, 24 Apr 2017 14:44:51 GMT

I just moved the options before the host and it fixed the issue!

filename outfile sftp 'test.csv' CD ='/Class/'  options="-p = 1000 -i = c:/PrivateKey.ppk" host="IPAddress" user="UserName"  DEBUG  ;

PROC EXPORT 
Data = info /* This is the dataset name */
 OUTFILE= outfile
 DBMS=csv REPLACE;
run;