Bulk Load via ODBC to SQL Server

This SAS^© script demonstrates how to use the BULKLOAD (bcp=yes) option with an ODBC LIBNAME to efficiently load data from a SAS^© table to a table in a SQL Server database. It includes creating a temporary internal dataset, deleting an existing table in the target database, creating the target table via a DATA STEP statement, and verifying the load via a PROC PRINT. The code is compatible with SAS^© Viya^™ 4.

Data Analysis

Type : MIXTE

The source dataset `work.DUBLKDAT` is created internally within the script using the DATALINES/CARDS statement. This dataset is then used to load data into a table `mybulk.DUBLKTAB` residing in an external database via the configured ODBC connection.

1 Code Block

LIBNAME Statement

Explanation :
Declares a LIBNAME named `mybulk` to connect to an ODBC data source. The macro variables `&dbms` and `&connopt` must be defined beforehand (for example, in an autoexec or a parent script). The `bcp=yes` option enables bulk load mode to optimize performance when inserting large amounts of data.

Copied!

1	LIBNAME mybulk &dbms &connopt bcp=yes;

2 Code Block

PROC DELETE

Explanation :
Deletes the `DUBLKTAB` table from the `mybulk` library (which points to the external database). This step is often used to ensure a clean state before recreating or reloading data into the table.

Copied!

1	PROC DELETE DATA=mybulk.DUBLKTAB;
2	RUN;

3 Code Block

DATA STEP Data

Explanation :
Creates a temporary dataset `work.DUBLKDAT` using a DATA STEP statement with embedded data via the CARDS option. This dataset contains information about individuals (name, age, sex, date of birth) and serves as the source for the bulk load.

Copied!

1	DATA work.DUBLKDAT;
2	INPUT name $ age sex $ bdate mmddyy.;
3	CARDS;
4	amy 3 f 030185
5	bill 12 m 121277
6	charlie 35 m 010253
7	david 19 m 101469
8	elinor 42 f 080845
9	pearl 78 f 051222
10	vera 96 f 101200
11	frank 24 m 092663
12	georgia 1 f 040687
13	henry 46 m 053042
14	joann 27 f 020461
15	buddy 66 m 101432
16	;
17	RUN;

4 Code Block

DATA STEP Data

Explanation :
Creates or replaces the `DUBLKTAB` table in the external database (via the `mybulk` LIBNAME) by loading the contents of the SAS dataset `work.DUBLKDAT` into it. Thanks to the `bcp=yes` option defined on the `mybulk` LIBNAME, this DATA STEP triggers a bulk load operation to the underlying SQL Server database.

Copied!

1	DATA mybulk.DUBLKTAB;
2	SET work.DUBLKDAT;
3	RUN;

5 Code Block

PROC PRINT

Explanation :
Displays the content of the `mybulk.DUBLKTAB` table (the table loaded into the external database) in the SAS log. The `date7.` format is applied to the `bdate` column for better date readability. This allows verification that the bulk load was successful.

Copied!

1	PROC PRINT DATA=mybulk.DUBLKTAB;
2	FORMAT bdate date7.;
3	title 'proc print of table';
4	RUN;

This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.

Retour à la liste