topic Re: IBM Cognos to PROC SQL in SAS Data Management

IBM Cognos to PROC SQL

BenCBanks — Thu, 16 Aug 2018 11:46:05 GMT

How can I take an extract from IBM Cognos 11 and then put it into SAS to run on the data? Is this possible? They obviously have different syntaxes, which is the difficult part.

Re: IBM Cognos to PROC SQL

RW9 — Thu, 16 Aug 2018 12:29:07 GMT

An extract of what, SQL script, data? If its data then follow standard import. If its SQL script then yes you would need to assess the code, what is base ANSI SQL, and what is Cognos specific (i.e. most DB's provide their own sets of functions, these are not cross compatible). Other than that not sure what I can say with limited information.

Re: IBM Cognos to PROC SQL

BenCBanks — Thu, 16 Aug 2018 12:43:16 GMT

Yes, I have the option to extract the "generated SQL" in either "native
SQL" or "IBM Cognos SQL"-- and then I want to paste it in SAS because I
have access to the same tables in a library in SAS that Cognos is pulling
from.

Re: IBM Cognos to PROC SQL

RW9 — Thu, 16 Aug 2018 13:04:30 GMT

Depends what "native" SQL is. I would assume that is with all the functions and what not expanded out, which may make a lot of code. Try it and see what you get. End of the day, your most likely better off understand what is required (and here is where documentation comes in handy) and then see how much can be easily transposed across - SAS has a lot of operations which are very simple there which are very hard in SQL for instance, so analysis of the code may yield better, faster code.

Re: IBM Cognos to PROC SQL

BenCBanks — Thu, 16 Aug 2018 13:10:16 GMT

You make a good point. In some ways, I think that analyzing the SQL (it is
37 pages long) and trying to piece it together to create in SAS may be
better.

Re: IBM Cognos to PROC SQL

RW9 — Thu, 16 Aug 2018 13:28:08 GMT

You could model it using a graph like system, might help visualise the SQL and easier to break down. Just a quick search resulted in this:

https://app.sqldep.com/queryscope/demo/

Looks quite fun, but $15 a month. Probably simpler to build it yourself using boxes or clip art in Excel.

Re: IBM Cognos to PROC SQL

SASKiwi — Thu, 16 Aug 2018 22:38:46 GMT

If your "native SQL" is from a back-end relational database you may be able to drop it into a SAS SQL passthru query/ies and run it virtually unchanged. It depends what it is doing, whether it is creating tables or just producing a result set.