SFTP Private key authentication SAS Code throws ERROR

[JJP1]

Hi Team,

iam trying to read the file from remote server by making use of private key authentication in SAS code.but iam getting error.please help

%let host = XXXXXXXXXXXXXXXXXXXXXXXX.com;
%let ftpuser = YYYYYYYYYY;
filename ftpin sftp "CCCCCCC..xls" recfm=f host="&host" user="&ftpuser"
recfm=f cd= "/AAA/ALL/SSS"
options="-oPort=40022 -i /hhh/uuuu/uuuu/.ssh/ggg.key -o StrictHostKeyChecking=no";
data _null_;
infile ftpin;
/* file ftpout;*/
input;
put _infile_;
run;

error log:

ERROR: An exception has been encountered.
Please contact technical support and provide them with the following traceback information:

The SAS task name is [OBJECT_E]
Segmentation Violation

Traceback of the Exception:

0xf548(?)
yxrasgn+0x294
yxrasgn+0x38 -- bridge stub
ytlibf+0xd80
yasany+0x4c8
yasany2+0x188
yttknx+0x1480
yalexec+0xf98
yalinln+0x4d8
yalproc+0x234
yalproc+0x38 -- bridge stub
sasxobj+0x3d8
vvtentr+0x194
_pthread_body+0xf0
 

[Kurt_Bremser]

Open a track with SAS technical support. When SAS throws a segmentation violation (a memory access error, caused either by a null pointer or pointing to unallocated memory), it encountered a condition it is unprepared for.

Does the ERROR happen immediately, or after some time running the sftp?

[JJP1]

After sometime just 2seconds i will get below dialog box and then i click ok to see the log.please help .

why it is the causing issue.is it because of SAS EG application issue or any other SFTP issue

[ballardw]

Note that INFILE in a data step basically expects a TEXT file of some form, plain text, delimited text or similar to read the file. XLS files are NOT text and I would not expect to read one with a data step.

You might try Proc Import to generate a data set.

As an example create a small xls file locally and try that data step on that file and see what happens.

[JJP1]

you mean SFTP will not work for excel sheet....Would you please point me where i am exactly going wrong and where i need to modify the code to work this please.i did not clearly understood please.

[andreas_lds]

---

@JJP1 wrote:

you mean SFTP will not work for excel sheet....Would you please point me where i am exactly going wrong and where i need to modify the code to work this please.i did not clearly understood please.

---

The infile statement can't be used to read an excel-file regardless of the place where it is stored (local file-system, unc-path, sftp-server). Excel files are read either by defining a libref with the libname statement or by proc import.

[JJP1]

iam sorry,but we all  ready reading the excel file using infile statement and it works fine .

as part of my change i just need to change the connections,so that it will take file from SFTP server

[Kurt_Bremser]

Look at the code. It's nothing but an sftp transfer complicated by using SAS. It uses recfm=f to read blocks and write blocks to the outfile.

There might be an issue when the program reads the last "record" of the binary file and finds less than the default 254 bytes.

But since it ends in a segmentation violation, it's a clear case for SAS TS IMO.

[JJP1]

Thnaks @Kurt. So what does it mean "clear case for SAS TS IMO."Iam sorry and iam new to SFTP SAS connection as i never heard of SAS TS IMO.

So would you please suggest how can i overcome this error please

[Kurt_Bremser]

Go to support.sas.com, scroll down, and select Technical Support. On the next page, go to "3 Contact Technical Support", and on the next page, select Create a Track.

[JJP1]

Thanks @Kurt_Bremser 

[Kurt_Bremser]

BTW, I think it would be much better to run the whole sftp from the commandline; once it works from there, you can use SAS to create the commandline call and run it with a filename pipe.

It is also advised that you set up automatic passwordless authentication to all sftp targets first by properly setting your entries in your .ssh directory. The aim is to get this working:

sftp sourceuser@sourcehost:sourcepath/sourcefile targetuser@targethost:targetpath/targetfile

without having to enter any further options.

[JJP1]

The connection was successful when i run as command line with same conection details on unix server.

So it was successfully connected and then  i am trying now in SAS please.any suggestions please.

.ssh set was done @Kurt_Bremser and privatekey authentication was successfull on unix SAS server

[Kurt_Bremser]

I have to state that I do not use SAS FILENAME SFTP at all in our environment, as I have found it to be unreliable.

That is because SAS uses an external sftp client (with FILENAME FTP, the whole logic is built into SAS itself) to create a local copy, and reads that copy after a given delay. If the sftp is slow, SAS can "overtake" the sftp and stop reading prematurely.

So I always run the sftp as an external command:

filename oscmd pipe "sftp user@host:filename %sysfunc(pathname(work))/localname 2>&1";

data _null_;
infile oscmd;
input;
put _infile_;
run;

filename in "%sysfunc(pathname(work))/localname";

data want;
infile in;
/* further processing */

[JJP1]

Thank you @Kurt_Bremser .

I was running the code you suggested and it ran for 1 minutes and i thought it was successfull.thanks @kurt

but iam getting below note now and not the old error

connect to host XXXX.com port 22: Connection timed out
Connection closed

where can i include the port no and option keyword in the sample code you provided please