BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nedaha
Fluorite | Level 6

Hi there,

 

I'm new to SAS and I need to export or some excel file on a remote server using sftp.

 

Currently I use the following command to create the files on a shared drive!

PROC EXPORT DATA = Final_data OUTFILE= "\\testfolder\DailyReport.xls"
DBMS=EXCEL REPLACE LABEL; SHEET="Data"; RUN;

 

However the new approach is using sftp for the security purposes.

My first question is, is it possible to use sftp to create files on the remote server?

If not, I'm thinking of creating files on local server and move them using sftp to the destination server. So my second question is what is the command to transfer all files of one folder to the destination server using sftp and private key.

 

Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
nedaha
Fluorite | Level 6

I just moved the options before the host and it fixed the issue!

 

filename outfile sftp 'test.csv' CD ='/Class/'  options="-p = 1000 -i = c:/PrivateKey.ppk" host="IPAddress" user="UserName"  DEBUG  ;

PROC EXPORT
Data = info /* This is the dataset name */
OUTFILE= outfile
DBMS=csv REPLACE;
run;

 

View solution in original post

3 REPLIES 3
andreas_lds
Jade | Level 19

The filename statement [https://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003041481.htm] can establish a connection to a sftp-server.

 

filename narf sftp "DailyReport.xls" host="..." ...;

proc export outfile=narf ...;
nedaha
Fluorite | Level 6

Thanks for the resources Ansreas.

I review the filename statement help and create the following code.

 

filename outfile sftp 'test.csv' CD ='/Class/' host="IPAddress" user="UserName" options='-p = 1000 -i = c:/PrivateKey.ppk'  DEBUG  ;
run;
PROC EXPORT 						
Data = info /* This is the dataset name */
	OUTFILE= outfile
	DBMS=csv REPLACE;
run;

After the code is ran the following error occured.

 

 

NOTE: psftp: unknown option "-p"
try typing "psftp -h" for help
NOTE: cd /Class/
ERROR: .

 

The version of sas is 9.3

The source os is windows and sftp server is unix.

I also tried -oport instead of -p but it didn't work! 

 

If I remove the port option I'll see the following error!

NOTE: PuTTY Secure File Transfer (SFTP) client
Release 0.68
Usage: psftp [options]
      [user@]host
Options:
  -V        print version information and exit
  -pgpfp    print
      PGP key fingerprints and exit
  -b file   use specified batchfile
  -bc       output
      batchfile commands
  -be       don't stop batchfile processing if errors
  -v
      show verbose messages
  -load sessname  Load settings from saved session
  -l user
      connect with specified username
  -P port   connect to specified port
  -pw passw
      login with specified password
  -1 -2     force use of particular SSH protocol
      version
  -4 -6     force use of IPv4 or IPv6
  -C        enable compression
  -i key
         private key file for user authentication
  -noagent  disable use of Pageant

      -agent    enable use of Pageant
  -hostkey aa:bb:cc:...
            manually specify a
      host key (may be repeated)
  -batch    disable all interactive prompts
  -proxycmd
      command
            use 'command' as local proxy
  -sshlog file
  -sshrawlog file

               log protocol details to a file

ERROR: Public Key Authentication is required, Password validation is not supported.
ERROR: For further debugging assistance, insert the PSFTP verbose parameter "options="-v " and
the "debug" option on the Filename sftp statement and review the SAS log.
ERROR: .

Any suggestion? 

 

Thanks so much!

 

 

nedaha
Fluorite | Level 6

I just moved the options before the host and it fixed the issue!

 

filename outfile sftp 'test.csv' CD ='/Class/'  options="-p = 1000 -i = c:/PrivateKey.ppk" host="IPAddress" user="UserName"  DEBUG  ;

PROC EXPORT
Data = info /* This is the dataset name */
OUTFILE= outfile
DBMS=csv REPLACE;
run;

 

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
  • 3 replies
  • 17391 views
  • 2 likes
  • 2 in conversation