code simplification

Reply
N/A
Posts: 1

code simplification

Hello,

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

PROC SQL;

  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'

QUIT;

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: 7,076

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';

  memname='dm_Sdtm';

  varnum + 1;

  input name type length label & ;

cards4;

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

            ,length

            ,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

;

quit;


Here is code to create an empty table.


data &memname ;

   &attrib ;

   stop ;

run;

Ask a Question
Discussion stats
  • 1 reply
  • 199 views
  • 0 likes
  • 2 in conversation