FILENAME Statement, SFTP Access Method

Enables you to access remote files by using the SFTP protocol.
Valid in: Anywhere
Category: Data Access
Syntax
Details
The Basics
SFTP Access Methods and SFTP Prompts
Comparisons
Examples
Example 1: Connecting to an SSHD Server at a Standard Port
Example 2: Connecting to an SSHD Server at a Nonstandard Port
Example 3: Connecting a Windows PUTTY Client to an SSHD Server
Example 4: Reading Files from a Directory on the Remote Host
Example 5: Using a Batch File
See Also

Syntax

FILENAME fileref SFTP 'external-file' <sftp-options>;

Arguments

fileref
is a valid fileref.
Tip:The association between a fileref and an external file lasts only for the duration of the SAS session or until you change it or discontinue it with another FILENAME statement. You can change the fileref for a file as often as you want.
SFTP
specifies the access method that enables you to use Secure File Transfer Protocol (SFTP) to read from or write to a file from any host computer that you can connect to on a network with an OpenSSH SSHD server running.
'external-file'
specifies the physical name of an external file that you want to read from or write to. The physical name is the name that is recognized by the operating environment.
Operating environment:For details about specifying the physical names of external files, see the SAS documentation for your operating environment.
Tips:If you are not transferring a file but performing a task such as retrieving a directory listing, then you do not need to specify an external filename. Instead, put empty quotation marks in the statement.

You can associate a fileref with a single file or with an aggregate file storage location.

sftp-options
specifies details that are specific to your operating environment such as file attributes and processing attributes.
Operating environment:For more information about some of these SFTP options, see the SAS documentation for your operating environment.

SFTP Options

sftp-options can be any of the following values:
BATCHFILE='path'
specifies the fully qualified pathname and the filename of the batch file that contains the SFTP commands. These commands are submitted when the SFTP access method is executed. After the batch file processing ends, the SFTP connection is closed.
Requirement:The path must be enclosed in quotation marks.
Tip:After the batch file processing ends, the SFTP connection is closed and the filename assignment is no longer available. If subsequent DATA step processing requires the FILENAME SFTP statement, then another FILENAME SFTP statement is required.
CD='directory'
issues a command that changes the working directory for the file transfer to the directory that you specify.
DEBUG
writes informational messages to the SAS log.
DIR
enables you to access directory files. Specify the directory name in the external-file argument. You must use valid directory syntax for the specified host.
Interaction:The CD and DIR options are mutually exclusive. If both are specified, SFTP ignores the CD option and SAS writes an informational note to the log.
Tips:If you want SFTP to create the directory, then use the NEW option in conjunction with the DIR option. The NEW option will be ignored if the directory exists.

If the NEW option is omitted and you specify an invalid directory, then a new directory will not be created and you will receive an error message.

HOST='host'
where host is the network name of the remote host with the OpenSSH SSHD server running.
You can specify either the name of the host (for example, server.pc.mydomain.com) or the IP address of the computer (for example, 2001:db8::).
LRECL=lrecl
where lrecl is the logical record length of the data.
Default:256
Interaction:Alternatively, you can specify a global logical record length by using the LRECL= System Option in SAS System Options: Reference.
LS
issues the LS command to the SFTP server. LS returns the contents of the working directory as records with no file attributes.
Restriction:The LS option will not display files with leading periods, for example .xAuthority.
Interaction:The LS and LSA options are mutually exclusive. If you specify both options, the LSA option takes precedence.
Tip:To return a listing of a subset of files, use the LSFILE= option in addition to LS.
LSA
issues the LS command to the SFTP server. LSA returns all the contents of the working directory as records with no file attributes.
Interactions:The LS and LSA options are mutually exclusive. If you specify both options, the LSA option takes precedence.

To display files without leading periods, for example .xAuthority, use the LS= option.

Tip:To return a listing of a subset of files, use the LSFILE= option in addition to LSA.
LSFILE='character-string'
in combination with the LS option, specifies a character string that enables you to request a listing of a subset of files from the working directory. Enclose the character string in quotation marks.
Restriction:LSFILE= can be used only if LS or LSA is specified.
Tip:You can specify a wildcard as part of 'character-string '.
Example:This statement lists all of the files that start with sales and end with sas:
filename myfile sftp '' ls lsfile='sales*.sas' 
   other-sftp-options;
