Chapter 8. DFsas: DFdiscover to SAS®

The DFNUM statement identifies the DFdiscover study number (248 in this example) for which the SAS® job is to be created.

The SASDIR statement identifies the full path name of the directory where the DFsas job file can be found and where the output SAS® job and data files are to be written.

The SASJOB statement identifies the name of the DFsas job file. This file includes all of the specifications needed to create the SAS® job and data files. This name also serves as the root name of the SAS® files to be created. In the example, test.sas will become the name of the SAS® job file, and test.d01 and test.d02 will become the names of the SAS® input data files.

The SASVERSION statement specifies the version of SAS® to be used with the SAS® job file created by DFsas. This statement is created by specifying the -SAS # option when generating the DFsas job file. If this option is not specified, the default of SAS® version 7 is used.

The CHECK statement is used to specify the desired output for check fields (i.e. those data fields defined by a single box which is either checked or not). In our example we are requesting that the labels specified in the study schema be written to the data files for all check fields (instead of the numeric codes, 0 and 1). All check fields must have labels defined in the study schema ^[17]. Note that local specification of codes at the variable level overrides specification of labels at the global level.

If labels are to be written, DFsas encloses the label in double quotes and removes any double quotes present in the label's text. Single quotes present in the label text are preserved.

The CHOICE statement is used to specify the desired output for choice fields (i.e. those fields like race, for which there are 2 or more response options). As with CHECK, the options are codes or labels. In the example, codes have been requested. If labels are requested DFsas will read them from the study schema. DFsas will enclose the label in double quotes and remove any double quotes present in the label's text. Single quotes present in the label text are preserved. If no labels are found, DFsas will default to the numeric codes. Note that local specification of codes at the variable level overrides specification of labels at the global level.

The VLABEL statement, followed by yes indicates that SAS® variable labels are to be written to the SAS® job file. These labels are read from the description of each field found in the study schema.

When writing variable labels, DFsas encloses the label in double quotes and removes any double quotes present in the variable description text. Single quotes present in the variable label are preserved.

The BLANK statement is used to identify how blank fields are to be written to the data files. In the example, all blank fields will be written as they are (i.e. they will remain blank).

The RETAIN QUOTES no statement instructs SAS to discard any quotation marks when reading string fields from the input data files. If no is changed to yes, quotation marks are preserved.

The MISSING statement is used to identify how fields that contain a DFdiscover missing code, are to be written to the data files. In the example MISSING all asis specifies that all missing codes are to be output as they are, (i.e. without any re-coding). The MISSING lost .L statement specifies that the missing value code .L is to be output in fields exported from missed data records.

The NOVISIT statement is used to specify a value to be used for all variables for visits that do not exist in the database for a subject, when constructing data records containing variables that are repeated across visits. The SAS® . (dot) missing value code has been used in the example.

The RECSTATUS command is used to select data records by record status. The default is to export all final, incomplete and missed data records.

The RECLEVEL command is used to select tdata records by workflow level. The default is to export records at all levels. Levels can be specified using a list of values and ranges, as in: RECLEVEL 3-5,7.

The MERGE statement followed by 'yes' specifies that the merge command needed to combine all of the data files into a single SAS® data set, should be included at the end of the SAS® job file. In cases where a more detailed merge command is needed, use MERGE no, and include a new merge specification at the top of the SAS® procedures section.

for SAS® version 6, and older:

for SAS® version 7, and newer:

DFRASTER (the DFdiscover CRF page identifier)

DFSTUDY (the DFdiscover study number)

DFPLATE (the DFdiscover plate number)

DFCREATE (the record creation date/time field)

DFMODIFY (the last date/time the record was modified)

DFSCREEN (screen value of the record status)

DFNUM.  Has a legal DFdiscover study number been specified?

SASDIR.  Does the SAS® directory exist?

SASJOB.  Has a SAS® job name been specified?

Reserved character, |.  Has the field delimiter been used inappropriately? DFsas checks for illegal use of | in the specification of fixed fields, default labels for check fields and recoded values for blanks and missing value codes.

