topic Re: How do I transfer csv files from Sas server location to DB server location in SAS Programming

How do I transfer csv files from Sas server location to DB server location

samgautam007 — Thu, 23 Nov 2017 02:14:37 GMT

Hello Everyone,

I am stuck at a point where I need to transfer CSV files generated from sas to db server location. Below are the details:

I am using Sas 9.4 and below process will be run on batch via control M.

SAS server: 10.20.30.40

Csv file location: /sasdata/test.csv

Db server: 10.20.30.50

Csv transfer location: /test1/test2/test.csv

I have tried "filename outfile sftp" but stuck at primary key think (no knowledge about this).

Below is the code that I tried:

Filename outfile sftp 'test.csv' cd='/test1/test2/'

Host='10.20.30.50' options="-oport=22" user="username" debug;

Proc export data=test outfile=outfile dbms=csv replace ;run;

Error: host key verification failed. Couldn't read packet: connection reset by peer.

Please provide your valuable suggestions.

Thanks

Re: How do I transfer csv files from Sas server location to DB server location

Kurt_Bremser — Thu, 23 Nov 2017 08:51:09 GMT

In order for filename sftp to work, you need to set up passwordless authentication and initialize the known_hosts entry.

On your SAS server, log on with your SAS user.

Use ssh-keygen to create a public/private key pair. This will create files in .ssh:

id_dsa
id_dsa.pub
id_rsa
id_rsa.pub

(rsa or dsa depending on which method you chose)

Change to directory .ssh

Enter

sftp username@10.20.30.50

You will be prompted to accept a host verification key. After doing that, 10.20.30.50 will be in your .ssh/known_hosts file with this key. In the future, ssh will check if the target host is still the same, to prevent a MITM attack.

After logging on (here you still need the password for username on 10.20.30.50), change to .ssh

use the put subcommand to copy your .pub file to the server, change the target name, so you do not accidentally overwrite a file already there.

log off, and log on with ssh

change to .ssh

Now, either rename your copied file to

authorized_keys

or append its contents to the file, if it already exists:

cat authorized_keys your_file > authorized_keys.new
rm authorized_keys
mv authorized_keys.new authorized_keys

While doing this, make sure that all files and the .ssh directory are read/writable only by the user

Log off and try ssh username@10.20.30.50 again. You should log on without being prompted for a password.

Now you can use filename sftp on that server from SAS.

Re: How do I transfer csv files from Sas server location to DB server location

samgautam007 — Mon, 27 Nov 2017 01:46:48 GMT

Thank so much KurtBremser ... it worked smoothly.

Regards,

Sandeep