Create an Enterprise Guide Query Builder from a Proc SQL job?

[RHenson]

Hello folks,     Does anyone have experience with converting a proc sql job into a query builder by simply cutting and pasting the code?   We have an enormous amount of conversion work to complete and manually joining tables, selecting rows and applying filters and sorts to mimic existing sql is going to take a lot of time.   Is there a wizard or a custom task option that would in essence reverse the extract code feature available from a query builder?

[LinusH]

I am not aware of a such toiol.

The only similar tool I know about is the Code Importer in SAS Data Integrations studio.

A thing that might help you navigate and undestand your massive code base is PROC SCAPROC, which analyzes your programs and document them in a structured way.

SAS Help Center: SCAPROC Procedure 

[Patrick]

If you've got already working SQL code then why can't you just move this code to a SAS EG program node? What would be the advantage to have this logic implemented via a query builder node?

[RHenson]

Hi Patrick, thanks for responding.   We've got a large group of users in multiple departments who are versed in editing their own ad-hoc reports using EG Query Builders.   I fully understand that copy/pasting into a program node would get the same results, it's just that we're trying to keep the conversion process as familiar to the end users as possible.   

[Patrick]

Hi @RHenson 

SQL is a widely used and transferrable skillset that's very valuable in many scenarios (example: using Python interfacing with a database). 

If your userbase really doesn't have these SQL skills already then I'd consider to invest into some SQL training as part of change management. 

@SASKiwi already pointed out: "As with most user interfaces, the SAS Query Builder only goes so far, and it can't provide all of the functionality a coded approach can."

Even if you would manage to develop some conversion tool it would likely still not allow to express all your existing code via the existing EG transformation.

[SASKiwi]

Is there a lot of commonality in the  conversion work you doing? For example, repeating the same joins and selects across many programs? If so, using SAS macros may be a way of doing the changes more efficiently.

As with most user interfaces, the SAS Query Builder only goes so far, and it can't provide all of the functionality a coded approach can. That is one of the main reasons, reverse engineering code isn't available.

[AlanC · Accepted solution]

If I understand correctly, what you would need is a SQL parser. From there, you could use the syntax tree to generate the Query Builder components and/or movements if simulating a UI.

SQL parsing is not a trivial task. Combine that with the fact that SAS SQL is a different syntax than just ANSI SQL and you have a real challenge. 

Ultimately, it will be far more work for you to do the parsing. You can look at generic parsers for SQL but they tend to be specific. After getting the statements parsed out, you then need to automate the query building process.  Better SQL parsers are very, very expensive. 

If you have repetitive tasks that can be logically described in a UI, use UiPath and script the human movement. UiPath is an RPA tool and can dramatically speed up repetitive manual tasks. You should be able to use their desktop version. Takes a bit of getting used to but you will never go back to manual, repetitive tasks again. Wonderful product.

[Kurt_Bremser]

Trying to squeeze code into the confines of the Query Builder is like going back from Gutenberg to stone tables, hammer and chisel.

Instead of further hampering your users by restricting them to a helper tool, make them SQL literate.