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 ;