BookmarkSubscribeRSS Feed
BenCBanks
Fluorite | Level 6

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. 

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

BenCBanks
Fluorite | Level 6
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.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

BenCBanks
Fluorite | Level 6
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.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

SASKiwi
PROC Star

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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1955 views
  • 0 likes
  • 3 in conversation