Tuesday, 9 November 2010

NOTE: More Enterprise Guide Musings

Earlier in the year I offered some reflections on Enterprise Guide, having had the benefit of spending some solid time with it as a user. There are a few more things worth mentioning...

Enterprise Guide is usually introduced as a tool that encourages the use of visual coding. Visual coding has a great number of benefits, but some of the constraints are worth mentioning here. These relate to the fact that the EG visual code editor prefers to generate SQL code.
  • Each SQL node can only output one table. A single DATA step, on the other hand, can create more than one output table. This can make SQL inefficient when it needs to read an input table more than once in order to create multiple output tables - when a DATA step can read the input table just once in order to achieve the same outputs
  • Beware the Append node. Functionally it works, but it can be very inefficient when compared with PROC APPEND. Yes, that's right, the Append node doesn't use PROC APPEND, it uses SQL. And, while PROC APPEND simply adds rows to the end of an existing table (avoiding any need to re-write the existing table), EG's Append node re-writes the whole output table (existing rows plus new rows)
The reason EG prefers to generate SQL is clear - it offers a clearer, simpler means of accessing non-SAS tables, i.e. tables stored in relational databases. And, for the most part, SQL is a very efficient means of doing data access. However, the EG visual programmer needs to be aware of the code that is being generated if their resulting program is to operate efficiently. This may be particularly true of those who are familiar with Base SAS - those new to SAS coding might not make the same (false) assumptions about how things such as Append work.