Generating PROC SQL Code to Create a Table from a SAS Data Set :: SAS(R) Clinical Standards Toolkit 1.7: User's Guide, Second Edition

The %CSTUTILSQLGENERATETABLE Macro

The %CSTUTILSQLGENERATETABLE macro creates code that enables you to create a table from a SAS data set. The type of code to create is specified by the _cstSQLType parameter: SAS PROC SQL, ANSI SQL, or Oracle SQL. The SAS data set is specified by the _cstSourceDS parameter.

The code created by the %CSTUTILSQLGENERATETABLE macro serves as a template that you can modify. The template code handles more than 90% of the SAS data sets that are passed to it. You might need to perform these tasks:

Modify the template code for certain conditions that could pose a problem, such as nested quotation marks in the data.
For ANSI SQL and Oracle SQL, review modified reserved words. The macro identifies reserved words and appends a reserved word with __SQL1 (single underline, single underline, SQL1).
For ANSI SQL and Oracle SQL, review and modify the SQL code as needed.

Example: Generating SAS PROC SQL Code

The following example demonstrates generating SAS PROC SQL code for the CDISC SDTM AE data set:

libname test 'c:\test\data';
%cstutilsqlgeneratetable(_cstDSName=AE,_cstDSLibraryIn=SDTM32,_cstDSLibraryOut=test,
                         _cstSQLFile=c:\test\create_sasSQL.sas,_cstSQLType=SAS);

The resulting SAS PROC SQL code is written to the create_sasSQL.sas file, which is specified by the _cstSQLFile parameter. The resulting table generated by the SQL code is written to the test library, which is specified by the _cstDSLibraryOut parameter.

The following is an excerpt of the generated code in the create_sasSQL.sas file:

