topic Re: Read data from SFTP in SAS Programming

Read data from SFTP

ruchir_m — Wed, 22 Nov 2017 05:36:13 GMT

can we read data from SFTP through SAS . I have below approach ,need suggestion for better .

Re: Read data from SFTP

Reeza — Tue, 21 Nov 2017 06:07:08 GMT

What’s your definition of better? Can you include the information directly in the post, attachments don’t work for everyone.

Re: Read data from SFTP

Kurt_Bremser — Tue, 21 Nov 2017 06:47:36 GMT

SAS provides filename sftp to access files via the ssh protocol, see here: http://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.2&docsetId=lestmtsglobal&docsetTarget=p0xln1fiwsr340n1xxf4mkmfxp6f.htm&locale=en

Re: Read data from SFTP

ruchir_m — Wed, 22 Nov 2017 05:39:54 GMT

Yeah sure, so definition is take less time and easy to implement in SAS.

I have to get sftp files to sas system and then perform ETL. I can write unix script for the same but team what to do this operation with sas only.

Re: Read data from SFTP

ruchir_m — Wed, 22 Nov 2017 05:41:23 GMT

Thanks Kurt. It will be helpful 🙂

Re: Read data from SFTP

Kurt_Bremser — Wed, 22 Nov 2017 06:55:00 GMT

In short:

- you need an external sftp; other than with filename ftp, which is programmed into SAS, filename sftp relies on an installed external sftp client.

- you need to set up passwordless authentication; SAS expects that sftp username@host will automatically log on; usually this is done with public/private key pairs

Re: Read data from SFTP

PSNn — Wed, 22 Nov 2017 07:33:23 GMT

I try again:....

Warning: I have not tested this functionality lately, but last time i tried to use it I gave up. This Interface is probably the worst **bleep** SAS Institute has ever made (sorry for the Words, but this i true). I tried to get SAS to fix it With the help of my former collegue in SAS (yes, I have worked for SAS earlier for many years). After a lot of work and dokumentation we actually gave up. There was absolutely no will at SAS in Cary to even consider to do anything about it. The typicall answer from them was: "What do you expect ?". We tried to explain that we expect at least a warning or an error Message when Things do not work. But, no, we had no chance.

So what is (I assume it still is) wrong With this Interface ?

It Works somewhat like this:

It starts an external process that Writes the data to a local file.

After WAIT_MILLISECONDS (an option you can set) SAS starts Reading the local file without checking that the external fetch is done.

In real world you use SFTP to fetch date from outside Your Company, and the line speed is mostly not as intenal speed. The SAS read of the file will be a lot quicker than the external fetch of the file and SAS will read Down and conclude that it has reached the end of the file, and tell you the statistics and make you think everything is OK. However, if you run exactly the same code again and again you will get fifferent result every time, AND NO WARNINGS! Your only Choice is to adjust WAIT_MILLISECONDS, but as you never know the size of the file..... you are stuck,, Setting WAIT_MILLISECONDS to a big enough value will make evey sigle file fetch take at leat 2 times WAIT_MILLISECONDS (or maybe it was 3 times) as WAIT_MILLISECONDS is Applied to every single operation in the process (like also logging in). So... Be careful.. Check if this really is reliable in Your situation!!!

I have only tested this under Unix/Linux. I do not know how this woulld behave under Windows.. It's possible that you get an error Message under Windows since I assume Windows is not (always) happy about Reading from a file that is currently opened for Write, Unix/Linux does not care and that's why there is absolutely no indications of any problems... As I said, I had to go for other solutions to fetch files With SFPT. The SAS Interface is (or at least was) a really bad joke. TEST IT before using it !!!!!

Re: Read data from SFTP

Kurt_Bremser — Wed, 22 Nov 2017 07:55:26 GMT

@PSNn is that still so in SAS 9.4? I noticed that unreliable behaviour in 9.2 (when filename sftp was first added), and programmed a workaround that does the sftp externally and then reads from the local file, so that the file is transmitted completely before SAS starts reading. IIRC, there was a SAS TS track by me regarding this issue. If it's still not fixed, shame on SAS, as this descends to Microsoft-typical depths of totally unacceptable design and coding habits.

Re: Read data from SFTP

PSNn — Wed, 22 Nov 2017 08:09:05 GMT

I work another Place now. I totally gave up the FILENAME SFTP, and like you I have a Linux solution which I use/call from SAS (and that Works great). I cannot be 100% sure, but I do believe that we had 9.4 the other Place. At least we had 9.3. If you like, I might be able to find the answer: I'm sure my friend at the local SAS Office sits on all the conversation we had. I had to leave all my email conversations behind when I started working for the competitor 😉