[. . . ] Government Restricted Rights Notice: Use, duplication, or disclosure of this software and related documentation by the U. S. government is subject to the Agreement with SAS Institute and the restrictions set forth in FAR 52. 227-19, Commercial Computer Software-Restricted Rights (June 1987). 1st electronic book, February 2011 SAS® Publishing provides a complete selection of books and electronic products to help customers use SAS software to its fullest potential. For more information about our e-books, e-learning products, CDs, and hard-copy books, visit the SAS Publishing Web site at support. sas. com/publishing or call 1-800-727-3228. [. . . ] If you run a statistical analysis that depends on row order in a DBMS table (such as the Durbin-Watson statistic in the REG procedure or tables with ORDER=DATA in the FREQ procedure), the results might differ every time you run the analysis. To get a consistent row order for a DBMS table, you can use the DBCONDITION= data set option to specify an ORDER BY clause with one or more variables that defines a unique order for the rows. However, the DBCONDITION= option works only for out-of-database processing. Following is an example that uses the DBCONDITION= option for out-of-database processing to control the row order in PROC PRINT: options ls=72 options nodate nostimer nonumber; %let server =sl91204; %let user =sas; %let password=sas; libname tera teradata server=&server user=&user password=&password; The SAS System generates the following note: NOTE: Libref TERA was successfully assigned as follows: Engine: TERADATA Physical Name: sl91204 Indeterminate Row Order in a DBMS Table !29 proc delete data=tera. test; run; The SAS System generates the following warning: WARNING: File TERA. test. DATA does not exist. data tera. test; do i=1 to 3; do j=1 to 4; x=rannor(12345); output; end; end; run; The SAS System generates the following note: NOTE: The data set TERA. test has 12 observations and 3 variables. title "Indeterminate Row Order"; proc print data=tera. test; run; The SAS System generates the following output: Indeterminate Row Obs i j 1 1 1 2 3 1 3 1 2 4 3 2 5 1 3 6 3 3 7 1 4 8 3 4 9 2 1 10 2 2 11 2 3 12 2 4 Order x -0. 04298 0. 57221 -0. 09999 0. 17571 -0. 24349 -1. 44361 -0. 22226 0. 44887 0. 07353 0. 49937 -1. 52119 0. 79180 NOTE: There were 12 observations read from the data set TERA. test. title "Order Rows by I and J"; proc print data=tera. test(dbcondition="order by i, j"); run; 30 !Chapter 1: SAS Analytics Accelerator 1. 3 for Teradata: Guide The SAS System generates the following output: Order Rows by I and J Obs i j x 1 1 1 -0. 04298 2 1 2 -0. 09999 3 1 3 -0. 24349 4 1 4 -0. 22226 5 2 1 0. 07353 6 2 2 0. 49937 7 2 3 -1. 52119 8 2 4 0. 79180 9 3 1 0. 57221 10 3 2 0. 17571 11 3 3 -1. 44361 12 3 4 0. 44887 NOTE: There were 12 observations read from the data set TERA. test. title "Order Rows by X"; proc print data=tera. test(dbcondition="order by x"); run; The SAS System generates the following output: Order Rows by X Obs i j 1 2 3 2 3 3 3 1 3 4 1 4 5 1 2 6 1 1 7 2 1 8 3 2 9 3 4 10 2 2 11 3 1 12 2 4 x -1. 52119 -1. 44361 -0. 24349 -0. 22226 -0. 09999 -0. 04298 0. 07353 0. 17571 0. 44887 0. 49937 0. 57221 0. 79180 NOTE: There were 12 observations read from the data set TERA. test. proc delete data=tera. test; run; The SAS System generates the following note: NOTE: Deleting TERA. test (memtype=DATA). SAS/ACCESS Data Set Options for Teradata !31 SAS/ACCESS Data Set Options for Teradata Table 1. 5 describes whether SAS/ACCESS data set options for Teradata work correctly with the DATA= and OUT= SAS/STAT and SAS/ETS procedure options. The cell entries in the DATA= and OUT= columns have the following values: Yes--indicates that the option works for in-database computing No--indicates that the option does not work for in-database computing N/A (not applicable)--indicates that the option does not apply to in-database computing Sometimes--see Comment column for details Some cell entries in the DATA= and OUT= columns have superscripts that are intended to convey additional information. The meanings of the superscripts are as follows: A superscript of 1 (Yes1 or No1 ) indicates an option that correctly affects in-database processing as expected. A superscript of 2 (Yes2 or No2 ) indicates an option that works differently for in-database computing compared to out-of-database computing. A superscript of 3 (Yes3 , No3 , N/A3 , or Sometimes3 ) indicates an option that causes the procedure to use SAS/ACCESS for out-of-database computing. The meanings of the superscripts are as follows: A superscript of 1 (Yes1 or No1 ) indicates an option that correctly affects in-database processing as expected. A superscript of 2 (Yes2 , No2 , or N/A2 ) indicates an option that causes SAS/ACCESS syntax errors or warnings. When SAS/ACCESS software issues a warning message, the option is ignored by the procedure. A superscript of 3 (Yes3 , No3 , or N/A3 ) indicates an option that causes the procedure to use SAS/ACCESS for out-of-database computing. Table 1. 6 Base SAS Data Set Options Data set option ALTER= DATA= No2 OUT= No2 Comments SAS passwords are not supported on DBMSs. For SAS data sets only. BUFNO= BUFSIZE= CNTLLEV= COMPRESS= DLDMGACTION= DROP= ENCODING= N/A N/A N/A N/A N/A Yes1 No2 N/A N/A N/A N/A N/A No3 No2 36 !Chapter 1: SAS Analytics Accelerator 1. 3 for Teradata: Guide Data set option ENCRYPT= FILECLOSE= FIRSTOBS= DATA= No2 N/A No3 OUT= No2 N/A No3 Comments Requires SAS passwords. DBMS tables have no inherent row order, and FIRSTOBS= is defined in terms of row order. Neither the Teradata nor TSSQL drivers support generation options that are explicitly specified in the procedure statement, and the procedure does not know whether a generation number is explicit or implicit. Neither the Teradata nor TSSQL drivers support generation options that are explicitly specified in the procedure statement, and the procedure does not know whether a generation number is explicit or implicit. For SAS data sets only. GENMAX= No2 No2 GENNUM= No2 No2 IDXNAME= IDXWHERE= IN= INDEX= KEEP= LABEL= N/A N/A N/A N/A2 Yes1 No2 N/A N/A N/A No2 No3 No2 SAS data set attributes cannot be stored in Teradata. DBMS tables have no inherent row order, and FIRSTOBS= is defined in terms of row order. [. . . ] By looking at the output of these procedures, you can see that the dependent variable GOOD_BAD is a character variable. To use PROC REG, you must transform the GOOD_BAD character variable to a numeric variable. You need to use the explicit pass-through syntax to force processing in-database. For the column named Foreign, you must enclose the column name in quotation marks to prevent Foreign from being interpreted as a Teradata keyword. [. . . ]