SyntaxHighlighter

Tuesday, August 27, 2013

Getting around SQL reserved words

PROC SQL has some ANSI standard reserved words such as USER that cannot be used without renaming it or using the DQUOTE = ANSI option according to this web site

The first SQL query below fails because "the USER keyword will have a constant value for all rows in a query".  The second query works but requires the DQUOTE = ANSI option and you must double quote the reserved word.

I did not want to quote or rename a column. Through some trial and error I figured out that using a fully qualified table.column name was the solution that worked best for me.

data x ;
  do user = 'Billy', 'Joe', 'Bob' ;
    do i = 1 to 10 ;
      output ;
    end ;
  end ;
run ;

proc sql    ;
  create table x1 as
    select       user
               , count(*) as cnt
      from       x
      group by   user ;

  reset dquote = ansi ;
  create table x2 as
    select       "user"
               , count(*) as cnt
      from       x 
      group by   "user" ;

  create table x3 as
    select       x.user
               , count(*) as cnt
      from       x
      group by   x.user ;
quit ;