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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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

[ Edited ]

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


Accepted Solutions
Solution
‎11-26-2017 08:47 PM
Super User
Posts: 10,280

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

Posted in reply to samgautam007

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
‎11-26-2017 08:47 PM
Super User
Posts: 10,280

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

Posted in reply to samgautam007

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.

 

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

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

Posted in reply to KurtBremser

Thank so much 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 255 views
  • 1 like
  • 2 in conversation