topic Re: How do I use SAS eg to import the only CSV file in SFTP folder in SAS Programming

How do I use SAS eg to import the only CSV file in SFTP folder

hotprawnstar — Tue, 17 Jan 2017 09:11:54 GMT

Hi There,

I'm trying to read a CSV file from a SFTP folder. The customers have instructed that the filename would vary day to day due to a datetime stamp in the file name. However the file that needs to be imported into SAS will be the only file sitting in the SFTP folder everyday.

Is there a way to get SAS to import the only file that is sitting in this SFTP folder instead of having to specify the filename explicitly? I'm on version 7.1 btw.

Thanks!

Re: How do I use SAS eg to import the only CSV file in SFTP folder

RW9 — Tue, 17 Jan 2017 09:16:01 GMT

Personally, if I was paying them to send me data I would be telling them how to provide the data. Hence my import agreement would define structure, content, and delivery details which they would then sign and abide by. As part of the delivery details I would instruct them to name the file the same each time, without filename modifications, so you wouldn't need to worry about that (and your version control system would be able to handle this text file).

Re: How do I use SAS eg to import the only CSV file in SFTP folder

Kurt_Bremser — Tue, 17 Jan 2017 09:22:40 GMT

I don't think it's possible to use the SAS directory functions over SFTP.

So I'd use an external SFTP utility to extract all files found to a local directory (clean it out first!), and then use the SAS directory functions to retrieve the filename.

Re: How do I use SAS eg to import the only CSV file in SFTP folder

Damo — Tue, 17 Jan 2017 16:39:51 GMT

Hi,

Did you try to use the LS option coming with the SFTP method of FILENAME?

From the SAS® 9.4 Statements: Reference, Fifth Edition and its FILENAME Statement, SFTP Access Method chapter, we have:

Other options such as LSFILE, etc. might help you too.

From that same page, you'll also have access to Exemple 4: Reading Files from a Directory on the Remote Host.

This can be done after you retrieved the file name:

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;

Hope that helps.

(Sorry I can't test, i don't have a SFTP available).

Cheers,
Damo