MGET
transfers multiple files, similar to the SFTP command MGET.
Tip:The whole transfer is treated as one file. However, as the transfer of each new file is started, the EOV= variable is set to 1.
NEW
specifies that you want SFTP to create the directory when you use the DIR option.
Restriction:The NEW option is not available under z/OS.
Tip:The NEW option will be ignored if the directory exists.
OPTIONS=
specifies SFTP configuration options such as port numbers.
PATH
specifies the location of the SFTP executable if it is not installed in the PATH or $PATH search path.
Tip:It is recommended that the OpenSSH “SFTP” executable or PUTTY “PSFTP” executable be installed in a directory that is accessible via the PATH or $PATH search path.
RECFM=recfm
where recfm is one of two record formats:
F
is fixed-record format. Thus, all records are of size LRECL with no line delimiters.
V
is variable-record format (the default). In this format, records have varying lengths, and they are separated by newlines. Data is transferred in image (binary) mode.
Default:V
USER='username'
specifies the user name.
Requirement:The username is required by the PUTTY client on the Windows host.
Tips:The username is not typically required on LINUX or UNIX hosts when using public key authentication.

Public key authentication using an SSH agent is the recommended way to connect to a remote SSHD server.

WAIT_MILLISECONDS=milliseconds
specifies the SFTP response time in milliseconds.
Default:1,500 milliseconds
Tip: If you receive a time-out message in the log, use the WAIT_MILLISECONDS option to increase the response time.

Details

The Basics

The Secure File Transfer Protocol (SFTP) provides a secure connection and file transfers between two hosts (client and server) over a network. Both commands and data are encrypted. The client machine initiates a connection with the remote host (OpenSSH SSHD server).
With the SFTP access method, you can read from or write to any host computer that you can connect to on a network with an OpenSSH SSHD server running. The client and server applications can reside on the same computer or on different computers that are connected by a network.
Specific implementation details are dependent on the OpenSSH SSHD server version and how that site is configured.
The SFTP access method relies on default send and reply messages to OpenSSH commands. Custom installs of OpenSSH that modify these messages will disable the SFTP access method.
You must have the applicable client software installed to use the SFTP access method. The SFTP access method supports only the following SSH clients.
  • OpenSSH – UNIX
  • PUTTY – Windows
Note: Password validation is not supported for the SFTP access method.
Note: Public key authentication using an SSH agent is the recommended way to connect to a remote SSHD server.
Note: If you have trouble running the SFTP access method try to manually validate SFTP client access to an OpenSSH SSHD server without involving the SAS system. Manually validating SFTP client access without involving the SAS system will ensure that your SSH/SSHD configuration and key authentication is setup correctly.

SFTP Access Methods and SFTP Prompts

The SFTP access method supports only the following prompts. Changing the prompt will disable the SFTP access method.
  • For OpenSSH:
    sftp>
    sftp >
  • For PUTTY:
    psftp>

Comparisons

As with the SFTP get and put commands, the SFTP access method lets you download and upload files. However, this method directly reads files into your SAS session without first storing them on your system.

Examples

Example 1: Connecting to an SSHD Server at a Standard Port

This example reads a file called test.dat using the SFTP access method after connecting to the SSHD server a standard port: 
filename myfile sftp '/users/xxxx/test.dat' host="unixhost1";
data _null_;
   infile myfile truncover;
   input a $25.;
run;

Example 2: Connecting to an SSHD Server at a Nonstandard Port

This example reads a file called test.dat using the SFTP access method after connecting to the SSHD server at port 4117: 
filename myfile sftp '/users/xxxx/test.dat' host="unixhost1" options="-oPort=4117";
data _null_;
   infile myfile truncover;
   input a $25.;;
run;

Example 3: Connecting a Windows PUTTY Client to an SSHD Server

This example writes a file called test.dat using the SFTP access method after connecting a Windows PUTTY client to the SSHD server with a user ID of userid: 
filename outfile sftp '/users/xxxx/test.dat' host="unixhost1" user="userid";
data _null_;
   file outfile;
   do i=1 to 10;
      put i=;
   end;
run;

Example 4: Reading Files from a Directory on the Remote Host

This example reads the files test.dat and test2.dat from a directory on the remote host. 
filename infile sftp '/users/xxxx/' host="unixhost1" dir;
data _null_;
   infile infile(test.dat) truncover;
   input a $25.;
   infile infile(test2.dat) truncover;
   input b $25.;
run;

Example 5: Using a Batch File

In this example, when the INFILE statement is processed, the batch file associated with the FILENAME SFTP statement, sftpcmds, is executed. 
filename process sftp ' ' host="unixhost1" user="userid" 
   batchfile="c:/stfpdir/sftpcmds.bat";
data _null_;
   infile process;
run;

See Also

Barrett, Daniel J., Richard E. Silverman, and Robert G. Byrnes. 2005. “SSH, The Secure Shell: The Definitive Guide.” Sebastopol, CA: O'Reilly
Statements:
FILENAME Statement
FILENAME Statement, CATALOG Access Method
FILENAME Statement, EMAIL (SMTP) Access Method
FILENAME Statement, FTP Access Method
FILENAME Statement, Hadoop Access Method
FILENAME Statement, JMS Access Method in Application Messaging with SAS
FILENAME Statement, SOCKET Access Method
FILENAME Statement, URL Access Method
LIBNAME Statement
Copyright © SAS Institute Inc. All rights reserved.