Generating PROC SQL Code to Create and Populate Data Sets :: SAS(R) Clinical Standards Toolkit 1.7: User's Guide, Second Edition

The %CSTUTILSQLCOLUMNDEFINITION Macro

The %CSTUTILSQLCOLUMNDEFINITION macro generates the SQL equivalent of the SAS ATTRIB statement in a SAS data set. The structure and content of the returned code differs based on what type of SQL code you choose to generate: SAS, ANSI, or Oracle.

The macro checks each column name in the SAS data set against a list of reserved words for both ANSI SQL and Oracle SQL. If a reserved word is found in the SAS data set, a message appears in the SAS log file, and the macro appends __SQL1 (single underline, single underline, SQL1) to the column name in the SAS data set. In the generated code, you must decide whether to modify the column name in the generated code or rename the column in the SAS data set before submitting the macro.

The results of the macro are intended to be used by the %CSTUTILSQLGENERATETABLE macro. However, you can use the results with other macros as needed. The %CSTUTILSQLCOLUMNDEFINITION macro can be run stand-alone.

Example: Generating SAS SQL

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

libname sdtm32 'c:\cstSampleLibrary\cdisc-sdtm-3.2-1.7\sascstdemodata\data';
%let _cstColumnDef=;
%cstutilsqlcolumndefinition(_cstSourceDS=sdtm32.ae,_cstSQLColDef=_cstColumnDef, 
                            _cstSQLType=SAS);
%put &=_cstColumnDef;

The %CSTUTILSQLGENERATETABLE macro populates the &_cstColumnDef macro variable with the following SAS PROC SQL column description values:

_CSTCOLUMNDEF=(
STUDYID char(40) label="Study Identifier",
DOMAIN char(8) label="Domain Abbreviation",
USUBJID char(40) label="Unique Subject Identifier",
AESEQ numeric label="Sequence Number",
AEGRPID char(40) label="Group ID",
AEREFID char(40) label="Reference ID",
AESPID char(40) label="Sponsor-Defined Identifier",
AETERM char(200) label="Reported Term for the Adverse Event",
AEMODIFY char(200) label="Modified Reported Term",
AELLT char(100) label="Lowest Level Term",
AELLTCD numeric label="Lowest Level Term Code",
AEDECOD char(200) label="Dictionary-Derived Term",
AEPTCD numeric label="Preferred Term Code",
AEHLT char(100) label="High Level Term",
AEHLTCD numeric label="High Level Term Code",
AEHLGT char(100) label="High Level Group Term",
AEHLGTCD numeric label="High Level Group Term Code",
AECAT char(40) label="Category for Adverse Event",
AESCAT char(40) label="Subcategory for Adverse Event",
AEPRESP char(2) label="Pre-Specified Adverse Event",
AEBODSYS char(80) label="Body System or Organ Class",
AEBDSYCD numeric label="Body System or Organ Class Code",
AESOC char(80) label="Primary System Organ Class",
AESOCCD numeric label="Primary System Organ Class Code",
AELOC char(40) label="Location of Event",
AESEV char(20) label="Severity/Intensity",
AESER char(2) label="Serious Event",
AEACN char(40) label="Action Taken with Study Treatment",
AEACNOTH char(200) label="Other Action Taken",
AEREL char(40) label="Causality",
AERELNST char(40) label="Relationship to Non-Study Treatment",
AEPATT char(20) label="Pattern of Adverse Event",
AEOUT char(40) label="Outcome of Adverse Event",
AESCAN char(2) label="Involves Cancer",
AESCONG char(2) label="Congenital Anomaly or Birth Defect",
AESDISAB char(2) label="Persist or Signif Disability/Incapacity",
AESDTH char(2) label="Results in Death",
AESHOSP char(2) label="Requires or Prolongs Hospitalization",
AESLIFE char(2) label="Is Life Threatening",
AESOD char(2) label="Occurred with Overdose",
AESMIE char(2) label="Other Medically Important Serious Event",
AECONTRT char(2) label="Concomitant or Additional Trtmnt Given",
AETOXGR char(20) label="Standard Toxicity Grade",
AESTDTC char(64) label="Start Date/Time of Adverse Event",
AEENDTC char(64) label="End Date/Time of Adverse Event",
AESTDY numeric label="Study Day of Start of Adverse Event",
AEENDY numeric label="Study Day of End of Adverse Event",
AEDUR char(64) label="Duration of Adverse Event",
AEENRF char(20) label="End Relative to Reference Period",
AEENRTPT char(40) label="End Relative to Reference Time Point",
AEENTPT char(40) label="End Reference Time Point" )

