BookmarkSubscribeRSS Feed
ruchir_m
Fluorite | Level 6

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

 

8 REPLIES 8
Reeza
Super User

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

ruchir_m
Fluorite | Level 6

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.

ruchir_m
Fluorite | Level 6

Thanks Kurt. It will be helpful 🙂 

 

Kurt_Bremser
Super User

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

PSNn
Obsidian | Level 7

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

Kurt_Bremser
Super User

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

PSNn
Obsidian | Level 7

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 😉

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1358 views
  • 4 likes
  • 4 in conversation