Plate and variable numbers.  Have legal values been specified? DFsas checks the specified plates to verify that they have been defined for the study, and also verifies that the specified field numbers have been defined for each plate.

Field numbers do not contain extraneous characters (e.g. 12-, 12., 12a, a12, etc).

Field number ranges are legal (e.g. 12~55, and not: 55~12, 12~15~55, etc.)

If a string split is specified, is the field a string?

No more than one field qualifier is used on any field.

If a date qualifier is specified, is the field a date?

Variable name and label length.  DFsas checks the length of all variable names and labels in the DFsas job file and prints error messages and stops if there are any which exceed the limits of the specified SAS® version.

If the DFsas job contains a request to split a specified data field, DFsas uses the SASVERSION statement to determine the maximum length allowed for the variable names that it creates (see Impact of SAS® limits).

IMPUTE no.  Date fields are exported exactly as they appear in the database, regardless of what imputation method has been defined.

IMPUTE yes.  All 2-digit years are converted to 4 digit years using the pivot year defined for each date. In addition the imputation method defined in the study schema for each date field is used to convert missing days and/or months to legal values. If the imputation method is set to none only 2 digit to 4 digit year conversions are performed. When imputing dates, any nonsensical dates, i.e. dates with impossible values for day, month or year, are converted to the default DFdiscover missing code, *. If other missing value codes have been defined for the study, any dates that use them will retain their original missing value code.

INFORMAT dates.  Create SAS® informat statements for date fields that SAS® recognizes. DFdiscover allows some date formats that SAS® does not recognize as dates, (e.g. Jan 25, 2001). When this global statement is used, date fields with formats that SAS® does not allow are converted to string fields and a corresponding character informat statement is created.

INFORMAT all.  Convert all fields to type character, including dates.

INFORMAT all dates.  Use date formats for all date fields and convert all other fields to type character.

:j - julian value.  This option converts the date to a number representing the number of days since a date in 4712 BC. No informat statement is written to the SAS® job file as this is treated by SAS® as a number.

:o - original value.  This option turns off imputation to yield the value exactly as it appears in the database, overriding any global specification IMPUTE yes.

With this date qualifier a date informat statement is written to the SAS® job file using the original date format specified in the study schema. This option should only be used for fields that cannot contain a partial date. Otherwise SAS® will complain.

:c - calendar value.  This option converts 2 digit years to 4 digit years and performs imputation as specified in the study schema. If imputation leads to a nonsensical date it is converted to *, the DFdiscover default missing value code. When :c is specified the appropriate date informat statement is written to the SAS® job file overriding any global INFORMAT statement.

:s - string value.  Like the :o option, :s also turns off imputation to yield the value exactly as it appears in the database, but the field is considered to be a string and thus a character (not date) informat statement is written to the SAS® job file. This option allows one to output partial dates exactly as they appear in the database with a character informat that SAS® will accept.

:t5 - SAS time type TIME5.  This option indicates that the field is to be identified as the SAS® time type, TIME5, represented as HH:MM The appropriate SAS® informat statement is written to the SAS® job file for any fields so qualified.

:t8 - SAS time type TIME8.  This option indicates that such fields are to be identified as SAS time type TIME8, represented by HH:MM:SS. The appropriate SAS® informat statement is written to the SAS® job file for any fields so qualified.

The global statement INFORMAT dates is written to the DFsas job file.

If all dates in the study schema use the same pivot year, DFsas creates the following global statements:

OPTIONS YEARCUTOFF=yyyy    (1)
IMPUTE no

If all dates do not use the same pivot year a YEARCUTOFF statement cannot be generated because SAS® only allows one such statement. Instead, DFsas converts all dates to 4 digit years using the global statement IMPUTE yes.

If more than one output format is requested for date fields using the -d csoj option, DFsas creates a unique variable name for each of the output date variables by appending 1, 2, 3, 4 to the variable name. DFsas uses the SAS® version number to determine the maximum length allowed for these variable names.

