The script begins with a cleanup phase using `PROC DELETE` to remove any existing test tables (`testblkld1`, `testblkld2`) in the `mydblib` library. Next, a working dataset (`work.testblkld`) is created in memory with data specified directly in the script (`cards`). Finally, the `work.testblkld` data is bulk loaded into the Greenplum database using two distinct approaches: the first via a `CREATE TABLE AS SELECT` statement in `PROC SQL` with `BULKLOAD` options enabled directly on the target table, and the second via a `DATA` step which also uses `BULKLOAD` options during the declaration of the target table `mydblib.testblkld2`.
Data Analysis
Type : INTERNAL_CREATION
The source dataset `work.testblkld` is created directly within the SAS script via a `DATA` step and the `cards;` statement to define inline data. The target tables `mydblib.testblkld1` and `mydblib.testblkld2` are external tables in a Greenplum database, but the source data is internal to the script.
1 Code Block
PROC DELETE
Explanation : These `PROC DELETE` statements are used to delete tables `testblkld1` and `testblkld2` from the `mydblib` library if they exist. This ensures a clean environment for script execution and avoids errors from creating existing tables. `mydblib` is a SAS/ACCESS library connected to an external database.
Explanation : This `DATA` step creates a temporary dataset named `work.testblkld`. It contains four variables: `name` (character), `age` (numeric), `sex` (character), and `bdate` (numeric, formatted as a SAS date from an `mmddyy.` format). The data is provided inline via the `cards;` statement, meaning the dataset is created directly from the values specified in the script.
Copied!
data work.testblkld;
input name $ age sex $ bdate mmddyy.;
cards;
amy 3 f 030185
bill 12 m 121277
charlie 35 m 010253
david 19 m 101469
elinor 42 f 080845
pearl 78 f 051222
vera 96 f 101200
frank 24 m 092663
georgia 1 f 040687
henry 46 m 053042
joann 27 f 020461
buddy 66 m 101432
;
run;
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;
3 Code Block
PROC SQL
Explanation : This block uses `PROC SQL` to create a new table `testblkld1` in the `mydblib` library (which represents the Greenplum connection). The `BULKLOAD=YES` option enables bulk loading. The `BL_PORT`, `BL_HOST`, and `BL_PROTOCOL="gpfdist"` options are specific parameters for configuring Greenplum's GPFDIST protocol, allowing optimized data transfer. `bl_format='CSV'` specifies the format of the data sent. The table is created by selecting all columns from the `work.testblkld` dataset.
Copied!
proc sql;
create table mydblib.testblkld1
(BULKLOAD=YES
BL_PORT=&port
BL_HOST=&host
BL_PROTOCOL="gpfdist"
bl_format='CSV')
as select * from work.testblkld;
quit;
1
PROC SQL;
2
create TABLE mydblib.testblkld1
3
(BULKLOAD=YES
4
BL_PORT=&port
5
BL_HOST=&host
6
BL_PROTOCOL="gpfdist"
7
bl_format='CSV')
8
as select * from work.testblkld;
9
QUIT;
4 Code Block
DATA STEP
Explanation : This block uses a `DATA` step to create the `testblkld2` table in the `mydblib` library. As in the `PROC SQL` example, the `BULKLOAD=YES`, `BL_PORT`, `BL_HOST`, and `BL_PROTOCOL="gpfdist"` options are specified directly in the `DATA` statement for the target table, thus enabling bulk loading to Greenplum. The `set work.testblkld;` statement indicates that the data to be loaded comes from the previously created temporary dataset `work.testblkld`.
Copied!
data mydblib.testblkld2 (
BULKLOAD=YES
BL_PORT=&port
BL_HOST=&host
BL_PROTOCOL="gpfdist"
);
set work.testblkld;
run;
1
DATA mydblib.testblkld2 (
2
BULKLOAD=YES
3
BL_PORT=&port
4
BL_HOST=&host
5
BL_PROTOCOL="gpfdist"
6
);
7
SET work.testblkld;
8
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.
Copyright Info : SAS SAMPLE LIBRARY, NAME: bulkload.sas, TITLE: Sample Programs, PRODUCT: SAS/ACCESS to Greenplum, SYSTEM: z/OS, UNIX, WINDOWS, REF: SAS/ACCESS 9 for Relational Databases: Reference
SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration. WeAreCAS is an independent community site and is not affiliated with SAS Institute Inc.
This site uses technical and analytical cookies to improve your experience.
Read more.