Specifies whether comments should be kept and passed down to your data source for processing.
| Valid in: | SAS/ACCESS LIBNAME statement, PROC SQL CONNECT statement |
|---|---|
| Category: | Data Set Control |
| Default: | NO |
| Data source: | DB2, Microsoft SQL Server, ODBC, Oracle |
| Note: | Support for this option was added in SAS 9.4M8. |
Table of Contents
specifies whether comments should be passed down to your data source.
When PRESERVE_COMMENTS=YES, comments can be passed in an explicit SQL query to your data source for processing. The comment is passed from the CONNECT statement in PROC SQL. The comment that you specify can contain database-specific keywords (hints), special characters, and markup text that are used by your native query processor.
To see the query that is passed to your data source, including any comments that you add, specify SASTRACE=',,,d' in the OPTIONS statement.
You can also pass comments for implicit SQL queries that SAS generatesautomatically, such as for PROC MEANS or PROC PRINT. This capability is available for the data sources that use the PRESERVE_COMMENT= LIBNAME option. For more information, see Macro Variables for Passing Content to DBMS Queries.
Here is a query that you pass to your DB2 data source where you set PRESERVE_COMMENTS= in the CONNECT statement. The query includes a comment that is passed to the data source for processing.
options SASTRACE=',,,d' msglevel=i;
proc sql ;
connect to db2(user=myuser pw=mypwd database=mydb2dbms PRESERVE_COMMENTS=YES) ;
select * from connection to db2 (
select Lname, Fname, City, State, IdNumber, Salary, Jobcode
from staff left join payroll
on idnumber=idnum
/*
<OPTGUIDELINES>
<REGISTRY>
<OPTION NAME='DB2_UNION_OPTIMIZATION'
VALUE='DISABLE_OJPPD_FOR_NP'/>
</REGISTRY>
</OPTGUIDELINES>
; */
) ;
quit ;Here is a LIBNAME statement and query that you pass to the DB2 data source. The query includes a comment that is passed to the data source for processing.
options SASTRACE=',,,d' msglevel=i;
libname mydblib db2 dsn=dsnname user=myuser pwd=mypwdval PRESERVE_COMMENTS=YES;
proc sql ;
connect using mydblib;
select * from connection to mydblib (
select Lname, Fname, City, State, IdNumber, Salary, Jobcode
from staff left join payroll
on idnumber=idnum
/*
<OPTGUIDELINES>
<REGISTRY>
<OPTION NAME='DB2_UNION_OPTIMIZATION'
VALUE='DISABLE_OJPPD_FOR_NP'/>
</REGISTRY>
</OPTGUIDELINES>
; */
) ;
quit ;