DFNUM.  This keyword is followed by the DFdiscover study number of the database from which you plan to export a SAS® data set. For example:

DFNUM 248

SASJOB.  The file name specified after this keyword is the name of the DFsas job file and is also the root name for the SAS® job and summary data files created by DFsas.

The following sets the DFsas job name to job1:

SASJOB job1

The SAS® job file name is constructed by appending .sas to the specified job name, constructing a filename similar to job1.sas. A separate data file is created for each set of retrieval specifications introduced by the keyword RECORD or NORMALIZE. These summary data files are named by appending .d## (where ## is in the range 01 to 99, inclusive) to the job name (e.g. job1.d01, job1.d02). Summary data files are numbered in the order in which they appear in the DFsas job file.

SASVERSION.  When using the -C or -c option to create a default DFsas job file, the target SAS® version can be specified on the command line using the -SAS # option. SAS® version 7 is the default if the -SAS # is not specified. Each DFsas job file contains a SASVERSION statement regardless of whether or not the -SAS # is explicitly specified on the command line.

SASVERSION 7

SASDIR.  The SAS® job file and summary data files created by DFsas are written to the directory specified by the value of this keyword. A full path name must be given.

SASDIR /studies/mystudy/sas/baseline

RUNDIR.  If you plan to move the SAS® job and data files created by DFsas to another platform, such that the files will be processed by SAS® from a different file location than they were created by DFsas, use this keyword and specify the location where you intend to install the files. If a RUNDIR statement is present, DFsas will use this directory when creating filename statements in the SAS® job file. Without the RUNDIR statement DFsas uses SASDIR when creating filename statements. A full path name must be given.

RUNDIR C:\studies\mystudy\sas\baseline

LIBNAME.  DFsas includes support for SAS® libraries, and also allows users to create their own data set names for both the individual data files corresponding to DFdiscover plates, and for the merged data set that currently has the default name final. SAS® libraries are used to save a SAS® data set created during a SAS® run, and to use a previously saved SAS® data set. When libraries are not used, the SAS® data sets created during a SAS® run are temporary, and are removed when the run is completed.

SAS® libraries may be used by specifying the LIBNAME statement in the global specifications section of a DFsas job file as per the following example.

LIBNAME mylib1 `C:\studies\study254\sas\baselib'
LIBNAME mylib2 `C:\studies\study254\sas\aelib'

	Note
	DFsas will not create the LIBNAME if it does not exist. The user must do this.

Data set names may be specified for each input data file by including a DATA statement immediately following the RECORD statement used to introduce data set specifications for a plate in a DFsas job file, or immediately below a normalized data set.

RECORD 5
DATA mylib1.vitals

NORMALIZE if( length(medname)>0 )
DATA mylib2.meds

To change the name of the final merged data set, from the current default value of final, add the desired name of the merged data set to the global MERGE statement.

MERGE yes mylib1.baseline

OPTIONS.  A DFsas job file may include one or more OPTIONS statement to define SAS® options. For example,

OPTIONS YEARCUTOFF=1940
OPTIONS PAGESIZE=60 LINESIZE=72 NOCENTER

IDNAME.  When DFsas is used to create a DFsas job file it determines the field variable name assigned to the subject ID field from the first plate in the schema and writes this out as the global IDNAME statement. The variable name can be changed to any desired value, keeping in mind the SAS® restrictions on variable names. If the IDNAME statement does not appear in a DFsas job file, DFsas uses the name ID.

SUBJECTS.  This statement can be used to specify a list of subject IDs and ID ranges to be included in the SAS® data sets. The default setting is

SUBJECTS all

which includes all subjects who have a primary data record in at least one of the plates being extracted from the study database.

SUBJECTS 1001~1999,3001~3999,5501,6002

VISITS.  This option can be specified on the command line during SAS® job creation by using the option -v visit#_list or added to the global specifications section by editing the DFsas job file. If this option is not specified, DFsas will create DFsas job files containing the global statement VISITS all . For backwards compatibility, DFsas will assume VISITS all if the VISITS global statement is missing from a DFsas job file.

