Functions

functions can be used to perform operations on data, for example, to change strings or to compute other data for mapping date/datetime variables you often need to convert into iso 8601 format the function “convert date to iso 8601” is used for this purpose this section details the list of functions available with the dataset mapper plugin for each listed function there is a description and the expected parameters for each function boolean and true/false functions may be case sensitive function structure whenever users are able to specify a function mapping in any context the structure of the function will always be the same the structure is as follows function name this is a dropdown list of all the possible functions in the system refer to the function list section for detail on each individual function parameter the parameter element provides the arguments for each function the function selected will determine which parameter elements should exist by default there will only be one parameter element users need to manually add more parameters if required below is an example of how this works refer to the function list section for the expected mapping structure for the parameters and the possible number of parameters for each function some functions may also support regex operations as the sources are datasets, there are no null values only blank values if a blank value is passed as a parameter, it is treated as a blank value for example, running the concatenate function on a source variable a="first", b=null, c="last" will result in a return of “firstlast” issues for blank returns in function calls are reported in the conversion log parameters follow the following typical structure source type in this context, the source type is restricted to none source string function copy mapping choice valuelist name valuelist label valuelist data value valuelist origin valuelist original unit valuelist variable sequence no number of rows sequence no number of rows per subject sequence no unscheduled visit populate idvarval comment data value source see variable properties > mapping for more details controlled terminology lookup see variable properties > mapping for more details function see variable properties > mapping for more details copy mapping see variable properties > mapping for more details choice see variable properties > mapping for more details the parameter element is able to contain nested elements this means that for a function, the first parameter can be a function on another set of parameters function list below are all the functions in the system and the expected parameters for each one convert date to iso 8601 purpose converts a date string or a numeric sas date into an iso 8601 date string, e g 1977 01 31 for sas dates leave the date format blank unknown date components should appear in the date as "un", "unk", two hyphens or spaces (" ", " "), three hyphens or spaces (" ", " ") expected number of parameters 2 parameter detail the first parameter should be a mapping structure which returns the date string the user wishes to convert to an iso 8601 format the second parameter should be a mapping structure that returns a specified format for the date string example in the example below a source column vsdtc is selected to be converted into iso format a second parameter specifies the correct format parameter 1 31 jan 1977 parameter 2 dd mmm yyyy return 1977 01 31 convert time to iso 8601 purpose converts a time string or a numeric sas time into an iso 8601 time string, e g 13 22 for sas times leave the time format blank expected number of parameters 2 parameter detail the first parameter should be a mapping structure which returns the time string the user wishes to convert to an iso 8601 format the second parameter should be a mapping structure that returns a specified format for the time string example in the example below a source column vstim is selected to be converted into iso format a second parameter specifies the correct format parameter 1 53100 parameter 2 hh\ mm return 14 45 convert datetime to iso 8601 purpose converts a datetime string or a numeric sas datetime into an iso 8601 datetime string, e g 1977 01 31t13 22 for sas datetimes leave the datetime format blank expected number of parameters 2 parameter detail the first parameter should be a mapping structure which returns the datetime string the user wishes to convert to an iso 8601 format the second parameter should be a mapping structure that returns a specified format for the datetime string example in the example below a source column vsdtc is selected to be converted into iso format a second parameter specifies the correct format parameter 1 31 jan 1977 12 00 parameter 2 dd mmm yyyy hh\ mm return 1977 01 31t12 00 convert dmy to iso 8601 date purpose converts a date string into an iso 8601 date string expected number of parameters 2 or 3 parameter detail parameter 1 users should return a mapping structure which is a string that will represent the day component parameter 2 users should return a mapping structure which is a string that will represent the month component parameter 3 users should return a mapping structure which is a string that will represent the year component example in the example below the first parameter returns the value of source column vsdat dd the second parameter returns the value of source column vsdat mm the third parameter returns the value of source column vsdat yy parameter 1 21 parameter 2 jun parameter 3 2005 return 2005 06 21 convert hms to iso 8601 time purpose converts a time string into an iso 8601 time string expected number of parameters 3 parameter detail parameter 1 users should return a mapping structure which is a string that will represent the hour component parameter 2 users should return a mapping structure which is a string that will represent the minute component parameter 3 users should return a mapping structure which is a string that will represent the second component example in the example below the first parameter uses a nested function to select the substring before the “ ” in the vstim variable the second parameter uses a nested function to select the substring after the “ ” in the vstim variable the function will ignore the missing third parameter parameter 1 12 parameter 2 13 return 12 13 convert dmyhms to iso 8601 datetime purpose converts a datetime string into an iso 8601 datetime string expected number of parameters 6 parameter detail parameter 1 users should return a mapping structure which is a string that will represent the day component parameter 2 users should return a mapping structure which is a string that will represent the month component parameter 3 users should return a mapping structure which is a string that will represent the year component parameter 4 users should return a mapping structure which is a string that will represent the hour component parameter 5 users should return a mapping structure which is a string that will represent the minute component parameter 6 users should return a mapping structure which is a string that will represent the second component example in the example below the first parameter returns the value of source column vsdat dd the second parameter returns the value of source column vsdat mm the third parameter returns the value of source column vsdat yy the fourth parameter uses a nested function to select the substring before the “ ” in the vstim variable the fifth parameter uses a nested function to select the substring after the “ ” in the vstim variable the function will ignore the missing sixth parameter parameter 1 21 parameter 2 jun parameter 3 2005 parameter 4 12 parameter 5 09 return 2005 06 21t12 09 convert iso 8601 date to sas purpose converts an iso 8601 date string into sas date format expected number of parameters 1 parameter detail the parameter mapping structure should return the iso 8601 date string the user wishes to convert to sas date format example in the example below parameter 1 returns the values of the source variable vsdat which is an iso 8601 format parameter 1 2000 03 17 return 14686 convert iso 8601 time to sas purpose converts an iso 8601 time string into sas time format expected number of parameters 1 parameter detail the parameter mapping structure should return the iso 8601 time string the user wishes to convert to sas time format example in the example below parameter 1 returns the values of the source variable vstim which is an iso 8601 format parameter 1 14 45 return 53100 convert iso 8601 datetime to sas purpose converts an iso 8601 datetime string into sas datetime format expected number of parameters 1 parameter detail the parameter mapping structure should return the iso 8601datetime string the user wishes to convert to sas datetime format example in the example below parameter 1 returns the values of the source variable vsdtc which is an iso 8601 format parameter 1 1977 01 31t10 41 return 539174460 compute duration purpose computes a duration string from two iso 8601 dates / times / datetimes, defaulting to iso 8601 standard format expected number of parameters 2 or 3 parameter detail the first parameter mapping structure should return an iso 8601 date/time/datetime string that will be treated as start date/time/datetime the second parameter mapping structure should return an iso 8601 date/ time / datetime string that will be treated as the end date/time/datetime note both source variables must be in the same format, for example, date (2021 10 17) or datetime (2021 10 17t07 44) if they are not in the same format then you must use another function to change the formatting the third parameter is an optional parameter which will be a string to denote the format for the duration to be displayed example in the example below parameter 1 uses a nested function to convert aestdtc into an iso 8601 format the same is done in parameter 2 for aeendtc parameter 1 1977 01 01 parameter 2 1978 02 02 return p1y1m1d compute age purpose computes age based on two iso 8601 dates defaults to years expected number of parameters 2 or 3 parameter detail the first parameter mapping structure should return an iso 8601 date/time/datetime string that will be treated as birth date/time/datetime the second parameter mapping structure should return an iso 8601 date/ time / datetime string that will be treated as the current date/time/datetime note both source variables must be in the same format, for example, date (2021 10 17) or datetime (2021 10 17t07 44) if they are not in the same format then you must use another function to change the formatting the third parameter is an optional parameter which will be a string the units for the output (years, months, weeks, days, hours, minutes, seconds default to "years") this value for this parameter is case insensitive example in the example below parameter 1 is the source variable containing the subject’s date of birth the second parameter is the date of collection the return is the subject’s age in years by default as no third parameter is set if a third parameter was set with the value of months the return would be “465” parameter 1 1977 01 31 parameter 2 2015 10 31 return 38 compute dy purpose gets the study day of the second date compared to the first date expected number of parameters 2 parameter detail the first parameter mapping structure should return an iso 8601 date this will be the start date note for this the first date is considered day 1 the day before the first date is considered day 1 the second parameter mapping structure should return an iso 8601 date this will be the end date note both source variables must be in the same format, for example, date (2021 10 17) or datetime (2021 10 17t07 44) if they are not in the same format then you must use another function to change the formatting note as the functions requires to know the date in terms of which day of the year it is, if partial dates are given the full date will be imputed for partial dates including months, for example “2018 02”, the date imputed by the converter will be “2018 02 01” so it can correctly convert to which day of the year it is (32 in this case) for partial dates including only the year, for example “2018”, the date imputed by the converter will be “2018 01 01” so it can correctly convert to which day of the year it is (1 in this case) example in the example below parameter 1 is the reference start date for the subject parameter 2 is the date time of collection as it is prior to the reference start date the return is negative parameter 1 2012 05 29 parameter 2 2012 05 15t10 41 return 14 length purpose returns the character count for a string expected number of parameters 1 parameter detail the parameter mapping structure should return the string the user wishes to calculate the string length of example in the example below the length of exdose column is tested parameter 1 14 5 return 4 uppercase purpose converts a string to uppercase expected number of parameters 1 parameter detail the parameter mapping structure should return the string the user wishes to uppercase example in the example below the aeterm column is uppercased parameter 1 headache return headache lowercase purpose converts a string to lowercase expected number of parameters 1 parameter detail the parameter mapping structure should return the string the user wishes to lowercase example in the example below the dsterm column is lowercased parameter 1 informed consent return informed consent trim purpose removes leading and trailing spaces expected number of parameters 1 parameter detail the parameter mapping structure should return the string the user wishes to trim example in the example below the coval column is trimmed parameter 1 test string return test string concatenate purpose joins multiple strings expected number of parameters unlimited parameter detail each parameter mapping structure should return the string that the user wishes to be included in the concatenation example in the example below parameter 1 will return the value of the project column value parameter 2 will return a “ “character parameter 3 will return the subject column value parameter 1 abc parameter 2 parameter 3 1001 return abc 1001 contains "true" or "false" parameters are case sensitive purpose returns 'true' if the test string is contained within the string expected number of parameters 2 parameter detail the first parameter should be a mapping structure that will return the string to that will be the source for the string check the second parameter should be a mapping structure that will return the string the user wishes to see contained inside parameter 1 this is case sensitive this parameter does not support regex example in the example below parameter 1 will return the value of the project column value parameter 2 specifies the string of “gsk” to be searched for in parameter 1 parameter 1 gsk1001 parameter 2 gsk return true starts with "true" or "false" parameters are case sensitive purpose returns 'true' if the string starts with the test string expected number of parameters 2 parameter detail the first parameter should be a mapping structure that will return the string which will be the source for the string check the second parameter should be a mapping structure that will return the string the user wishes to see at the start of parameter 1 this is case sensitive this parameter does not support regex example in the example below parameter 1 will return the value of the subject column value parameter 2 specifies the string of “200” to be searched as the start of parameter 1 parameter 1 20010 parameter 2 200 return true ends with "true" or "false" parameters are case sensitive purpose returns 'true' if the string ends with the test string expected number of parameters 2 parameter detail the first parameter should be a mapping structure that will return the string which will be the source for the string check the second parameter should be a mapping structure that will return the string the user wishes to see at the end of parameter 1 this is case sensitive this parameter does not support regex example in the example below parameter 1 will return the value of the subject column value parameter 2 specifies the string of “001” to be searched at the end of parameter 1 parameter 1 20010 parameter 2 001 return false substring before purpose returns the string before the test string expected number of parameters 2 parameter detail the first parameter should be a mapping structure that will return the string which will be the source for the string check the second parameter should be a mapping structure that will return the marker in parameter 1 and everything before that marker will be returned this is case sensitive when there are multiple markers it will return everything before the first instance of the marker this parameter does not support regex example in the example below parameter 1 will return the value of the subject column value parameter 2 specifies the string of “ ” to be used as a marker for which to search everything before the marker parameter 1 1001 32 parameter 2 return 1001 substring after purpose returns the string after the test string expected number of parameters 2 parameter detail the first parameter should be a mapping structure that will return the string which will be the source for the string check the second parameter should be a mapping structure that will return the marker in parameter 1 and everything after that marker will be returned this is case sensitive when there are multiple markers it will return everything after the first instance of the marker this parameter does not support regex example in the example below parameter 1 will return the value of the subject column value parameter 2 specifies the string of “ ” to be used as a marker for which to search everything after the marker parameter 1 1001 32 parameter 2 return 32 substring purpose returns a string of the specified length starting at the specified position expected number of parameters 3 parameter detail the first parameter should be a mapping structure that will return the string which will be the source for the string check the second parameter should be an integer to denote the starting position in the string from parameter one the third parameter should be an integer to denote the length for which the substring should occur example in the example below parameter 1 will return the value of the subject column value parameter 2 specifies the string of “3” which is used as the marker point in the string parameter 3 specifies number of characters after the marker to return parameter 1 1001 32 parameter 2 3 parameter 3 2 return 01 split purpose splits string expected number of parameters 3 parameter detail the first parameter should be a mapping structure that will return the string which will be the source for the string check the second parameter should be a mapping structure which returns a regular expression the third parameter should be an integer to denote at what position the first parameter should be split based on the second parameter example in the example below parameter 1 will return the value of the vspos column value parameter 2 specifies the string of “,” which is used as the delimiter to split the string parameter 3 specifies the index of the split to be returned no trimming is done as part of the function so leading or trailing spaces would be retained parameter 1 upper, right, arm parameter 2 , parameter 3 2 return right replace purpose find and replace in string expected number of parameters 3 parameter detail the first parameter must be a mapping structure that will return the string which will be the source for the replacement operation the second parameter must match part of the source in parameter 1 the third parameter replaces the string in parameter 1 based on parameter 2 trailing spaces are retained example in the example below, if parameter 1 is the source variable's value "nausea?" parameter 2 specifies the string of “!” as the character to be replaced parameter 3 specifies the string “” to replace parameter 2 with no trimming is done as part of the function so leading or trailing spaces would be retained parameter 1 nausea? parameter 2 ? parameter 3 ! return nausea! example 2 patientid before patientid after 38516 38517 38518 38519 516 517 518 519 example 3 in this example, a regular expression (regex) is used to match line breaks or carriage returns in the source data these are replaced with an empty string parameter 1 specifies the source variable which contains the data with carriage returns parameter 2 \r \n matches zero or more carriage return characters (\r) zero or more newline characters (\n) is a quantifier that matches 0 or more occurrences of the preceding element parameter 3 is an empty string, which means the line breaks are removed translate purpose translates specific characters expected number of parameters 3 parameter detail the first parameter should be a mapping structure that will return the string which will be the source for the string check the second parameter should be a mapping that returns a string to translate from the third parameter should be a mapping structure that will return the string match you wish translate to example in the example below parameter 1 will return the value of the ceterm column value parameter 2 specifies the string of “ ” which is used as the character to be replaced parameter 3 specifies the string “” to replace parameter 2 with parameter 1 nausea1231 parameter 2 12 parameter 3 return nausea3 example2 patientid before patientid after 38516 38517 38518 38519 16 17 1 19 clean purpose removes all non alphanumeric characters expected number of parameters 1 parameter detail the first parameter should be a mapping structure that will return the string which should have all non alphanumeric characters removed this is strictly the upper and lowercase a z letters and 0 9 example in the example below parameter 1 is the value of a mapped variable parameter 1 pneumonia return pneumonia format purpose generates a string in a specified format expected number of parameters unlimited parameter detail the first parameter should be a mapping structure to return the format the user wishes to be implemented on the list of strings provided in the following parameter the format can be any regular expression once the format is specified in parameter 1 users can implement unlimited parameters after this for which the format in parameter will be applied too example in the example below parameter 1 specifies a regular expression to uppercase characters parameter 2 reads the value of the source column of coval parameter 1 %b parameter 2 this is a comment return this is a comment wordwrap purpose splits text strings at word boundaries expected number of parameters 3 parameter detail the first parameter should be a mapping structure that will return the string which will be the source for the string check the second parameter should be an integer to denote length of text to be taken from parameter one the third parameter should be an integer to denote the index for where the split should happen in the string provided in parameter 1, based on the size from parameter 2 example the example below details the mapping for the coval2 variable parameter 1 will return the value of the coval column value parameter 2 specifies to split parameter 1 into chunks of 200 parameter then selects the second chunk chunks will not split on full words parameter 1 coval \[value of length 500] parameter 2 200 parameter 3 2 return characters in the 200 400 range with respect to word boundaries maximum purpose returns maximum for a set of numbers expected number of parameters unlimited parameter detail each parameter should be a mapping structure that returns an integer example in the example below parameter 1 will return the value of the dose1 column value parameter 2 will return dose2 and parameter 3 will return dose3 note that null results will be ignored parameter 1 10 parameter 2 23 parameter 3 15 return 23 minimum purpose returns minimum for a set of numbers expected number of parameters unlimited parameter detail each parameter should be a mapping structure that returns an integer example in the example below parameter 1 will return the value of the dose1 column value parameter 2 will return dose2 and parameter 3 will return dose3 note that null results will be ignored parameter 1 10 parameter 2 parameter 3 15 return 10 add purpose numeric addition (a+b) expected number of parameters 2 parameter detail the first parameter should be a number that will be used as part of the addition the second parameter should be an integer that will be used as part of the addition example in the example below parameter 1 will return the value of the dose1 column value parameter 2 will return dose2 parameter 1 10 parameter 2 20 return 30 subtract purpose numeric subtraction (a b) expected number of parameters 2 parameter detail numeric inputs and outputs (integer or float) must be used the first parameter is a number that is used as part of the subtraction the second parameter is a number that is used as part of the subtraction example parameter 1 10 parameter 2 20 return 10 multiply purpose multiply (axb) expected number of parameters 2 parameter detail the first parameter should be a number that will be used as part of the multiplication the second parameter should be an integer that will be used as part of the multiplication example in the example below parameter 1 will return the value of the dose1 column value parameter 2 will return dose2 parameter 1 10 parameter 2 20 return 200 divide purpose divide (a÷b) expected number of parameters 2 parameter detail the first parameter should be a number which will be the dividend the second parameter should be an integer which will be the divisor example in the example below parameter 1 will return the value of the dose1 column value parameter 2 will return dose2 parameter 1 10 parameter 2 20 return 0 5 and "true" or "false" parameters are case sensitive purpose boolean (a and b) function will return true if both a and b are equal expected number of parameters 2 parameter detail the first parameter should be a mapping structure which returns a true or false to be used as the first part of the boolean and check the second parameter should be a mapping structure which returns a true or false to be used as the second part of the boolean and check example in the example below parameter 1 will return the value of the “true” parameter 2 will return the value of “true” parameter 1 true parameter 2 true return true or purpose boolean (a or b) function will return true if a or b exist, otherwise return false expected number of parameters 2 parameter detail the first parameter should be a mapping structure which returns a true or false to be used as the first part of the boolean or check the second parameter should be a mapping structure which returns a true or false to be used as the second part of the boolean or check example in the example below parameter 1 will return the value of the “true” parameter 2 will return the value of “false” parameter 1 true parameter 2 false return true not purpose boolean not(a) expected number of parameters 1 parameter detail the first parameter should be a mapping structure which returns true or false the result of this function will be the opposite of this parameter example in the example below parameter 1 will return the value of the “true” parameter 1 true return false is numeric "true" or "false" parameters are case sensitive purpose returns 'true' if the value is a number, otherwise return ‘false’ expected number of parameters 1 parameter detail the first parameter should be a mapping structure that will return the value for the numeric check example in the example below parameter 1 will return the value of the “65 6” parameter 1 65 6 return true is integer "true" or "false" parameters are case sensitive purpose returns 'true' if the value is an integer, otherwise return ‘false’ expected number of parameters 1 parameter detail the first parameter should be a mapping structure that will return the value for the integer check example in the example below parameter 1 will return the value of the “65 6” parameter 1 65 6 return false is float "true" or "false" parameters are case sensitive purpose returns 'true' if the value has a decimal component, otherwise return ‘false’ expected number of parameters 1 parameter detail the first parameter should be a mapping structure that will return the value for the decimal check example in the example below parameter 1 will return the value of the “65 6” parameter 1 65 6 return true round purpose rounds using the specified number of decimal places defaults to "half up" rounding expected number of parameters 2 or 3 parameter detail the first parameter should be a mapping structure that will return an number to be rounded the second parameter should be an integer to denote the maximum number of decimal places the third parameter is optional to denote the type of rounding i e ceiling, floor, up, down, half up, half down, half even this is case insensitive example in the example below parameter 1 will return the value of the “1 05” parameter 2 specifies the string of “3” and parameter specifies the optional parameter of “floor” parameter 1 1 0554 parameter 2 3 parameter 3 floor return 1 055 get standard unit purpose this function returns the standard unit for a given original unit, with the specified context expected number of parameters up to 6 parameter detail the first parameter should be a mapping structure that returns a string which is the original unit for conversion this should match a coded value in a codelist with map unit conversion property set to yes the next 5 parameter are optional parameters to satisfy the when condition of the standard unit mapping example in the example below parameter 1 uses the valuelist original unit functionality to return the standard unit the example below shows how users set the unit conversion for weight collection values the conversion will convert “lb” to “kg” users must specify that on “lb” what they want to convert too and specify the formula to drive this conversion users must also specify on “kg” that this will be the converted value parameter 1 lb return kg convert to standard unit purpose performs conversions to standardized units the standard units for each original unit are specified in a controlled terminology original units can map to different standard units depending on parameters like testcd, method etc expected number of parameters up to 6 parameter detail the correct standard unit is found as per "get standard unit" the value is then converted into that unit using the specified conversion factors standard value = constant a / constant b (original value + constant c) + constant k constant a and constant b default to "1" constant c and constant k default to "0" example in the example below parameter 1 uses the valuelist data value functionality to return the value which is to be converted to the standardized format the second parameter uses the valuelist original unit functionality to return the standard unit parameter 1 180 parameter 2 lb return 81 6