Using SAS to download files from a Unix box using SFTP…

Using SAS to download files from a Unix box using SFTP…

One can use these techniques…

a) SAS version 9.2 and above – use FILENAME SFTP statement
b) SAS Version 9.1 and below on a PC – SAS does not have a straight forward method…I will discuss an alternative method…

Discuss these methods in details…

a) SAS version 9.2 and above – use FILENAME SFTP statement

b) Alternative method – We can use the open source psftp.exe program to download the file to your local machine. Create a MS DOS batch file with all the logical commands to change the directories and then download the file. Then use call PSFTP Program with the –b option pointing to the batch file that you have just created.

1) Download PSFTP.exe and put in a folder convenient to you, for e.g. Desktop..
2) Gather the credentials that you need to use to establish the server connection…i.e.

• username/password/port/server
• Put together all the sftp commands that you would use for establishing the connection, changing the  directories local or remote.
• Fill the highlighted %let macro statement portion with your information.

3) Run the macro program.

%macro sftpfiles(file);

/******* Download the files

to the Source Data dir */

data _null_;

file "&BatchDir\gensftpbatchcommand.bat";

put "dir";

put 'cd "&remotedir"';

put 'lcd ''"'"&localdir"'"';

put "mget &file";

put "quit";

run;

%let RC_sFTP=0; /* Initialize RC_sftp to 0 */

data _null_;

rc=system("&psftpexeloc. &user.@&server -P &port -pw &pwdsftp -b %str (%"&BatchDir.\gensftpbatchcommand.bat%")");

put rc=;

if rc ne 0 then call symput('RC_sFTP',1);

run;

%mend;

%let BatchDir= C:\Documents and

Settings\SASTechies\Desktop\CMD; /*Directory where you

want to place the batch file that you would generate */

%let psftpexeloc=C:\Documents

and Settings\SASTechies\Desktop\psftp.exe; /*Location

of the psftp.exe */

%let user=username; /*Username*/

%let pwdsftp=password; /* Password to the server */

%let server=server; /*server name or ip */

%let port=21; /* port on the server to connect to */

%let remotedir = /some/dir/on/unix/box;/* remote dir on the server where the file is hosted */

%let localdir = C:\Documents and Settings\SASTechies\Desktop\local; /* destination dir on your system */

%sftpfiles(yourfile.txt);

Some more info on the PSFTP syntax