This option is similar to the existing SUBJECTS option in that both are applied during data export. Since data export occurs before data record processing, this option takes precedence over the visit specification on the RECORD statement. Thus, for example, specifying RECORDS 12 20, which indicates the creation of a data file for plate 12, visit 20, would produce no output at all if the global VISITS statement was not set to all or did not include 20. Also, if the global VISITS statement has already been used to select visit 20, the RECORDS statement would not need to include the visit specification. For example, RECORDS 12 would be adequate to produce the desired data file.

The specified visit number list is applied to all of the specified plates. It is not necessary that all visits be relevant for all plates.

	Note
	The VISITS option does not allow the specification of different visit numbers for different plates.

SUBJECTALIAS.  Specifying

SUBJECTALIAS yes

instructs DFsas to export the subject alias as the identifier in place of the traditional subject ID. It is possible to include both subject ID and subject alias by specifiying

SUBJECTALIAS yes

and also requesting field 7 in the list of fields to be exported.

CHECK.  Check fields are data fields that are entered using a single box which is either checked or left blank. An example might be a question that asks the investigator “Check all of the symptoms that apply to this subject from the following list”. Each of the symptoms will have a check box with only 2 possible values, for example, 1 if the box was checked and 0 if it was left blank.

This keyword is used to control the output for all data fields of this type. You may either print the codes (e.g. 0,1) or replace them with the labels specified in the study schema.

The 2 possibilities are specified as follows:

CHECK codes

which outputs the codes (e.g. 0, 1) for all check fields and creates a proc format statement for the value labels, and

CHECK labels

which outputs the schema labels (e.g. no for 0, yes for 1).

When using the labels option, missing value codes are preserved. If you request labels for all check fields, but have not specified any labels for a particular check field in the study schema, DFsas will use the codes.

The default for this global specification is CHECK codes, meaning that the numeric codes found in the database will be output to the SAS® data file if this statement does not appear in your DFsas job file.

You can override the global specification of labels or codes at the individual field level as described in Data Retrieval Specifications.

CHOICE.  Choice fields consist of a question followed by 2 or more mutually exclusive response options. Examples include: race, yes/no questions, severity mild/moderate/severe, etc. As for check fields, choice fields can be output as either codes or labels,

CHOICE codes
CHOICE labels

When codes are output to the data file, DFsas creates a proc format statement for value labels and writes it to the SAS job file.

Because the choice data type will apply to fields having many different response options it doesn't make sense to have default labels. Thus if you request labels and DFsas cannot find them in the study schema it will simply output the codes. As for check fields, DFsas will first check for missing value codes before substituting value labels for the codes stored in the database.

The default for this global specification is CHOICE codes, meaning that the numeric codes found in the database will be output to the SAS® data file if this statement does not appear in your DFsas job file.

You can over-ride the global specification of labels or codes at the individual field level as described in Data Retrieval Specifications.

IMPUTE.  Dates which use 2 digit years can be converted to 4 digit years and missing day and/or month values can be imputed with this statement, such that

IMPUTE no

disables imputation and outputs dates as they appear in the database and

IMPUTE yes

enables imputation and imputes the day and month in partial dates and convert 2 digit years to 4 digits.

Imputation of day and month, and conversion of 2 digit years to 4 digit years is performed using the imputation method and pivot year set in the study schema.

If a date variables has its imputation method set to never, imputation by DFsas will only result in conversion of 2 digit years to 4 digit years.

If a date is nonsensical, (i.e. does not yield a true date even after application of the imputation method, if any) imputation will output the DFdiscover default missing value code, *.

If a date field is blank or contains a missing value code, as defined in the study missing values map, imputation has no effect, i.e. the field will be output as is, with a blank or missing value code.

IMPUTE and RECODE