proc sql;
   create table work.cst7495 (label="Adverse Events")
   (STUDYID char(40) label="Study Identifier", DOMAIN char(8) label="Domain Abbreviation",...);
   insert into work.cst7495
   values ('SASCSTDEMODATA' , 'AE' , 'S001P002' , 1, '' , '' , '' , 'ABDOMINAL PAIN' , '' , '' ,...)
   values ('SASCSTDEMODATA' , 'AE' , 'S001P003' , 2, '' , '' , '' , 'ABDOMINAL CRAMP' , '' , 'Abdominal...)
   values ('SASCSTDEMODATA' , 'AE' , 'S001P003' , 3, '' , '' , '' , 'RASH' , '' , 'Rash' , 10037844,...)
.
.
.
   ;
   create table test.AE (label="Adverse Events")
   as select * from work.cst7495 order by STUDYID, USUBJID, AEDECOD, AESTDTC
   ;
   drop table work.cst7495
   ;
quit;

Note: The line

(STUDYID
                     char(40) label="Study Identifier", DOMAIN char(8) label="Domain
                     Abbreviation",...)

came from the call to %CSTUTILSQLCOLUMNDEFINITION.

After you submit the create_sasSQL.sas file in a SAS session, the AE data set is created in the test library.

The following display shows the AE data set.

AE Data Set

The following display shows that, in addition to the data, metadata (such as the label and the sort order) of the AE data set is retained.

AE Data Set Metadata

Example: Generating ANSI SQL Code

The following example demonstrates generating ANSI SQL code for the CDISC SDTM AE data set:

%cstutilsqlgeneratetable(_cstDSName=AE,_cstDSLibraryIn=SDTM32,_cstDSLibraryOut=test,
                         _cstSQLFile=c:\test\create_ansiSQL.sas,_cstSQLType=ANSI);

The following is an excerpt of the generated code in the create_ansiSQL.sas file:

create table AE
(STUDYID varchar(40), DOMAIN varchar(8), USUBJID varchar(40), AESEQ numeric, AEGRPID varchar(40),...);
insert into AE
   values ('SASCSTDEMODATA' , 'AE' , 'S001P002' , 1, '' , '' , '' , 'ABDOMINAL PAIN' , '' , '' , NULL,...)
   values ('SASCSTDEMODATA' , 'AE' , 'S001P003' , 2, '' , '' , '' , 'ABDOMINAL CRAMP' , '' , ...)
   values ('SASCSTDEMODATA' , 'AE' , 'S001P003' , 3, '' , '' , '' , 'RASH' , '' , 'Rash' , 10037844 ...)
   values ('SASCSTDEMODATA' , 'AE' , 'S001P005' , 4, '' , '' , '' , 'ABDOMINAL CRAMP' , '' ...)
   .
   .
   .
   values ('SASCSTDEMODATA' , 'AE' , 'S003P019' , 106, '' , '' , '' , 'HEARTBURN-LIKE DYSPEPSIA'...)
;

Note: The line

(STUDYID
                     varchar(40), DOMAIN varchar(8), USUBJID varchar(40), AESEQ numeric,
                     AEGRPID varchar(40),...)

came from the call to %CSTUTILSQLCOLUMNDEFINITION.

Example: Generating Oracle SQL Code

The following example demonstrates generating Oracle SQL code for the CDISC SDTM AE data set:

%cstutilsqlgeneratetable(_cstDSName=AE,_cstDSLibraryIn=SDTM32,_cstDSLibraryOut=test,
                         _cstSQLFile=c:\test\create_oracleSQL.sas,_cstSQLType=ORACLE);

The following is an excerpt of the generated code in the create_oracleSQL.sas file:

create table AE
(STUDYID varchar2(40), DOMAIN__SQL1 varchar2(8), USUBJID varchar2(40), AESEQ numeric, AEGRPID...);
insert into AE (STUDYID, DOMAIN__SQL1, USUBJID, AESEQ, AEGRPID, AEREFID, AESPID, AETERM, AEMODIFY...)
values ('SASCSTDEMODATA' , 'AE' , 'S001P002' , 1, '' , '' , '' , 'ABDOMINAL PAIN' , '' , '' , NULL,...)
insert into AE (STUDYID, DOMAIN__SQL1, USUBJID, AESEQ, AEGRPID, AEREFID, AESPID, AETERM, AEMODIFY,...)
values ('SASCSTDEMODATA' , 'AE' , 'S001P003' , 2, '' , '' , '' , 'ABDOMINAL CRAMP' , '' ,...)
insert into AE (STUDYID, DOMAIN__SQL1, USUBJID, AESEQ, AEGRPID, AEREFID, AESPID, AETERM, AEMODIFY,...)
values ('SASCSTDEMODATA' , 'AE' , 'S001P003' , 3, '' , '' , '' , 'RASH' , '' , 'Rash' , 10037844,...)
.
.
.
insert into AE (STUDYID, DOMAIN__SQL1, USUBJID, AESEQ, AEGRPID, AEREFID, AESPID, AETERM, AEMODIFY,...)
values ('SASCSTDEMODATA' , 'AE' , 'S003P019' , 106, '' , '' , '' , 'HEARTBURN-LIKE DYSPEPSIA' , '' ,...);
;

Note: The line

(STUDYID
                     varchar2(40), DOMAIN__SQL1 varchar2(8), USUBJID varchar2(40), AESEQ
                     numeric, AEGRPID...);

came from the call to %CSTUTILSQLCOLUMNDEFINITION.

Notice the DOMAIN column from the AE data set has been renamed in the generated Oracle SQL code as DOMAIN__SQL1. The word “domain” is a reserved word in Oracle SQL. Therefore, the macro appends __SQL1. You must decide where to change this column name: In the data set before submitting the macro or in the generated Oracle SQL code (to rename the column in the generated table).

After submitting the macro, the SAS log file contains the following warning message:

[CSTLOGMESSAGE.CSTUTILSQLCOLUMNDEFINITION] WARNING: Column [DOMAIN ] is an ORACLE SQL
            RESERVED WORD - This column may need to be changed in the contributing SAS data set.
[CSTLOGMESSAGE.CSTUTILSQLCOLUMNDEFINITION] WARNING: Column [DOMAIN ] is being renamed to
            DOMAIN__SQL1 .