topic Re: Reading SAS datasets located on Unix-server in QlikView

Reading SAS datasets located on Unix-server

Wed, 03 Apr 2013 08:55:43 GMT

Hi,

I just got involved in a project where we have to read a lot of SAS datasets for our QlikView solution. The SAS-data is located on a Unix Solaris 64-bit server. On another machine we have the QlikView-server. I am trying to figure out how to read the data directly from QV. The only information I have found involves installing an ODBC driver for SAS on the QlikView-server as well as installing a local SAS server on the QV-machine. This is the document someone at QlikTech gave me regarding this: http://community.qlik.com/servlet/JiveServlet/downloadBody/1998-102-1-1993/How%20to%20Setup%20ODBC%20Connection%20to%20SAS%20Datasets.doc.

However, we are not allowed to install a SAS server on the QV-server. And the document seems based on the assumption that the SAS server is Windows based and not Unix.

I am not at all experienced in matters involving connectivity between different environments, so any advice would be appreciated. How can I read SAS datasets from an Unix server directly from QlikView installed on another server?

Thanks,

David

Re: Reading SAS datasets located on Unix-server

rbecher — Wed, 03 Apr 2013 11:33:51 GMT

Hi David,

I'm not sure and haven't tested so far but maybe a JDBC connect could help:

http://support.sas.com/documentation/onlinedoc/jdbc/

- Ralf

Re: Reading SAS datasets located on Unix-server

Thu, 04 Apr 2013 07:52:03 GMT

Hi Ralf,

Thank you for the tip. I've also read other threads on this forum where you discuss the QlikView JDBC Connector. And also checked out the product at http://www.tiq-solutions.de/display/enghome/ENJDBC.

As I mentioned above, I'm not at all knowledgeable in the area of connectivity, and I wont be the one doing any installation on either our SAS-server or our QV-server. I'm merely trying to find information I can forward to the people responsible for this.

How would it work? Is it correctly understood by me that the SAS JDBC driver should be installed on the SAS-server and the QlikView JDCB Connector installed on the QV-server? But as SAS is not listed as one of the possible data sources for the JDBC Connector, what are the chances that it would work? Why wouldn't it work?

I really appreciate any advice you can give me.

Sincerely,

David

Re: Reading SAS datasets located on Unix-server

rbecher — Thu, 04 Apr 2013 11:39:13 GMT

Hi David,

we haven't tried SAS as JDBC source so far. There are hundreds of possible JDBC sources (or even more.. ;-). We can not test all of them upfront because this would be and endless venture. But, we will test on a clients request base and I see no problem since JDBC in fact is a standard and vendors have to follow the implementation rules. Of course there could be some obstacles but we can adapt the JDBC connector to a workable solution. Which we already did in some cases because of some inadequate vendor implementations.

- Ralf

PS: to answer your second question the JDBC connector must be installed on the QlikView server. The SAS JDBC driver must be configured on JDBC connector (on the QlikView server). I guess it's just an .jar file..

Re: Reading SAS datasets located on Unix-server

Thu, 04 Apr 2013 11:54:57 GMT

ok, great Ralf. Thank you. I will forward the information that you have provided to my colleagues. Hopefully we can test the solution you suggest and I will get back here and tell you how whether or nor we got it to work.

Cheers,

David

Re: Reading SAS datasets located on Unix-server

Thu, 11 Apr 2013 10:59:39 GMT

Hi again Ralf,

After a lot of internal political discussion regarding whom in our organisation would be responsible for making QlikView query SAS we finally got going according to your suggestion.

So we have the SAS-provided JDBC driver installed. But when we try to install the JDBC QlikView Connector it asks us to enable the Internet connection. This is something we can't do. My client is a bank and that server is not allowed to be connected to the internet. Do you have suggestions?

Thank you,

David

Re: Reading SAS datasets located on Unix-server

rbecher — Fri, 12 Apr 2013 06:55:40 GMT

Hi David,

please see attached file for offline activation.

- Ralf

Re: Reading SAS datasets located on Unix-server

Sat, 11 May 2013 15:28:01 GMT

Hi again Ralf,

My IT-department still haven't been able to get it to work. I believe they've been in contact with you or at least with someone at TIQ Solutions. I've been kind of out of the loop focusing on building the parts of the QV-applications that do not need SAS data. However, now I am at a point where I really need to be able to read our SAS data sets. But internally at my client this issue has turned into a blame game between the QlikView, Windows, Unix and SAS administrators and nothing gets done. I've been playing around on my own computer over the weekend to see if I can get it to work.

I've installed the latest JDBC driver from SAS and the Java environment. I have my local QV-client and a trial version of the JDBC QlikView Connector. I've tried loads of different configurations but whenever I try to connect by providing the connection URL, I get the message "No suitable driver found".

Can you assist me in what I am doing wrong? And is there any kind of documentation for your product?

Attached are screendumps of my latest configuration of the JDBC QlikView Connector.

Thanks again,

David

Re: Reading SAS datasets located on Unix-server

rbecher — Mon, 13 May 2013 09:16:35 GMT

Hi David,