The use of imputation in a DFsas job file (via any of the ways that it can be done) has the potential of creating missing value codes and thus the RECODE statement should be used to change the DFdiscover * to either the SAS® . or some other user-defined missing value code that SAS® will accept. If the user has defined a missing map which only contains legal SAS® missing value codes, then the RECODE statement could still be used for dates. For example, RECODE date *

NUMBER.  Numeric fields may be defined with labels, just like choice and check fields. In such cases you have the option of outputting the numeric values, as in

NUMBER codes

or, the labels defined for the values, as in

NUMBER labels

STRING.  It is rare to put value labels on string fields but DFdiscover does allow it. For example single letter entries in a string field may be used as codes for longer descriptions that are defined in value labels. In such cases you have the option of outputting the string values,

STRING codes

or the labels, as in:

STRING labels

If string splitting is used in conjunction with a request for labels and the resulting split string values are coded with labels, the labels will be output.

22:12x1c prob labels

RETAIN QUOTES.  This statement determines how SAS input statements are written for string fields. If

RETAIN QUOTES no

is specified (which is also the default setting), string field names are followed by a $ sign, and SAS will remove any quotation marks found in these fields when the input data files are read. If

RETAIN QUOTES yes

is specified, string field names are followed by ~$, which instructs SAS to retain quotation marks.

VLABEL.  Specifying

VLABEL yes

instructs DFsas to include variable labels in the SAS® job file. Variable labels are copied from the field descriptions included in the study schema file, with the exception that any double-quote characters appearing in the field description are removed from the variable label.

VALFMT.  If VALFMT yes is specified, equivalent proc format value label statements will be written to the SAS® job file for each variable that has code labels defined in it's style or variable definition in the study schema. With value formats defined in the SAS® job file, users can elect to use the data values as in the SAS® statement proc print, or use the value labels as in the SAS® statement proc print label.

DFsas creates value formats for all variables that have code labels, whether they are defined in the style or at the variable level. If code labels are defined in the style, the style name is used as the value format name in the SAS® job file, unless the style name would be an illegal SAS® name, in which case DFsas makes up a legal SAS® name. The style name must meet the SAS® restriction that it be no longer than 8 characters, start with a letter or underscore, thereafter contain characters that are letters, digits, or an underscore, and not end with a digit. If the code labels are defined at the variable level (instead of in a style), DFsas makes up a new SAS® value format name.

When DFsas creates a value format name, it does not check to see if the variable codes and labels exactly match another value format already in use. Thus if coding and labels are not defined in the style, it is possible that DFsas may create and use more value formats than are really required. If the same code labels are used by more than one variable, it is recommended that the codes and labels be defined in a style which the variables then use.

When creating new value format names, DFsas uses F####v where #### starts at 0001 and is incremented for each new value format name that DFsas creates as it reads through the list of study styles stored in the file DFschema.stl. v is the visit number which is appended to the variable's field name. There is one exception. For DFSTATUS and DFSCREEN variables, which are DFdiscover protected fields appearing on every plate, DFsas uses value format names DFSTATv and DFSCRNv respectively. Thus these 2 names should not be used for user-defined styles with code and label definitions of their own.

For normalized records DFsas creates SAS® value formats based on the code labels defined in the study schema for the first data record defined in the block of normalized records. Typically all records in the normalized block have the same variables with the same codes (which is why they are being normalized) but DFsas does not check to make sure that this is the case.

	Value format and variable names
	SAS® requires that value format names be different from variable names. DFdiscover does not impose this restriction and currently DFsas does not check for this possible SAS® error.

BLANK.  Data fields which are blank (i.e. empty) in the database can be output as they are or can be recoded to some other value (e.g. the SAS® . missing code). Since check and choice fields contain a numeric code when no box has been selected, check and choice fields are never blank. Consequently the BLANK global specification only applies to string, date and numeric fields.

The BLANK keyword is followed by either the keyword all or by the data type from the list string, date, or int, which is to be recoded. This is then followed by either the keyword asis or by the value to be inserted for blank fields. For example,

BLANK all asis

outputs all blank fields without any recoding.

