A customer phoned up SAS Technical Support the other day with a problem. (Yes, that happens occasionally...) When he ran a certain program through SAS Enterprise Guide, the program didn't seem to complete and no results were returned. Here is a snippet of the program (modified a bit to make it less proprietary) :
proc sql noprint; connect to teradata as tera2 (user=&user pw=&password server=&server mode=teradata FASTEXPORT=YES); create table buyers as select * from connection to tera2 (select id, trxn_dt, sum (ln_itm_qty) as Items, sum (ext_prc_amt as Sales from store.linetrxn a join cust.indv_xref b on a.rid = b.rid /* remainder removed for brevity */
Can you spot the problem? Other than the use of a user ID and password, which can be avoided, what else is wrong with this program? Why does it fail to finish?
The SAS code formatter in SAS Enterprise Guide 4.3 can tell you. Click Ctrl+I, and the code formatter scans your program and attempts to indent it for proper readability. But for this program, it stops short of that with this message:
Please check for an unmatched parenthesis on line 7.
Now you know: there is an unmatched parenthesis! And every SAS programmer knows that a missing parenthesis, unmatched quote, or missing semicolon can cause problems for the SAS language parser. (Rick Wicklin calls this a "parse-time error" in his post about finding and fixing programming errors.)
But where is the unmatched paren in this program? The message says "line 7", but line 7 looks okay. There is an open paren for the inner SELECT clause. But starting there, there must be a missing matching parenthesis within the clause. To find it, use the arrow keys in the program editor to navigate the program and visit every parenthesis character in the region. The program editor in SAS Enterprise Guide 4.3 highlights the matching parentheses pairs as you navigate:
You can also use the Ctrl+[ (open bracket) key to move the cursor from one parenthesis to its match, if there is one. If no match can be found, the program editor emits a friendly beep to tell you, "sorry, can't find a match".
In this example, you don't have to look further than line 10, where the programmer has left off a closing parenthesis. The line should read:
sum (ext_prc_amt) as Sales
People are really starting to appeciate the new SAS program editor (including the formatter). Earlier this week I received an unsolicited phone call from a SAS programmer (who happens to work at SAS). She was calling just to say that this feature is "AWESOME" and that it has saved her countless hours as she maintains legacy SAS programs. Angela Hall also highlights it in her blog about real-world BI experiences.
I love to hear these stories. Have you started using SAS Enterprise Guide 4.3 yet? What do you like about it? And what could be improved? Let us know, either here in the comments or via SAS Technical Support.
I copied the example code in the article and tried the Ctrl+l without any success. I must be doing something wrong. Using EG 4.3 9.2 M 2.
Try right-click, Format Code in the program editor view, just to make sure. It's possible your Ctrl+I key is mapped to a different command.
If it continues to not work for you, contact SAS Technical Support.
It is a great feature. I always use it.
Do you have any idea or anything that can check the SAS code syntax and notify us like this where we are doing the mistake.
Like I have taken the above example and wrote like proc ql instead of proc sql and at this point I am expecting ctrl+I should help me identify this.
Please let me know if there is any option available for SAS EG.
Sorry, the formatter isn't a true compiler, which is what you need to check the syntax as SAS would see it.
I have library of many SAS programs that could use this done to them. Is there a way to "batch" format them or do I have to open each, one at a time, do the "Ctrl-I" then save the result?
For now, no batch method. Just one at a time.