DBMSlist Macro - Batch Database Querying

This macro automates the 'chunking' process. It first counts the total number of observations in the source dataset (`dsn`). Then, it iterates through blocks (size defined by `bitesize`). In each iteration, it creates a temporary table (`gwhxxxx2`), calls two external macros (`%MakeList` to format the list of values, and `%RunQuery` to execute the SQL query) and aggregates the results into the target table (`newdsn`) via `PROC APPEND`. The script also handles a test mode to execute only a single iteration.

Data Analysis

Type : EXTERNAL

The macro expects an input dataset via the `dsn` parameter. It generates temporary tables (`gwhxxxx1` macro var, `gwhxxxx2`, `gwhxxxx3`) and a final table specified by `newdsn`.

1 Code Block

PROC SQL

Explanation :
Counts the total number of records in the source dataset to determine the number of passes needed. Stops execution if the table is empty.

Copied!

1	PROC SQL noprint;
2	select count(*) into :gwhxxxx1
3	from &dsn;
4	QUIT;
5
6	%IF &gwhxxxx1=0 %THEN %DO;
7	%put ====== WARNING: INPUT DATA SET &dsn is empty, macro ends =======;
8	%goto exit;
9	%END;

2 Code Block

DATA STEP Data

Explanation :
Main loop: calculates the number of passes, extracts a data subset (chunk) into `gwhxxxx2`, and calls auxiliary macros (not defined here) `%MakeList` and `%RunQuery` to process this batch.

Copied!

1	%let totpass=%sysevalf(&gwhxxxx1/&bitesize, ceil);
2	%IF &test=no %THEN %DO j=1 %to &gwhxxxx1 %BY &bitesize;
3	%let p=%sysevalf(&j/&bitesize, ceil);
4	%put ================= Starting pass &p of &totpass ==================;
5
6	DATA gwhxxxx2;
7	SET &dsn (firstobs=&j obs=%eval(&j+&bitesize-1));
8	RUN;
9
10	%MakeList(mylist, gwhxxxx2, &column, &vtype);
11	%RunQuery(&dbname, gwhxxxx3, &query);
12
13	/* ... Logique d'accumulation ... */
14	%END;

3 Code Block

DATA STEP / PROC APPEND Data

Explanation :
Aggregation logic: initializes the output table (`newdsn`) during the first iteration, and appends the results (`gwhxxxx3` returned by `%RunQuery`) in subsequent iterations via `PROC APPEND`.

Copied!

1	%IF &j=1 %THEN %DO;
2	DATA &newdsn;
3	SET gwhxxxx3;
4	RUN;
5	%END;
6	%ELSE %DO;
7	PROC APPEND base=&newdsn DATA=gwhxxxx3;
8	RUN;
9	%END;

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 : Garth W. Helf, 2001 "Can't Relate? A Primer on Using SAS With Your Relational Database", SUGI 27 Paper 155-27

Retour à la liste