Bulk Loading Data

This program uses global macros to configure bulk loading options (data file, host, port). Before loading, it performs a cleanup by deleting existing target tables. A temporary SAS^© data table is then created in memory from in-line data. Finally, it presents two approaches for bulk loading: the first via a `CREATE TABLE AS SELECT` statement in PROC SQL with BULKLOAD options, and the second via a DATA STEP where BULKLOAD options are specified directly on the DATA statement. The bulk loading options are parameterized by macro variables.

Data Analysis

Type : INTERNAL_CREATION

The `work.testblkld` data source is created directly in the script via a DATA step and the `cards;` statement with in-line data (amy, bill, etc.). No unmanaged external data is required.

1 Code Block

Macro Definition

Explanation :
This block declares and initializes global macros (`BLDATF`, `BLHOST`, `BLPORT`) that will be used later to specify bulk loading operation parameters, such as the data file, host, and target port.

Copied!

1	%GLOBAL BLDATF;
2	%GLOBAL BLHOST;
3	%GLOBAL BLPORT;
4
5	%let BLDATF =;
6	%let BLHOST =;
7	%let BLPORT =;

2 Code Block

OPTIONS Statement

Explanation :
Sets a SAS system option (`SAS_HADOOP_RESTFUL`) to 1. This option is likely related to configuring a RESTful connection with a Hadoop environment, which is common with SAS/ACCESS for certain database management systems.

Copied!

1	options SET=SAS_HADOOP_RESTFUL=1;

3 Code Block

PROC DELETE

Explanation :
These PROC DELETE calls are used to delete the `testblkld1` and `testblkld2` tables from the `mydblib` library if they exist. This ensures a clean environment and avoids potential errors when recreating these tables.

Copied!

1
2	PROC DELETE
3	DATA=mydblib.testblkld1;
4
5	RUN;
6	PROC DELETE
7	DATA=mydblib.testblkld2;
8
9	RUN;
10

4 Code Block

DATA STEP Data

Explanation :
This DATA step creates a temporary SAS table named `testblkld` in the `WORK` library. Data is read in-line using the `cards;` statement, defining the `name`, `age`, `sex`, and `bdate` variables with the `mmddyy.` date format.

Copied!

1	DATA work.testblkld;
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;

5 Code Block

PROC SQL

Explanation :
This block uses PROC SQL to create a new `testblkld1` table in the `mydblib` library. The `BULKLOAD=YES`, `BL_DATAFILE`, `BL_HOST`, and `BL_PORT` options are specified to enable and configure bulk loading, allowing optimized data transfer from the `work.testblkld` table.

Copied!

1	PROC SQL;
2	create TABLE mydblib.testblkld1
3	(BULKLOAD=YES
4	BL_DATAFILE=&bldatf
5	BL_HOST=&blhost
6	BL_PORT=&blport )
7	as select * from work.testblkld;
8	QUIT;

6 Code Block

DATA STEP

Explanation :
This block uses a DATA step to create a `testblkld2` table in the `mydblib` library. As with PROC SQL, the `BULKLOAD=YES`, `BL_DATAFILE`, `BL_HOST`, and `BL_PORT` options are included in the DATA statement to perform a bulk load of data from `work.testblkld`.

Copied!

1	DATA mydblib.testblkld2 (
2	BULKLOAD=YES
3	BL_DATAFILE=&bldatf
4	BL_HOST=&blhost
5	BL_PORT=&blport );
6
7
8	SET work.testblkld;
9	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