I am attempting to write a PROC SQL query, in which a few of the database tables I am querying use reserved words for the column names. Specifically there are columns name 'ORDER' and 'DESC' in a couple of the tables. I should also mention that the query has many more selected columns and joined tables than I will show, but here is an example of what a a portion of the query looks like:
create table NEW_TABLE as
SELECT ,PropType.NUM_OF_UNITS AS PT_NUM_OF_UNITS
,PropType.DESC AS PT_DESC
,PropType.ORDER AS PT_ORDER
FROM Property_Type_lookup as PropType
The tables are aliased because there are more than one table that contains the columns 'ORDER' and 'DESC'. I have tried a couple different methods:
1. using the DQUOTE=ANSI option and typing the selected variable as both ,"PropType.ORDER" and ,Proptype."ORDER". I wasn't able to get that to work.
2. Setting VALIDVARNAME = ANY and using name literals: 'PropType.ORDER'n
3. Using (rename=(ORDER=PT_ORDER)) options on the datasets that use reserved in "FROM" portion of the query.
My questions are the following: Does the DQUOTE= option work with aliased table references and if so how where do you place the double quotes to get SAS to correctly read the column from the database?
Any insight or other options as to how I can make this work would be appreciated.
I think I left out some key information that may make a difference to the answer for my question. The data source(s) in question are not SAS datasets, but rather they are database tables accessed via SAS/ACCESS. The errors are occurring in the translation to the databases language. Here is the log from simple step I ran, similar to the example provided:
27 proc sql;
28 create table NEW_TABLE as
29 select Prop_Typ_ID
32 from HASP.PROP_TYP as PropType
33 ! quit;
ODBC: AUTOCOMMIT is NO for connection 1 5216 1307561914 ducon 0 SQL (2)
2 The SAS System 07:58 Wednesday, June 8, 2011
ODBC: AUTOCOMMIT turned ON for connection id 1 5217 1307561914 setconlo 0 SQL (2)
5218 1307561914 du_prep 0 SQL (2)
ODBC_920: Prepared: 5219 1307561914 du_prep 0 SQL (2)
SELECT * FROM dbo . PROP_TYP 5220 1307561914 du_prep 0 SQL (2)
5221 1307561914 du_prep 0 SQL (2)
5222 1307561914 du_prep 0 SQL (2)
ODBC_921: Prepared: 5223 1307561914 du_prep 0 SQL (2)
SELECT PROP_TYP_ID , DESC , ORDER FROM dbo . PROP_TYP 5224 1307561914 du_prep 0 SQL (2)
5225 1307561914 du_prep 0 SQL (2)
ODBC: ROLLBACK performed on connection 1. 5226 1307561914 du_comm 0 SQL (2)
ERROR: CLI describe error: [DataDirect][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'DESC'.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.03 seconds
Thanks for the suggestion. I did something similar to this. Since the tables in my joins that are impacted by the reserved words are very small lookup tables, I just did separate data steps to create temporary datasets in the Work library and used a rename= option to change the variable names.