please get in touch with me directly: ralf.becher@tiq-solutions.de

- Ralf

Re: Reading SAS datasets located on Unix-server

Thu, 13 Jun 2013 14:22:04 GMT

Hello,

It's been a while since I started this thread, and finally we have got it working. It took a lot of trial and error and a lot of help from Ralf B. but we now read SAS data sets from Qlikview using QlikView JDBC Connector.

I have a lot of information gained from this experience that I would like to share here on the forum. But at the moment I am overloaded with work trying to meet a deadline of last of June. But after that, early July I will write another post in detail describing what we had to do, and what we did that didn't work.

//David

Re: Reading SAS datasets located on Unix-server

raajeshn — Fri, 28 Jun 2013 09:54:09 GMT

Hi David,

I guess - you can help me out. I am also facing an issue, something on your similar lines. Apart from connectivity, just wanted to check - if QlikView can consume the data directly from SAS Datasets - my client is somehow being convinced that there is an additional SAS SHARE License needed (which is approximatley around 84 K USD + 30% maintenance). Do we actually need this? Are you having this in your SAS environment? Appreciate your response.

Ralf - Not sure, but if you can throw some light on this, that will be great.

Thanks & Regards,

Raajesh N

Re: Reading SAS datasets located on Unix-server

rbecher — Fri, 28 Jun 2013 09:58:55 GMT

Hi Raajesh,

sorry, but I don't know anything about SAS licensing..

- Ralf

Re: Reading SAS datasets located on Unix-server

Fri, 28 Jun 2013 15:04:00 GMT

Hi Raajesh,

I'm not 100% sure but I don't think it's possible to read SAS datasets directly. You need some kind of SAS server/base engine.

There are a lot of different SAS modules that can read the data sets and integrate with other systems. We do run a SAS Share server at my client. I have no idea about the licensing fee. But you don't need that just to be able to read SAS datasets. If you have the datasets locally the cheapest local client SAS Base engine would suffice.

Best regards David

Re: Reading SAS datasets located on Unix-server

raajeshn — Fri, 28 Jun 2013 16:22:05 GMT

Hi David,

Thanks for the quick turn around.

I am looking at a QlikView Document where it suggests about a ODBC Connector and I am trying to understand the cost / process involved behind this. Two issues - 1) I dont have SAS connectivity for now test 2) My knowledge on SAS is below 0? Any thoughts around this will be absolutely great.

Also, do you have any idea on how much does it cost for a SAS Base Engine.

Thanks & Regards,

Raajesh N

Re: Reading SAS datasets located on Unix-server

Sat, 29 Jun 2013 12:35:17 GMT

Hi Raajesh,

I guess you are referring to the document "How to Setup ODBC Connection to SAS Datasets" which can be found at different places on the forum and is also provided by QlikTech. There is nothing wrong with that guide but the step-for-step instructions are only applicable if you have your datasets locally and also have a local SAS Base engine. I have no idea about the cost for SAS BASE. Here is a link to SAS and their BASE Engine page:http://www.sas.com/technologies/bi/appdev/base/#section=1. Give them a ring and ask if there is any possible way to read datasets without a SAS service running (and report it back here if there are). But according to the SAS ODBC Driver User Guide there is not.

The only other thing I've seen about reading SAS datasets without a SAS engine is something I found on another forum. If it's just a matter of you having some SAS datasets that you want to convert to another format this might work. But if you continuously need to read datasets in your production environment this is probably not the right solution. Anyway, this is copied from the other forum:

I have never tried http://www.oview.co.uk/dsread/, but it might be what you're looking for: "a simple command-line utility for working with datasets in the SAS7BDAT file format." But do note "This software should be considered experimental and is not guaranteed to be accurate.

Sincerely,

David

Re: Reading SAS datasets located on Unix-server

thanstad — Fri, 11 Oct 2013 12:13:00 GMT

Hi, we are today using QlikView for all kind of data collection into a set of core apps and forther userwritten apps based on these. We also provide true QlikView a set of core information to SAS analysis and today we go from QlikView via putty and Oracle database to produce SAS data. I am a former SAS user and I have read about the connector from SAS datasets, but I wonder if I could send data from QlikView via a connector to SAS datasets directly. Then we coulkd save a lot of work.

Tormod Hanstad

Re: Reading SAS datasets located on Unix-server

rbecher — Fri, 11 Oct 2013 12:31:48 GMT

Hi Tormod,

the JDBC connector supports to connect to SAS IOM. This can be used during QlikView LOAD script to SELECT data from a SAS source, although it has some limitations. Perhaps, you can pass an INSERT statement thru the connection, but I don't know if it work for now. However, I think it is not possible to bulk upload data with it.

- Ralf

Re: Reading SAS datasets located on Unix-server

thanstad — Mon, 14 Oct 2013 08:08:51 GMT

Hi Ralph, thanks for your explanation. Yea it seems to make sens what you saying. I think we stick to the solution we have so far. If I should make the workflow with fewer steps, I could give the SAS people the csv file directly. SAS and QlikView are both capable to read 15 mill records in a relative short time. Well infact, QlikView is faster in our case.

/Tormod