DATA Step, Macro, Functions and more

Read data from SFTP

Reply
Occasional Contributor
Posts: 7

Read data from SFTP

[ Edited ]

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

 

Super User
Posts: 23,224

Re: Read data from SFTP

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

Occasional Contributor
Posts: 7

Re: Read data from SFTP

[ Edited ]

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.

Super User
Posts: 9,855

Re: Read data from SFTP

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...

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 7

Re: Read data from SFTP

Posted in reply to KurtBremser

Thanks Kurt. It will be helpful Smiley Happy 

 

Super User
Posts: 9,855

Re: Read data from SFTP

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 14

Re: Read data from SFTP

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 !!!!!

Super User
Posts: 9,855

Re: Read data from SFTP

@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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 14

Re: Read data from SFTP

Posted in reply to KurtBremser

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 ;-)

Ask a Question
Discussion stats
  • 8 replies
  • 212 views
  • 4 likes
  • 4 in conversation