16 Using DQUOTE=ANSI Chapter 2
select * from mydblib."International Delays";
Notice that you use single quotation marks to specify the data value for London
(
int.dest=’LON’) in the WHERE clause. Because of the preserve name LIBNAME
options, using double quotation marks would cause SAS to interpret this data value as
a column name.
Output 2.4 DBMS Table with Nonstandard Column Names
International Delays
FLIGHT
NUMBER DATES ORIGIN DESTINATION DELAY
-----------------------------------------------------------
219 01MAR1998:00:00:00 LGA LON 18
219 02MAR1998:00:00:00 LGA LON 18
219 03MAR1998:00:00:00 LGA LON 18
219 04MAR1998:00:00:00 LGA LON 18
219 05MAR1998:00:00:00 LGA LON 18
219 06MAR1998:00:00:00 LGA LON 18
219 07MAR1998:00:00:00 LGA LON 18
219 01MAR1998:00:00:00 LGA LON 18
219 02MAR1998:00:00:00 LGA LON 18
219 03MAR1998:00:00:00 LGA LON 18
Next, you query a DBMS table and use a label to change the FLIGHT NUMBER
column name to a standard SAS name, Flight_Number. A label (enclosed in single
quotation marks) changes the name only in the output. Because this column name and
the table name, International Delays, each have a space in their names, you have to
enclose the names in double quotation marks. A partial output follows the example.
options linesize=64 nodate;
libname mydblib oracle user=testuser password=testpass path=’airdata’
schema=airport preserve_names=yes;
proc sql dquote=ansi outobs=5;
title "Query from International Delays";
select "FLIGHT NUMBER" label=’Flight_Number’, dates, delay
from mydblib."International Delays";
Output 2.5 Query Renaming a Nonstandard Column to a Standard SAS Name
Query from International Delays
Flight_
Number DATES DELAY
--------------------------------------
219 01MAR1998:00:00:00 18
219 02MAR1998:00:00:00 18
219 03MAR1998:00:00:00 18
219 04MAR1998:00:00:00 18
219 05MAR1998:00:00:00 18
Next, you preserve special characters by specifying DQUOTE=ANSI and using
double quotation marks around the SAS names in your SELECT statement.
proc sql dquote=ansi;
connect to oracle (user=testuser password=testpass);
