SAS/ACCESS Software for Relational Databases: Reference

DBLOAD Procedure: ODBC Specifics

This section describes the statements that you use in the SAS/ACCESS interface to ODBC.

DBLOAD Procedure Statements for ODBC

To create and load an ODBC table, the SAS/ACCESS interface to ODBC uses the following statements in batch mode.

PROC DBLOAD DBMS=ODBC <DATA=<libref.>SAS-data-set>;

DSN|DATABASE|IN=<'>database-name<'>;

UID|USER=<'>username<'>;

PWD|PASSWORD|PW|PASS|USING=
<'>password<'>;

TABLE=<authorization-id.>table-name;

COMMIT=commit-frequency;

DELETEvariable-identifier-1<...variable-
identifier-n;>

ERRLIMIT=error-limit;

LABEL;

LIMIT=load-limit;

LIST<ALL|COLUMN|variable-identifier>;

NULLSvariable-identifier-1 = Y|N|D<...variable-identifier-n= Y|N>;

QUIT|EXIT;

RENAME|COLUMNvariable-identifier-1=<'>column-name-1<'>
<...variable-identifier-n = <'>column-name-n <'>>;

RESET ALL | variable-identifier-1<...variable-identifier-n >;

SQL DBMS-specific SQL-statement;

TYPE variable-identifier-1='column-type-1'
<...variable-identifier-n = 'column-type-n'>;

WHERESAS-where-expression;

LOAD;

RUN;

DSN | DATABASE | IN= <'>database-name<'>;

specifies the name of the database in which you want to store the new ODBC table. Database-name is limited to eight characters.

The database that you specify must already exist. If the database name contains the following special characters (_,$,@,#), you must enclose it in quotes. However, the ODBC standard recommends against using special characters in database names.

USER | UID= <'>username <'>;

enables you to connect to an ODBC database, such as SQL Server or AS/400, with a user ID that is different from the default ID.

The USER= and PASSWORD= statements are optional in ODBC. If you specify USER=, you must also specify PASSWORD=. If USER= is omitted, your default user ID is used.

PWD | PASSWORD | PW | PASS | USING=<'>password<'>;

specifies the ODBC password that is associated with your user ID.

The USER= and PASSWORD= statements are optional in ODBC because users have default user IDs. If you specify USER=, you must specify PASSWORD=.

DBLOAD Procedure Examples

The following example creates a new ODBC table, TESTUSER.EXCHANGE, from the DLIB.RATEOFEX data file. You must be granted the appropriate privileges in order to create new ODBC tables or views.

proc dbload dbms=odbc data=dlib.rateofex;
   dsn=sample; user=testuser; password=testpass;
   table=exchange;
   rename fgnindol=fgnindollars 
          4=dollarsinfgn;
   nulls updated=n fgnindollars=n 
         dollarsinfgn=n country=n;
   load;
run;

The next example only sends an ODBC SQL GRANT statement to the SAMPLE database and does not create a new table. Therefore, the TABLE= and LOAD statements are omitted.

proc dbload dbms=odbc;
   user=testuser;
   password=testpass;
   dsn=sample;
   sql grant select on testuser.exchange 
      to dbitest;
run;

Chapter Contents
Previous
Next
Top of Page