topic Re: PROC SQL - Issues with SQL Reserved words in SAS Programming

PROC SQL - Issues with SQL Reserved words

DerekD_WF — Wed, 08 Jun 2011 15:36:17 GMT

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:

proc sql;
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
;quit

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.

Re: PROC SQL - Issues with SQL Reserved words

Doc_Duke — Wed, 08 Jun 2011 18:48:35 GMT

Derek,

Once I removed the typo from your SELECT statement (leading comma), your code ran fine, so the problem is not where you are showing us.

Perhaps you can simplify your program, get that to fail, and include the log.

Doc Muhlbaier
Duke
-----------------

DATA Property_type_lookup;
input num_of_units desc order;
cards;
3 4 5
6 7 8
;
RUN;

proc sql;
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
;quit

Re: PROC SQL - Issues with SQL Reserved words

DerekD_WF — Wed, 08 Jun 2011 19:40:27 GMT

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
30 ,PropType.DESC
31 ,PropType.ORDER
32 from HASP.PROP_TYP as PropType
33 ;
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

Re: PROC SQL - Issues with SQL Reserved words

Doc_Duke — Wed, 08 Jun 2011 23:23:49 GMT

Since the SQL/Server allowed the column names, you may be able to use explicit pass-thru code to do the work on the server end and just get the renamed results back.

This is a kluge, but you might be able to use PROC COPY to copy the tables into SAS datasets on WORK and then do your SQL on SAS datasets.

Doc Muhlbaier
Duke

Re: PROC SQL - Issues with SQL Reserved words

DerekD_WF — Thu, 09 Jun 2011 15:01:51 GMT

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.