BLANK all .

recodes all blank fields to a period.

BLANK all blank

recodes all blank fields to the word blank.

BLANK string .

recodes blank string fields to a period.

BLANK int 0

recodes blank int fields to 0.

It is legal to include more than one BLANK specification, to specify different recoding instructions for string, date and int data types. Any such field type specifications will take precedence over a BLANK all specification.

RECODE.  A RECODE statement can be used to recode all data fields of a given type. Since SAS® provides extensive recode capabilities, only minimal recoding has been implemented in DFsas. DFsas only allows one RECODE statement per data type and only allows for the replacement of one data value by another.

The RECODE keyword is followed by the data type, from the list check choice string date int, which is to be recoded. This is followed by the value to be recoded and then the new value to be written to the SAS® data input files. For example:

RECODE choice 0 .

recodes 0 to a period in all choice fields, and

RECODE check 0 9

recodes 0 to 9 in all check fields

Note

If you request output labels instead of codes for check and/or choice fields, and a label has been specified for zero for some or all check and/or choice fields, then the label will appear in the data field, and the above recode specification will have no effect. The desired recode can however be obtained when outputting labels, by specifying the label that is to be recoded instead of the numeric value (see Recode Processing Order).

MISSING.  Data fields that contain a missing value code in the database can be output as they are or can be recoded to some other value. For example, while several different missing value codes may be used in a DFdiscover study, you might want to change all missing value codes to the SAS® . missing code for a particular SAS® analysis.

The MISSING keyword is followed by either the keyword all or by the data type, from the list check, choice, string, date, or int, which is to be recoded. This is followed by either the keyword asis or by the value to be used in place of all missing codes. For example:

MISSING all asis

outputs all missing codes without any recoding,

MISSING all .

recodes all missing codes for all fields to a dot,

MISSING all NA

recodes all missing codes for all fields to "NA", and

MISSING string .

recodes all missing codes in string fields to a dot.

It is legal to include more than one MISSING specification, to specify different recoding instructions for choice, check, string, date and int data types. However, if the all specification appears it takes precedence over all other specifications.

When creating new SAS® job files, a second MISSING statement is included by default. This statement MISSING lost allows you to specify the missing value code to be used in data fields for missed records. The keyword lost can be followed by a user-defined missing value code that will be inserted into each data field after field 7 (Subject ID) in each missed data record. If the global statement MISSING lost exists with no missing value code specification, all data fields following field 7 are left blank for missed records.

It is important to note that the MISSING all global statement described above will not insert missing value codes into fields of missed records. A separate MISSING lost statement must be specified in order to do this. However, if the MISSING lost statement specifies the DFdiscover missing value code (*) and a MISSING all code statement is used to recode DFdiscover missing value codes, then the MISSING all code takes precedence and this statement will apply to missed records as well.

Note

The MISSING lost global statement will have no effect on the output unless missed records are requested by the RECSTATUS global statement (See RECSTATUS). By default, the RECSTATUS statement includes final, incomplete and missed records. If missed records are requested but a MISSING lost statement has not been included, or has been included but with no missing value code specification (i.e. literally as MISSING lost), all data fields following field 7 will be left blank for missed records.

The MISSING statement can also be used to specify that a SAS® MISSING statement is to be included in the SAS® job file created by DFsas. A SAS® MISSING value statement is generated if the DFsas job file contains the following:

MISSING SAS missing_value_list_from_study_missing_value_map

For example the global statement:

MISSING SAS A B C

will generate the SAS® MISSING statement:

MISSING A B C;

to indicate that the values A, B and C represent missing values.

Note

A MISSING SAS statement is generated automatically when DFsas is executed with -c or -C, regardless of whether or not DFmissing_map contains legal SAS® missing value codes. If only legal SAS® codes have been used the MISSING SAS statement should be removed, as SAS® expects this statement only for non-standard codes. The SAS® missing value statement is written at the top of each data step rather than just once at the top of the entire SAS® job file.

% DFsas testjob -c 254 -P 5

