SAS/ACCESS Software for Relational Databases: Reference

PROC SQL Pass-Through Views

A Version 6 PROC SQL Pass-Through view does not need to be updated to be used in Version 7 or Version 8. The conversion of PROC SQL Pass-Through views is automatic and does not require you to use the PROC CV2ODBC procedure, as described in CV2ODBC Procedure. The ODBC interface and DBMS client must be available and ready to connect. In order for any truncated variable names to be correctly interpreted by the ODBC driver, you must specify the VALIDVARNAME=V6 option.

Note: If the view cannot be processed, or if you want to see what the view is, use the DESCRIBE VIEW statement to see what the existing view is. Then you can use the PROC SQL statements to create a new view for the ODBC connection. [cautionend]

IBM AS/400 Specifics

To run your SQL Pass-Through views for IBM AS/400, you must

create a data source name first by using the ODBC administrator. Refer to Data Source Configuration for the IBM AS/400 for more information.
set the environment variable AS400DSN to be 'data-source-name'. Quotation marks are required if the name includes blanks or special characters.

MS SQL Server Specifics

To run your SQL Pass-Through views for the MS SQL Server, you are encouraged, but not required, to create a data source name. You can use the ODBC administrator to create it. Refer to Data Source Configuration for MS SQL Server for more information. If you do create a data source name, you must set the environment variable MSSQLDSN to be 'data-source-name'. Quotation marks are required if the name includes blanks or special characters.

Pass-Through View Examples

Example 1: An AS/400 Pass-Through View

In this example,

CONNECT TO AS400 AS market;

is converted to

CONNECT TO ODBC AS market
  (NOPROMPT="DSN=IBM AS/400 Database;
   UID=TESTUSER; PWD=TESTPASS;
   NAM=1"
  )
;

Example 2: An MS SQL Server Pass-Through View

In this example,

CONNECT TO SQLSERVR AS finance
  (user=testuser pass=testpass
   server='dbipc1.pc.sas.com'
   database='sample'
  )
;

is converted to

CONNECT TO ODBC AS finance
  (NOPROMPT="DSN=Microsoft SQL Server Database;
   SERVER=dbipc1.pc.sas.com;
   UID=testuser; PWD=testpass;
   DATABASE=sample"
  )
;

Example 3: Using the VALIDVARNAME=V6 Option

In this example, you must use the SAS option VALIDVARNAME=V6 in order to successfully process this Version 6 SQL Pass-Through view.

options validvarname=v6;
proc sql;
  describe view as4sql.invoice4;
run;

/* NOTE: SQL view AS4SQL.INVOICE4 is defined as: */
select
 INVOICEN as INVOICE,
 AMTBILLE as AMOUNT format=DOLLAR20.2,
 BILLEDON
 from connection to AS400
 /* dbms=AS400, connect options=() */
 (select invoicenum, amtbilled, billedon
   from sasdemo/invoice
   where paidon ='18OCT1998');

In Version 6, the AS/400 column name INVOICENUM is mapped to the SAS variable INVOICEN, and AMTBILLED is mapped to AMTBILLE. If you do not specify option VALIDVARNAME=V6, you get the following error because the ODCB driver attempts to find the truncated column names in the DBMS table:

ERROR: The following columns were not found in the
       contributing  tables: AMTBILLE, INVOICEN.

Example 4: AS/400 Short Alias Names in SQL Pass-Through Views

This example demonstrates a problem in which AS/400 short alias names cannot be returned by the AS/400 ODBC driver. This problem causes you to get an error, for example, if you have specified the short alias names in your selection list before the CONNECTION TO component, but have not specified the short alias names in the selection list that defines the view. If you encounter this problem with your Version 6 SQL Pass-Through views, you need to re-create the views.

This example creates an AS/400 table named TEST5 with the columns CUSTOMER_FIRST_NAME and CUSTOMER_LAST_NAME. The short name alias for CUSTOMER_FIRST_NAME is FNAME and the short name alias for CUSTOMER_LAST_NAME is LNAME.

options validvarname=v6;
%let name=test5;
proc sql;
  describe view as4sql.&name;
  /* NOTE: SQL view AS4SQL.TEST5 is defined as: */
  select FNAME, LNAME from connection to AS400
  /* dbms=AS400, connect options=() */
  ( select * from sasdemo/test 
      where lname = 'Ju' );
quit;
proc print data=as4sql.&name;
run;

This example generates the following errors:

ERROR: The following columns were not found in
       the contributing tables: FNAME, LNAME.
ERROR: SQL View AS4SQL.TEST5 could not be processed.

The following two examples work successfully because the short alias names are specified in the SELECT statement that defines the view.

create view as4sql.&name as
  select FNAME, LNAME from connection to AS400
  /* dbms=AS400, connect options=() */
  (select FNAME, LNAME from sasdemo/test
    where lname = 'Ju' );

create view as4sql.&name as
  select * from connection to AS400
  /* dbms=AS400, connect options=() */
  (select fname, lname from sasdemo/test
    where lname = 'Ju' );

Data Source Configuration for the IBM AS/400

Note: The following instructions are specific to PC platforms. [cautionend]

Use the following steps to configure your data source for AS/400.

Install the IBM AS/400 Client Access for Windows 95 or NT and Client Access ODBC Driver (32 bit).
Note: You can install the software from 'IBM AS/400 Client Access Family' CD-ROM. Refer to your IBM AS/400 documentation for more information.
Double click on the 32-bit ODBC Administrator in the Control Panel.
To make the data source available to all users of your PC, click on the System DSN... button. If you do not want the data source to be available to all users of your PC, continue with step 4.
Select the Add button, and choose 'Client Access ODBC Driver(32 bit)'.
Type in a Data Source Name. 'AS400' is the standard, or you may choose another name.
Type in your user ID if you want it to be the default.
Make sure that the System Name is correct.
Select the Server tab, and clear the Default library.
Select the Format tab, and select 'System naming convention (*SYS)' as the naming convention.
Select the Performance tab, and uncheck 'Enable extended dynamic support'.
Select OK to save the data source configuration.

Data Source Configuration for MS SQL Server

Note: The following instructions are specific to PC platforms. [cautionend]

Use the following steps to configure your data source for MS SQL Server.

Install the Microsoft SQL Server Client for Windows 95 or NT and Microsoft SQL Server ODBC Driver (32 bit). Refer to your MS SQL Server documentation for more information.
Double click on the 32-bit ODBC Administrator in the Control Panel.
To make the data source available to all users of your PC, click on the System DSN... button. If you do not want the data source to be available to all users of your PC, continue with step 4.
Select the Add button, and choose 'SQL Server ODBC Driver.'
Type in a Data Source Name. 'MSSQL' is the standard, or you may choose another name.
If you want to set default server and database names, type in the Server and Database Name fields.
Select OK to save the data source configuration.

Chapter Contents
Previous
Next
Top of Page