Desktop productivity for business analysts and programmers

SAS EG 6.1 - Modify program code for a Query while the Query Builder is still open

Reply
Occasional Contributor
Posts: 11

SAS EG 6.1 - Modify program code for a Query while the Query Builder is still open

Hello Everyone,

I have been trained to use SAS EG 6.1, but am still relativity new. My boss is an old time programmer, and sometimes we have difficulty communicating about what I need to change or do in my programs. She is convinced there is a way to open and modify code from an EG query. Apparently this is easily done in other programs.

She want's to be able to open up a code like this:

PROC SQL;

   CREATE TABLE WORK."MAJORGROUPS3"n AS

   SELECT t1.ACADEMIC_PERIOD_GRADUATION,

          t1.MIN_of_ACADEMIC_PERIOD,

          t1.ID,

          t2.Major_Grouping,

      FROM WORK.MAJORGROUPS2 t1

           LEFT JOIN WORK.QUERY (t1.MIN_of_ACADEMIC_PERIOD = t2.ACADEMIC_PERIOD) AND (t1.ID = t2.ID);

QUIT;

  • Then be able to modify code without leaving the query builder to add DISTINCT or whatever needs to be added. Not copy the code then edit it. Thus I'll be able to still make changes in the query builder after we have changed whatever it is in the code she wants to change.

PROC SQL;

   CREATE TABLE WORK."MAJORGROUPS3"n AS

   SELECT DISTINCT t1.ACADEMIC_PERIOD_GRADUATION,

          t1.MIN_of_ACADEMIC_PERIOD,

          t1.ID,

          t2.Major_Grouping,

      FROM WORK.MAJORGROUPS2 t1

           LEFT JOIN WORK.QUERY (t1.MIN_of_ACADEMIC_PERIOD = t2.ACADEMIC_PERIOD) AND (t1.ID = t2.ID);

QUIT;

Is there a way that this can be done?

Thank you,

Renee

PROC Star
Posts: 1,146

Re: SAS EG 6.1 - Modify program code for a Query while the Query Builder is still open

Hi, Renee

First, I think you're definitely making the right decision to use EG. It's a great tool for smoothing the learning curve for a new SAS user, and I think you'll be productive much quicker than if you programmed in Base SAS.

In terms of your question, as far as I know there's no facility for modifying a query as it's being created. I suspect your boss is thinking of the "Insert code" option that appears in the Preview pane for many of the tasks, but this doesn't seem to be in the Query Builder.

One option that I frequently use is to create as much of the query as I can with Query Builder (especially convenient for selecting large numbers of variables), and then open the Preview window. From there I can copy the query code and drop it into a code window for modifications.

Incidentally, in terms of your question, if you click the "Select distinct rows only" check box on the Select Data tab, I think it will insert the "distinct" clause that you want.

Good luck,
  Tom

Occasional Contributor
Posts: 11

Re: SAS EG 6.1 - Modify program code for a Query while the Query Builder is still open

Hello Tom,

Thank you for responding. I see what you mean about the other tasks having the "Insert code" button. I hadn't noticed that before since I mostly use the query builder. Does the Insert Code button open the shell code?

In this document: http://support.sas.com/resources/papers/proceedings14/1557-2014.pdf

Page 1 say's "SQL IN THE QUERY BUILDER

SQL is a query language used by many software packages.The Query Builder in Enterprise Guide generates PROC SQL code behind the scenes. The user doesn’t need to code SQL because the query builder will do the heavy lifting.

An EG user can take the shell SQL code that EG generates and modify with special options, etc.

*image of shell code window*"


Do you think this is an error? That the person jumped to the conclusion that the query builder had the same capabilities as other tasks?


Thanks,

Renee

PROC Star
Posts: 1,146

Re: SAS EG 6.1 - Modify program code for a Query while the Query Builder is still open

That screen picture looks pretty definite...I took a look this morning, and I just looked again, and if there's a way to do this, I can't find it I'm afraid.


It's a recent paper...email the authors, and see what they say!

Best,

  Tom

Super User
Posts: 3,237

Re: SAS EG 6.1 - Modify program code for a Query while the Query Builder is still open

My interpretation is that you can take a copy of the SQL generated by Query Builder and modify it to suit your purposes. Once you do that, the code no longer matches the QB definition so you can only maintain the code outside of QB.

QB doesn't allow you to retrofit custom SQL into itself as such functionality would be fraught with difficulty. For example dealing with simple problems like typos.

The insert code button on other tasks I think is designed to add a self-contained step, like a DATA step prior to or after the process step, not to be able to modify the process itself.

Valued Guide
Posts: 3,208

Re: SAS EG 6.1 - Modify program code for a Query while the Query Builder is still open

It is the perception on what is coding about..

With Eguide Query builder the metadata-data information on tables and the defined relationships is the real source-code and the generates sas-code is a compiled version.

Yes, the result is readable SAS-code and you can modify that manually, but it is about the process around that.

There was a time Assembler guys where fighting the Cobol programmers because you could code far better coding with Assembler as with that too easy to be used Cobol (no real code challenge).

You boss is an old timer you could ask where the assembly code should go and where the T-ford goes into maintenance. She won't treat you friendly when you are that unfriendly.

The question would be more how could you seduce her to use he new approach.

When its is more acceptable to use the generated code than that is the situation. You can add save a code node after the query-builder.

By that isolating the code modification.

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 5 replies
  • 1748 views
  • 0 likes
  • 4 in conversation