MISSING all asis
MISSING SAS * _A _U _N

MISSING recode * _D
MISSING SAS _D _A _U _N

F.  In addition to extracting data from the DFdiscover database you might also want to include fixed fields, i.e. data fields that contain the same value for all cases. An example might be a study protocol number or a study name that you want to be able to include in data listings. Another possibility is that you might plan to merge data sets from different studies at some point and want to have the protocol number included in the data set for subjects from each study.

Fixed fields can be defined globally (in which case they are added to all data records from all plates) or they may be included in the variable list following the RECORD statement used to begin data retrieval from a specified plate (as described in Data Retrieval Specifications). Fixed fields can be defined for both normalized or non-normalized data sets.

A fixed field is defined using a statement in the following format:

F fixed_value variable_name variable_label
(1)                       (2)

F 18345 SNUM "Study Number"
F 44.171 PNUMBER "Protocol Number"
F "Study 44-171" PNAME "Protocol Name"
F 09/15/97 STARTDT "Study Start Date"
F "150mg" STDOSE "Study Treatment Dose"

Exactly the same format is used to specify fixed fields, whether defining them in the global statements section of the DFsas job, or locally in the variable list following a RECORD statement for a particular plate. The only difference is that globally defined fixed fields are inserted at the beginning of each data record immediately after the subject ID, while locally defined fixed fields are inserted in the position in which they are defined within the variable list. A DFsas job may include both globally and locally defined fixed fields.

There are 3 restrictions on fixed fields:

NOVISIT.  By specifying visit numbers after the RECORD statement, it is possible to construct data records in which the data for repeating visits are laid out on a single summary data record for each subject (e.g. ID dbp0 dbp1 dbp2 dbp3 etc.). The NOVISIT keyword is used to specify a value to be inserted for variables that don't exist in the database for a subject, because that visit has not been completed, or received. Whatever follows the NOVISIT keyword will be used, exactly as typed. Do not include quotes or the quotes will also be inserted. The only exception is the word blank, which is interpreted as requesting that the fields be left blank. A few examples follow.

NOVISIT .

Inserts the SAS® missing value code, i.e. the dot.

NOVISIT NA

Inserts the 2 letters NA.

NOVISIT blank

Leaves the fields blank.

RECSTATUS.  If the RECSTATUS statement is not used, DFsas retrieves final, incomplete and missed data records from the database. It does not retrieve pending records because this status is normally used to indicate that the data is not ready for statistical analysis, and in some cases might even indicate that one or more of the keys (subject ID, visit or plate) are uncertain. Missed records are included by default so that the data set includes all cases as is typically desired for an intention to treat analysis. The MISSING lost statement is also be included by default in new SAS® job files. This outputs blank fields for missed records. Alternatively a missing value code can be specified (See MISSING).

The RECSTATUS statement can include any combination of the following record status keywords: final, incomplete, pending and missed.

	Note
	While it is also possible to export 'secondary' records this is not generally useful, as the only meaningful data field on secondary records is the image ID.

The RECSTATUS statement applies only to data plates and not to queries. If you want to export data by query status, you must specify plate 511 when building an initial DFsas job file, as in

% DFsas job 1 -c 7 -p 511

In the data retrieval specifications for job1, specify

RECORD 511 unresolved

or

RECORD 511 resolved

to export unresolved or resolved queries respectively.

SETNAME.  DFsas names the input data files which it creates by appending d01, d02, d03, etc. to your SAS® job file name (specified using the SASJOB command). It is possible instead to have DFsas append the plate number as the extender following the SAS® job name. This is done using the SETNAME statement, as follows:

SETNAME byplate

which results in the naming of data sets by plate number (not d01, d02, etc.). The ASCII data files will be named jobname.# and the SAS® data sets will be named data# where # is the plate number.

This might be helpful if you want to have a common data set name across different SAS® jobs or different studies, in situations where the same variables are always pulled from each plate, or the same plate numbers are used in different studies.

