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 ;
No comments:
Post a Comment