The source data (`mydblib.RDTAB78`) used for creating the test tables is dynamically generated at the beginning of the script using a DATA Step. Although these tables reside in a library (`mydblib`) that is configured to point to an external database management system (DBMS) via SAS/ACCESS, the initial content is internal to the script. The script tests SAS's ability to manipulate data and create new tables within this DBMS.
1 Code Block
PROC DELETE
Explanation : This block deletes the test tables `RDTAB78`, `CRTAB78A`, and `CRTAB78B` from the `mydblib` library if they exist. This ensures a clean environment for each script execution and prevents errors from creating tables that already exist.
Explanation : Creates a table named `RDTAB78` in the `mydblib` library. This table is populated with 100 observations, where variables `x` and `y` take values from 1 to 10. This table will serve as the source for subsequent `CREATE TABLE AS SELECT` operations.
Copied!
data mydblib.RDTAB78;
do x = 1 to 10;
do y = 1 to 10;
output; end; end; run;
1
DATA mydblib.RDTAB78;
2
DO x = 1 to 10;
3
DO y = 1 to 10;
4
OUTPUT; END; END; RUN;
3 Code Block
PROC SQL Data
Explanation : This block disables direct SQL execution by the DBMS (`options nodbidirectexec`). It uses `PROC SQL` to create two new tables (`CRTAB78A` and `CRTAB78B`) in `mydblib` from `RDTAB78`. SAS executes the `SELECT` clause logic internally, without delegating it to the DBMS. `CRTAB78A` contains variable `y` for all observations where `x` is greater than 5, and `CRTAB78B` contains distinct values of `y` for the same conditions.
Copied!
options nodbidirectexec;
proc sql noerrorstop;
create table mydblib.CRTAB78A as
select y from mydblib.RDTAB78
where x gt 5 order by y;
/* WITH IP TRIGGER */
create table mydblib.CRTAB78B as
select distinct y from mydblib.RDTAB78
where x gt 5 order by y;
quit;
1
options nodbidirectexec;
2
3
PROC SQL noerrorstop;
4
5
create TABLE mydblib.CRTAB78A as
6
select y from mydblib.RDTAB78
7
where x gt 5 order BY y;
8
9
/* WITH IP TRIGGER */
10
create TABLE mydblib.CRTAB78B as
11
select distinct y from mydblib.RDTAB78
12
where x gt 5 order BY y;
13
14
QUIT;
4 Code Block
DATA STEP Data
Explanation : These DATA Steps copy tables `CRTAB78A` and `CRTAB78B`, which were created without direct SQL execution, to the temporary `WORK` library under the names `noexeA` and `noexeB`. This allows them to be kept for later comparison.
Copied!
data work.noexeA; set mydblib.CRTAB78A; by y; run;
data work.noexeB; set mydblib.CRTAB78B; by y; run;
1
2
DATA work.noexeA;
3
SET mydblib.CRTAB78A;
4
BY y;
5
6
RUN;
7
DATA work.noexeB;
8
SET mydblib.CRTAB78B;
9
BY y;
10
11
RUN;
12
5 Code Block
PROC DELETE
Explanation : This block deletes tables `CRTAB78A` and `CRTAB78B` from the `mydblib` library. This step is necessary to be able to recreate these same tables immediately after, but this time with the direct SQL execution option enabled.
Explanation : This block enables direct SQL execution by the DBMS (`options dbidirectexec`). It uses `PROC SQL` to recreate tables `CRTAB78A` and `CRTAB78B` in `mydblib`. Unlike the previous block, SAS attempts to delegate the execution of the `SELECT` clause directly to the underlying DBMS. This can potentially improve performance by leveraging the DBMS's capabilities.
Copied!
options dbidirectexec;
proc sql noerrorstop;
create table mydblib.CRTAB78A as
select y from mydblib.RDTAB78
where x gt 5 order by y;
create table mydblib.CRTAB78B as
select distinct y from mydblib.RDTAB78
where x gt 5 order by y;
quit;
1
options dbidirectexec;
2
3
PROC SQL noerrorstop;
4
5
create TABLE mydblib.CRTAB78A as
6
select y from mydblib.RDTAB78
7
where x gt 5 order BY y;
8
9
10
11
create TABLE mydblib.CRTAB78B as
12
select distinct y from mydblib.RDTAB78
13
where x gt 5 order BY y;
14
15
QUIT;
7 Code Block
DATA STEP Data
Explanation : These DATA Steps copy tables `CRTAB78A` and `CRTAB78B`, which were created with direct SQL execution, to the temporary `WORK` library under the names `exeA` and `exeB`. These tables will be used for the final comparison.
Copied!
data work.exeA; set mydblib.CRTAB78A; by y; run;
data work.exeB; set mydblib.CRTAB78B; by y; run;
1
2
DATA work.exeA;
3
SET mydblib.CRTAB78A;
4
BY y;
5
6
RUN;
7
DATA work.exeB;
8
SET mydblib.CRTAB78B;
9
BY y;
10
11
RUN;
12
8 Code Block
PROC COMPARE
Explanation : This block uses `PROC COMPARE` to check for equivalence between tables created without (`noexeA`, `noexeB`) and with (`exeA`, `exeB`) direct SQL execution. The `ERROR` option flags differences, `BRIEFSUMMARY` provides a concise summary of the comparison results, and `NOTE` displays notes generated by the procedure. The goal is to confirm that both methods yield the same datasets.
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: createTableAsSelect.sas, TITLE: Sample Programs, PRODUCT: SAS/ACCESS Software for Relational Databases, SYSTEM: z/OS, UNIX, WINDOWS, REF: SAS/ACCESS 9 for Relational Databases: Reference
Related Documentation
Aucune documentation spécifique pour cette catégorie.
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.