Obviously, this option can not be used if you build more than one data set from a single plate. In the case of normalized data sets, which involve the creation of normalized records from more than 1 plate, DFsas will use the plate number from the first RECORDS statement to name the data set when SETNAME byplate is specified.

INFORMAT.  This statement controls the creation of SAS® informat statements. When a DFsas job file is created the following INFORMAT statement is automatically included:

INFORMAT dates

which instructs DFsas to create SAS® informat statements for date fields that SAS® recognizes. DFdiscover allows some date formats that the current version of SAS® does not recognize as dates, (e.g. Jan 25, 2016). Any date fields that SAS® does not allow are converted to string fields.

DFsas identifies string fields which are up to 8 characters long using the SAS® $ identifier in the data variable list, and generates informat statements for any string fields that have more than 8 characters.

DFsas converts fields which contain formatting characters to type string. Also if labels are requested for check or choice fields they too will be converted to SAS® string fields by DFsas, and if any of these labels is more than 8 characters long DFsas will automatically generate a SAS® informat statement for that field.

A global INFORMAT statement can be used to get DFsas to convert all variables to type string and generate the appropriate SAS® INFORMAT statements as follows:

INFORMAT all

This converts all fields to type string including dates. To use date formats for dates and convert all other fields to type string use the following:

INFORMAT dates all

FORMAT.  This statement is used to create SAS® output format statements for dates. The following example sets the output format for all dates to the SAS® date format DDMMYY10:

FORMAT dates DDMMYY10

If this option is used without specifying a SAS® date format, DFsas creates SAS® format statements which correspond to the format defined for each date in the study schema.

FIELDCODE.  By default, for choice and check fields, code labels are taken from the variable style and written as SAS® format statements. Generally speaking, this is the desired behavior for such fields. There may however be situations where the unique code labels for the field may be preferred. The default behavior can then be overridden with this statement as a global specification.

FIELDCODE yes

With this specification, the code labels specified at the individual field level are written as SAS® format statements.

MERGE.  In order to read all of the data files created by DFsas into a single SAS® data set it is necessary to include the appropriate SAS® commands after the data statements in the SAS® job file. Most of the time the commands that will be needed are something like the following:

data final;
merge test.d01 test.d02;
by ID;

In this example, a data set named final will be created by merging files test.d01 and test.d02 on the subject ID. The above is what is written to your SAS® job file by default, or when you explicitly ask for it by including:

MERGE yes

The summary data files are sorted on ID when they are created by DFsas and thus proc sort is not needed in SAS®.

Sometimes, for example when building normalized data sets, you may want to specify a different SAS® merge command. In such cases specify:

MERGE no

and then put the desired SAS® merge commands at the top of the SAS® procedures section of your DFsas job file.

:c - character string.  This option specifies that a fixed string is to be interpreted as a character field.

:n - number.  This option specifies that a fixed string is to be interpreted as a numeric field.

The qualification is only used from the first normalized record and is ignored if present on any other normalized records.

Qualified strings may have embedded spaces, as for pulse in the example above.

Single or double quotes may be used to specify qualified strings, but quotes of any type are not allowed within a string, and the :q qualifier is not allowed. Qualified strings must have matching quotes.

Empty strings are allowed, and may be specified with either single or double quotes, as for other in the above example.

medical problems among older men

if(item==2 && sex==1 && age>65)

untreated problems which are not resolved

if(item==2 && treat==1 && ok==1)

problems among women of any age or men over 65

if(item==2 && (sex==2 || age>65))

medical problems at visit 0 (baseline)

if(item==2 && vnum==0)

medical problems from visits 1 through 5 inclusive

if(item==2 && vnum>=1 && vnum<=5)

:n.  Sort the field in ascending numeric order, as in:

2:n

which sorts on field 2 in ascending numeric order.

:r.  Sort the field in descending alphanumeric order, as in:

2:r

which sorts on field 2 in descending alphanumeric order.

:nr.  Sort the field in descending numeric order, as in:

2:nr

which sorts on field 2 in descending numeric order.