How to connect with Rave database and download SAS dataset

[Leo9]

Hi Everyone,
I need to connect with Medidata rave database and download SAS dataset on to my company's server. I have never done something like this and have very little clue where to begin with. Here are the options I found when searching web.
1) Making a connection using filename and ftp methods. I need to download zip file containing SAS dataset and I am not sure how to achieve it using filename and ftp method.

2) SAS connect: I am not sure how to use this.
3) FTP client : Making a connection via FTP client and downloading dataset. I am looking for more automated message like running a SAS program to extract dataset. I am not sure how to set up FTP client to achieve this.

Any input is appreciated.

[ChrisNZ]

What's in the zip file? A SAS data set or a CSV file?

1) Is what you need to do if you can. Many examples of FILENAME FTP on the web and here.

2) SAS/CONNECT will not help here: it is used for SAS sessions to talk to each other

3) is of no use either if you want automation, but is useful to test the process before you automate it.

[Kurt_Bremser]

Maxim 3 (Know Your Data) is also valid for data retrieved from external sources. So you should first use a simple client (e.g. WinSCP) to download the file to your desktop and open it with a ZIP utility. Then your further action depends on what you find.

If the download involves some kind of authentication, do NOT use standard FTP, as it transmits users/passwords in the clear (the server should not provide the FTP port for that, anyway). FTP is only good for anonymous connections.

Do you have XCMD enabled on your SAS?

[Leo9]

Thank you! I forgot to mention we are using Windows SAS not UNIX. I am not sure what XCMD is but it seems it valid for UNIX only ? 

[Kurt_Bremser]

XCMD is a SAS system option that controls if you can run external commands; it is available on all supported platforms. I asked about it to know if you can make use of an external s(ftp) and/or unzip command.

[Leo9]

Thank you. I tried to connect to ftp server using FileZilla and I can connect fine without issue. I do not think I have XCMD option.

Since ftp connection working fine using filezilla, is there way to 1) use this functionality and some kind of SAS program to do on demand extract running SAS code 2) or connect via SAS filename ftp option ? I tried SAS filename ftp option but it is not working in my case as I am getting invalid user error. Which is strange because I could connect to ftp server using filezilla with same user name.  

Thanks in advance. 

[Kurt_Bremser]

Which protocol and port did you use with Filezilla?

[Leo9]

SSL/TLS

[Kurt_Bremser]

Have a look at FILENAME SFTP for downloading the file. If you manage to configure the access, you can use it with RECFM=N to download the file as a binary stream. Then you can open the local copy with FILENAME ZIP.

But I prefer to use the external sftp for file transfer via one of the "system exit" methods SAS provides, as it has proven to be more robust with larger files and network connections with inconsistent throughput.

[Kurt_Bremser]

PS if this is publicly available data, provide the URL, so we can play around with the process.

[caesee]

Hi

Any progress with rave databse and filename ftp download.

here`s my code 
filename _bcin ftp 'xxx.zip'
cd='xxxxx/sasondemand'
host='ftp01.ftp.mdsol.com'
PORT=21
user='' pass = ''
debug;

there is alway an error:ERROR: Cannot load SSL support.

[ChrisNZ]

I never used this but you seem to be using FTPS, which might require more parameters? See https://support.sas.com/kb/56/154.html