topic Re: How to connect with Rave database and download SAS dataset in SAS Programming

How to connect with Rave database and download SAS dataset

Leo9 — Tue, 29 Sep 2020 04:08:04 GMT

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.

Re: How to connect with Rave database and download SAS dataset

ChrisNZ — Tue, 29 Sep 2020 04:18:11 GMT

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.

Re: How to connect with Rave database and download SAS dataset

Kurt_Bremser — Tue, 29 Sep 2020 07:28:27 GMT

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?

Re: How to connect with Rave database and download SAS dataset

Kurt_Bremser — Tue, 29 Sep 2020 07:31:54 GMT

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

Re: How to connect with Rave database and download SAS dataset

Leo9 — Tue, 29 Sep 2020 16:18:42 GMT

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 ?

Re: How to connect with Rave database and download SAS dataset

Kurt_Bremser — Tue, 29 Sep 2020 18:24:24 GMT

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.

Re: How to connect with Rave database and download SAS dataset

Leo9 — Tue, 29 Sep 2020 21:24:47 GMT

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.

Re: How to connect with Rave database and download SAS dataset

Kurt_Bremser — Tue, 29 Sep 2020 22:30:41 GMT

Which protocol and port did you use with Filezilla?

Re: How to connect with Rave database and download SAS dataset

Leo9 — Thu, 01 Oct 2020 16:39:01 GMT

SSL/TLS

Re: How to connect with Rave database and download SAS dataset

Kurt_Bremser — Thu, 01 Oct 2020 19:16:11 GMT

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.

Re: How to connect with Rave database and download SAS dataset

caesee — Tue, 18 May 2021 08:24:21 GMT

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.

Re: How to connect with Rave database and download SAS dataset

ChrisNZ — Wed, 19 May 2021 01:07:15 GMT

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