Example: Generating ANSI SQL

The following example demonstrates generating ANSI SQL code for the SDTM AE domain:

%cstutilsqlcolumndefinition(_cstSourceDS=sdtm32.ae,_cstSQLColDef=_cstColumnDef,
                            _cstSQLType=ANSI);
%put &=_cstColumnDef;

The %CSTUTILSQLGENERATETABLE macro populates the &_cstColumnDef macro variable with the following ANSI SQL column description values:

_CSTCOLUMNDEF=(
STUDYID varchar(40),
DOMAIN varchar(8),
USUBJID varchar(40),
AESEQ numeric,
AEGRPID varchar(40),
AEREFID varchar(40),
AESPID varchar(40),
AETERM varchar(200),
AEMODIFY varchar(200),
AELLT varchar(100),
AELLTCD numeric,
AEDECOD varchar(200),
AEPTCD numeric,
AEHLT varchar(100),
AEHLTCD numeric,
AEHLGT varchar(100),
AEHLGTCD numeric,
AECAT varchar(40),
AESCAT varchar(40),
AEPRESP varchar(2),
AEBODSYS varchar(80),
AEBDSYCD numeric,
AESOC varchar(80),
AESOCCD numeric,
AELOC varchar(40),
AESEV varchar(20),
AESER varchar(2),
AEACN varchar(40),
AEACNOTH varchar(200),
AEREL varchar(40),
AERELNST varchar(40),
AEPATT varchar(20),
AEOUT varchar(40),
AESCAN varchar(2),
AESCONG varchar(2),
AESDISAB varchar(2),
AESDTH varchar(2),
AESHOSP varchar(2),
AESLIFE varchar(2),
AESOD varchar(2),
AESMIE varchar(2),
AECONTRT varchar(2),
AETOXGR varchar(20),
AESTDTC varchar(64),
AEENDTC varchar(64),
AESTDY numeric,
AEENDY numeric,
AEDUR varchar(64),
AEENRF varchar(20),
AEENRTPT varchar(40),
AEENTPT varchar(40) )

Example: Generating Oracle SQL

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

%cstutilsqlcolumndefinition(_cstSourceDS=sdtm32.ae,_cstSQLColDef=_cstColumnDef,
                            _cstSQLType=ORACLE);
%put &=_cstColumnDef;

The %CSTUTILSQLGENERATETABLE macro populates the &_cstColumnDef macro variable with the following Oracle SQL column description values:

_CSTCOLUMNDEF=(
STUDYID varchar2(40),
DOMAIN__SQL1 varchar2(8),
USUBJID varchar2(40),
AESEQ numeric,
AEGRPID varchar2(40),
AEREFID varchar2(40),
AESPID varchar2(40),
AETERM varchar2(200),
AEMODIFY varchar2(200),
AELLT varchar2(100),
AELLTCD numeric,
AEDECOD varchar2(200),
AEPTCD numeric,
AEHLT varchar2(100),
AEHLTCD numeric,
AEHLGT varchar2(100),
AEHLGTCD numeric,
AECAT varchar2(40),
AESCAT varchar2(40),
AEPRESP varchar2(2),
AEBODSYS varchar2(80),
AEBDSYCD numeric,
AESOC varchar2(80),
AESOCCD numeric,
AELOC varchar2(40),
AESEV varchar2(20),
AESER varchar2(2),
AEACN varchar2(40),
AEACNOTH varchar2(200),
AEREL varchar2(40),
AERELNST varchar2(40),
AEPATT varchar2(20),
AEOUT varchar2(40),
AESCAN varchar2(2),
AESCONG varchar2(2),
AESDISAB varchar2(2),
AESDTH varchar2(2),
AESHOSP varchar2(2),
AESLIFE varchar2(2),
AESOD varchar2(2),
AESMIE varchar2(2),
AECONTRT varchar2(2),
AETOXGR varchar2(20),
AESTDTC varchar2(64),
AEENDTC varchar2(64),
AESTDY numeric,
AEENDY numeric,
AEDUR varchar2(64),
AEENRF varchar2(20),
AEENRTPT varchar2(40),
AEENTPT varchar2(40) )