Bulk Loading Data to Snowflake via SAS/ACCESS

The script initializes a global macro `bl_internal_stage` to configure the bulk load mode in Snowflake. It then preemptively deletes an existing target table (`mydblib.SNBLKTAB`) to ensure a clean execution. A temporary SAS^© dataset (`work.SNBLKDAT`) is created with example data. Finally, a new Snowflake table is created via `PROC SQL` using the temporary dataset as the source, enabling the `BULKLOAD=YES` option and configuring the internal 'stage'. The script concludes with a `PROC PRINT` to display the content of the newly loaded table in Snowflake, thereby verifying the success of the operation.

Data Analysis

Type : INTERNAL_CREATION

The source data `work.SNBLKDAT` is created directly and entirely within the script via a `DATA STEP` using inline data (`cards;`).

1 Code Block

MACRO GLOBAL

Explanation :
This block defines and initializes a global macro named `bl_internal_stage`. This macro is intended to specify the type of internal 'stage' (e.g., 'user', 'table', or a specific 'internal stage') to be used by Snowflake during the bulk loading operation via SAS/ACCESS. The empty initialization allows for later configuration.

Copied!

1	%GLOBAL bl_internal_stage;
2	%let bl_internal_stage =;
3

2 Code Block

PROC DELETE

Explanation :
`PROC DELETE` is used to preemptively delete the `SNBLKTAB` table from the `mydblib` library (which represents the connection to Snowflake). This ensures that each script execution starts with a clean slate, preventing errors due to the prior existence of the table.

Copied!

1	PROC DELETE DATA=mydblib.SNBLKTAB; RUN;

3 Code Block

DATA STEP Data

Explanation :
This `DATA STEP` creates a temporary SAS dataset named `SNBLKDAT` in the `work` library. It defines the variables `name`, `age`, `sex`, and `bdate`, then populates the dataset with example data directly included via the `cards;` statement. This dataset will serve as the source for bulk loading to Snowflake.

Copied!

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

PROC SQL

Explanation :
`PROC SQL` is used here to create a new table `SNBLKTAB` in the Snowflake database (via the `mydblib` library). The `BULKLOAD=YES` clause enables SAS/ACCESS bulk loading functionality, optimizing data transfer. The `BL_INTERNAL_STAGE=&bl_internal_stage` option allows specifying the internal Snowflake 'stage' using the previously defined global macro. The table is populated by a full selection (`select *`) from the temporary SAS dataset `work.SNBLKDAT`.

Copied!

1	PROC SQL;
2	create TABLE mydblib.SNBLKTAB (
3	BULKLOAD=YES
4	BL_INTERNAL_STAGE=&bl_internal_stage
5	) as select * from work.SNBLKDAT;
6	QUIT;

5 Code Block

PROC PRINT

Explanation :
This block uses `PROC PRINT` to display the contents of the `SNBLKTAB` table located in the `mydblib` library (the Snowflake table). The `date7.` format is applied to the `bdate` variable for better readability. A title is also added to the output. This step serves to visually verify that the data has been correctly loaded into Snowflake.

Copied!

1	PROC PRINT DATA=mydblib.SNBLKTAB;
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.

Copyright Info : S A S S A M P L E L I B R A R Y; NAME: bulkload.sas; TITLE: Sample Programs; PRODUCT: SAS/ACCESS to Snowflake

Retour à la liste