code simplification

Not applicable
Posts: 1

code simplification


I am trying to create zero observation dataset for dm sdtm domain.


  CREATE TABLE dm_Sdtm ( STUDYID  char(12) LABEL = 'Study Identifier',

                                            DOMAIN   char(2)  LABEL = 'Domain Abbreviation',

                                            USUBJID  char(22) LABEL = 'Unique subject identifier',

                                            SUBJID   char(6)  LABEL = 'Subject identifier for the study',

                                            RFSTDTC  char(20) LABEL = 'Subject Reference Start Date/Time',

                                            RFENDTC  char(20) LABEL = 'Subject Reference End Date/Time',

                                            RFXSTDTC char(20) LABEL = 'Date/Time of First Study Treatment',

                                           RFXENDTC char(20) LABEL = 'Date/Time of Last Study Treatment'


If there are say for example 50 variables in metadata obviously it will be harder and will take long time to use above code for creating zero observation dataset.

can I please know any other best ways to simply the code and reuse it for other domains as well.

Kind regards

Super User
Super User
Posts: 8,127

Re: code simplification

Put the metadata into a table and generate the code from the metadata.  The fields in SASHELP.VCOLUMN are good ones to use for your metadata.

data metadata ;

  if 0 then set sashelp.vcolumn;

  keep libname memname name type length varnum label format informat ;

  libname = 'SDTM';


  varnum + 1;

  input name type length label & ;


STUDYID  char 12  Study Identifier

DOMAIN   char 2   Domain Abbreviation

USUBJID  char 22  Unique subject identifier

SUBJID   char 6   Subject identifier for the study

RFSTDTC  char 20  Subject Reference Start Date/Time

RFENDTC  char 20  Subject Reference End Date/Time

RFXSTDTC char 20  Date/Time of First Study Treatment

RFXENDTC char 20  Date/Time of Last Study Treatment


I would recommend generating a DATA step instead of SQL code as it will be more meaningful to SAS programmers and more flexible.

If your tables are small then you can generate the syntax into a macro variable with an SQL query.

Here is code to generate ATTRIB statements for each variable.

%let memname = dm_Sdtm;

proc sql noprint ;

select catx(' ','attrib',name,'length='

            ,case when type='char' then '$' else ' ' end


            ,case when informat = ' ' then ' '

                  else catx('=','informat',quote(trim(informat))) end

            ,case when format = ' ' then ' '

                  else catx('=','format',quote(trim(format))) end

            ,case when label = ' ' then ' '

                  else catx('=','label',quote(trim(label))) end


       , varnum

   into :attrib separated by ';'

      , :dummy

   from metadata

   where memname="&memname"

   order by varnum



Here is code to create an empty table.

data &memname ;

   &attrib ;

   stop ;


Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation