Functions and Operators

Functions & Operators
  1. Functions & Operators Introduction
  2. Operators
  3. Functions
  4. Chapter 14: Functions & Operators – Full Chapter [PDF 218 KB]
  5. Chapter 14: Functions & Operators – Full Chapter [Word 86 KB]
Related Pages

Functions

Do not put a space before the first parenthesis. Functions take the value of one or more variables and return the result of a calculation or transformation.

ABS Function

Description
The ABS function returns the absolute value of a variable by removing the negative sign, if any.

Syntax
ABS <variable>

  • The <variable> can be an existing numeric variable, a defined variable containing numbers, or a numeric constant.

Comments
Results will be numeric.

Value

ABS Function
–2 2
1 1
0 0
–0.0025 0.0025

Example

READ {.ProjectsSampleSample.prj}:Oswego  
DEFINE Age2 NUMERIC 
DEFINE Age3 NUMERIC 
ASSIGN Age2 = Age * -1 
ASSIGN Age3 = ABS(Age2) 
LIST Age Age2 Age3

DAY

Description
The DAY function extracts the day from the date.

Syntax
DAY(<variable>)
The <variable> is in date format.

Comments
If the date is stored in a text variable, the function will not be processed, and will be null.

Example

READ {.\Projects\Sample\Sample.prj}:Surveillance 
DEFINE BornOnDayNumber NUMERIC 
ASSIGN BornOnDayNumber = DAY( BirthDate )
LIST BirthDate BornOnDayNumber GRIDTABLE

DAYS

Description
The DAYS function returns the number of days between <var2> and <var1>. If any of the variables or values included in the formula is not a date, the result will be null.

Syntax
DAYS (<var1>, <var2>)
The <variable> is in a date format.

Comments
If the date stored in <var1> is later (more recent) than the date in <var2>, the result is the difference in days expressed as a negative number.

Example
In this example, we know the date of the church supper was held on 4/18/1940. If we want to know how many days were between the date of the supper and the date when attendees began getting ill, we can use the DAYS function as follows:

READ {.\Projects\Sample\Sample.prj}:Oswego
DEFINE SickDays NUMERIC 
ASSIGN SickDays = DAYS(04/18/1940, DateOnset) 
LIST DateOnset SickDays

EXISTS

Description
This function returns True if a file exists. Otherwise, it returns False.

Syntax
EXISTS(<variable>)
<variable> represents the complete file path and name in text format.

Comments
If you do not have permission to access the file, a False may be returned.

Example
In this example, we want to show the Analysis Dashboard if the Ecoli canvas exists. If the dashboard canvas doesn’t exist, then we want to show a dialog to explain that it couldn’t find the canvas file. The Ecoli dashboard canvas is a file named Ecoli.cvs7 and should be located in the .ProjectsEcoli project folder that comes with Epi Info 7.

READ {.\Projects\EColi\EColi.prj}:FoodHistory
DEFINE FilePath TEXTINPUT 
ASSIGN FilePath = ".ProjectsEcoliEcoli.cvs7"
IF EXISTS( FilePath ) = (+) THEN
	EXECUTE "AnalysisDashboard.exe .ProjectsEcoliEcoli.cvs7"
ELSE
	DIALOG "Could not locate the Ecoli dashboard canvas." TITLETEXT="Canvas Not Found"
END

EXP

Description
This function raises the base of the natural logarithm (e) to the power specified.

Syntax
EXP(<variable>)

Comments
This variable can be an existing numeric variable, a defined variable containing numbers, or a numeric constant.

Example

READ {.\Projects\Sample\Sample.prj}:Oswego
DEFINE ExpA NUMERIC 
ASSIGN ExpA=EXP(Age) 
LIST ExpA Age

FILEDATE

Description
This function returns the date a file was last modified or created. If FILEDATE is specified with a file path that lacks a directory, the current directory is used. If FILEDATE is specified without a file, or with a file that does not exist, the function returns missing.

Syntax
FILEDATE(<variable>)
The <variable> represents the complete file path and the name is text format.

Comments
This function is useful when several users are updating a large database.

Example
In this example, we want to make sure we have a recent data package from a district sending data. If the date of the package is over 7 days old from the System Date, then we want a message given saying that the data package is out of date.

READ {.\Projects\CentralOffice\CentralOffice.prj}:OutbreakSurveillance
DEFINE PackageFileDate DATEFORMAT
ASSIGN PackageFileDate=FILEDATE(".PackagesDistrict_AlphaLatestOutbreak.edp7") 
IF PackageFileDate > (SYSTEMDATE - 7) THEN 
    DIALOG "The data package is within 1 week old." TITLETEXT="Recent Data Package"
ELSE
    DIALOG "The data package is out of date. Request a new data package from District Alpha." TITLETEXT="Warning: Data Package Expired"
END

FINDTEXT

Description
This function returns the position in a variable in which the search string is located.

Syntax
FINDTEXT(<variable1>,<variable2>)
The <variable1> represents the string of characters to be found.
The <variable2> represents the string to be searched.

Comments
If the string is not found, the result is 0; otherwise it is a number corresponding to the position of the string starting from the left. The first character is 1. If the result is 0, the text was not found.

Example

READ {.\Projects\Sample\Sample.prj}:Surveillance  
DEFINE PositionM NUMERIC 
ASSIGN PositionM = FINDTEXT("M",LASTNAME) 
LIST LASTNAME PositionM

FORMAT

Description
This function changes the format of one variable type to text in a specified format. If no format is specified it returns text and converts a number to text.

Syntax
FORMAT(<variable>,[“Format Specification”])
The <variable> represents a variable in any format and the [“Format Specification”] can represent any of the following:

Format Specification

Description
Date Formats Description
General Date 11/11/1999 05:34
Long Date System’s long date format
Medium Date System’s medium date format
Short Date System’s short date format
Long Time System’s long time format
Medium Time System’s medium time format
Short Time System’s short time format
Number Formats Description
General Number No thousand separator
Currency Thousand separator plus two decimal places (based on system settings)
Fixed At least #.##
Standard #,###.##
Percent Number multiplied by 100 plus a percent sign
Scientific Standard scientific notation
Yes/No Displays NO if number = 0, else displays Yes
True/False False if number = 0
On/Off True if number <> 0
Displays 0 if number = 0, else displays 1
Custom Format Allows for the creation of customized formats

Comments

Output may vary based on the specific configuration settings of the local computer. 
Format(Time, "Long Time") 
MyStr = Format(Date,"Long Date") 
MyStr = Format(MyTime,"h:m:s")  Returns "17:4:23" 
MyStr = Format(MyTime,"hh:mm:ssAMPM")  Returns "05:04:23 PM" 
MyStr = Format(MyDate,"dddd, mmm yyyy")  Returns "Wednesday, ' Jan 27 1993".

If format is not supplied, a string is returned.

MyStr = Format(23)  Returns "23".  
User-defined formats 
MyStr = Format(5459.4, "##,##0.00")  Returns "5,459.40" 
MyStr = Format(334.9, "###0.00")  Returns "334.90" 
MyStr = Format(5, "0.00%")  Returns "500.00%" 
MyStr = Format("HELLO", "<")  Returns "hello" 
MyStr = Format("This is it", ">")  Returns "THIS IS IT" 
MyStr = Format("This is it", ">;*")  Returns "THIS IS IT"

Example

READ '.ProjectsRefugee.MDB':Patient 
DEFINE var2 NUMERIC 
DEFINE var3 NUMERIC 
DEFINE var4 NUMERIC 
DEFINE var5 NUMERIC 
DEFINE var6 NUMERIC 
DEFINE var7 YN 
DEFINE var8 Boolean 
DEFINE var9 
DEFINE var10 
var2=FORMAT(BOH, "Currency") 
var3=FORMAT(BOH, "fixed") 
var4=FORMAT(BOH, "Standard") 
var5=FORMAT(BOH, "Percent") 
var6=FORMAT(BOH, "Scientific") 
var7=FORMAT(BOH, "Yes/No") 
var8=FORMAT(BOH, "True/false") 
var9=FORMAT(BOH, "On/Off") 
var10=FORMAT(BOH, "VBs #,###.##") 
LIST dob var2 var3 var4 var5 var6 var7 var8 var9 var10

HOUR

Description
This function returns a numeric value that corresponds to the hour recorded in a date/time or time variable.

Syntax
HOUR(<variable>)
The <variable> represents a variable in date format.

Comments
If the time is stored in a text variable, the function will not be processed, and the result will be null.

Example

READ {.\Projects\Sample\Sample.prj}:Oswego 
DEFINE LocalTime DATEFORMAT 
ASSIGN LocalTime = SYSTEMTIME 
LIST LocalTime 
DEFINE HourPart NUMERIC 
ASSIGN HourPart = HOUR(LocalTime) 
LIST LocalTime HourPart

HOURS

Description
This function returns the number of hours between <var1> and <var2> in numeric format.

Syntax
HOURS(<var1>, <var2>)
<var1> and <var2> represent variables in time or date/time format.

Comments
If the time stored in <var1> is later (more recent) than the time in <var2>, the result will be the difference in hours expressed as a negative number. Both variables must contain data in date, time, or date/time format. If any of the variables or values included in the formula is not a date, the result will be null.

Example

READ {.\Projects\Sample\Sample.prj}:Oswego 
DEFINE NumberOfHours NUMERIC 
ASSIGN NumberOfHours = HOURS(Timesupper, Dateonset) 
LIST Timesupper NumberOfHours Dateonset

LN

Description
The function LN returns the natural logarithm (logarithm in base e) of a numeric value or variable. If the value is zero or null, it returns a null value.

Syntax
LN(<variable>)
The <variable> can be an existing numeric variable, a defined variable containing numbers, or a numeric constant.

Example

READ {.\Projects\Sample\Sample.prj}:Oswego 
DEFINE NatLogOfAge NUMERIC 
ASSIGN NatLogOfAge = LN(AGE) 
LIST Age NatLogOfAge

LOG

Description
This function returns the base 10 logarithm (decimal logarithm) of a numeric value or variable. If the value is 0 or null it returns a null value.

Syntax
LOG(<variable>)
The <variable> can be an existing numeric variable, a defined variable containing numbers, or a numeric constant.

Comments
The result is numeric.

Example

READ {.\Projects\Sample\Sample.prj}:Oswego 
DEFINE DecLog NUMERIC 
ASSIGN DecLog = LOG(Age) 
LIST Age DecLog

MINUTES

Description
This function returns the number of minutes between <var1> and <var2> in numeric format.

Syntax
MINUTES(<var1>, <var2>)
<var1> and <var2> represent variables in time or date/time format.

Comments
If the time stored in <var1> is later (more recent) than the time in <var2>, the result will be the difference in minutes expressed as a negative number. Both variables must contain data in date, time, or date/time format. If any of the variables or values included in the formula is not a date, the result will be null.

Example

READ {.\Projects\Sample\Sample.prj}:Oswego 
DEFINE IncubationMinutes NUMERIC 
ASSIGN IncubationMinutes = MINUTES(TimeSupper, DateOnset)
LIST TimeSupper DateOnset IncubationMinutes

MONTH

Description
This function extracts the month from the date.

Syntax
MONTH(<variable>)
The <variable> represents a variable in date format.

Comments
If the date is stored in a text variable, the function will not be processed, and the result will be null.

Example

READ {.\Projects\Lyme\Lyme.prj}:CaseReport 
DEFINE OnsetMonth NUMERIC 
ASSIGN OnsetMonth = MONTH(OnsetDate) 
FREQ OnsetMonth

MONTHS

Description
This function returns the number of months between <var1> and <var2>. If any of the variables or values included in the formula is not a date, the result will be null.

Syntax
MONTHS(<var1>, <var2>)
<var1> and <var2> represent variables in date format.

Comments
If the date stored in <var1> is later (more recent) than the date in <var2>, the result will be the difference in months expressed as a negative number.

Example

READ {.\Projects\Sample\Sample.prj}:Surveillance 
DEFINE AgeMonths NUMERIC 
ASSIGN AgeMonths = MONTHS(BirthDate, 01/01/2000) 
LIST BirthDate AgeMonths

NUMTODATE

Description
This function transforms three numbers into a date format.

Syntax
NUMTODATE(<year>, <month>, <day>)

  • <year> represents a numeric variable or a number representing the year.
  • <month> represents a numeric variable or a number representing the month.
  • <day> represents a numeric variable or a number representing the day.

Comments
If the date resulting from the conversion is not valid (e.g., December 41, 2000), the date is recalculated to the corresponding valid value (e.g., January 10, 2001). When ranges between 0 and 29, it is represented as the respective year between 2000 and 2029. Values from 30 to 99 are represented as the respective year between 1930 and 1999. The earliest date that can be recorded is Jan 01, 100.

Day

Month

Year

Date Created
02 02 1999 02/02/1999
60 01 1999 03/01/1999
15 18 2000 03/18/2001
99 99 99 06/07/0107
20 74 74 08/20/1974

Example

READ {.ProjectsSampleSample.prj}:Surveillance 
DEFINE BirthDayNum NUMERIC 
DEFINE BirthMonth NUMERIC 
DEFINE BirthYear NUMERIC 
ASSIGN BirthDayNum = DAY(BirthDate) 
ASSIGN BirthMonth = MONTH(BirthDate) 
ASSIGN BirthYear = YEAR(BirthDate) 
DEFINE ReassembledDate DATEFORMAT 
ASSIGN ReassembledDate = NUMTODATE(BirthYear, BirthMonth, BirthDayNum) 
LIST BirthDate BirthYear, BirthMonth, BirthDayNum ReassembledDate GRIDTABLE

NUMTOTIME

Description
This function transforms three numbers into a time or date/time format.

Syntax
NUMTOTIME(<hour>, <minute>, <second>)

  • <hour> represents a numeric constant or variable representing hours.
  • <minute> represents a numeric constant or variable representing minutes.
  • <second> represents a numeric constant or variable representing seconds.

Comments
Time must be entered in 24-hour format. Invalid dates will be recalculated to the respective valid time. If the number of the hour exceeds 24, the resulting variable will have a date/time format and the default day 1 will be December 31, 1899.

Hour

Minute

Second

Time Created
00 00 00 12:00:00 AM
00 00 90 12:01:30 AM
15 18 2000 03/18/2001
99 99 99 06/07/0107
20 74 74 08/20/1974

Example

READ {.ProjectsSampleSample.prj}:Surveillance 
DEFINE CurrentTime DATEFORMAT 
ASSIGN CurrentTime = SYSTEMTIME 

DEFINE Hour1 NUMERIC 
DEFINE Minute1 NUMERIC 
DEFINE Second1 NUMERIC 

ASSIGN CurrentHour = HOUR(CurrentTime) 
ASSIGN CurrentMinute = MINUTE(CurrentTime) 
ASSIGN CurrentSecond = SECOND(CurrentTime) 

DEFINE ReassembledTime DATEFORMAT 
ASSIGN ReassembledTime = NUMTOTIME(CurrentHour, CurrentMinute, CurrentSecond) 
LIST CurrentTime CurrentHour CurrentMinute CurrentSecond ReassembledTime

RECORDCOUNT

Description
This function returns the number of records in the current form. In Analysis, this takes into account any SELECT statement and value of the SET process setting.

Syntax
RECORDCOUNT

Example

READ {.\Projects\Sample\Sample.prj}:Oswego 
IF RECORDCOUNT=0 THEN 
   DIALOG "No records found." 
END

RND

Description
This function generates a random number between <var1> and <var2>.

Syntax
RND(<min>, <max>)

  • The <min> represents a number or numeric variable that corresponds to the lowest value of the random number to be generated.
  • The <max> represents a number or numeric variable that is one higher than the highest possible value for the random number to be generated.

Comments
The random number generated is from <min> up to but not including <max>. For a set of random numbers consisting of only 0 and 1, the syntax RND(0, 2) would be used to generate a random number from 0 up to but not including 2.  If the value for <min> is greater than the value for <max> a syntax error results.

Example

READ {.ProjectsSampleSample.prj}:Oswego 
DEFINE Random1 NUMERIC 
DEFINE Random2 NUMERIC 
DEFINE Random3 NUMERIC 
ASSIGN Random1=RND(1,100) 
ASSIGN Random2=RND(1,100) 
ASSIGN Random3=RND(1,100) 
LIST Random1 Random2 Random3

ROUND

Description
This function rounds the number stored in the variable to the closest integer. Positive numbers are rounded up to the next higher integer if the fractional part is greater than or equal to 0.5. Negative numbers are rounded down to the next lower integer if the fractional part is greater than or equal to 0.5.

Syntax
ROUND(<variable>)
The <variable> can be an existing numeric variable, a defined variable containing numbers, or a numeric constant.

Comments
The results are returned in numeric format.

Differences Between TRUNC and ROUND

Value

TRUNC

ROUND
0.123456 0 0
7.99999999 7 8
45.545 45 46

Example

READ {.ProjectsSampleSample.prj}:Oswego 
FREQ AGE 
DEFINE Decade NUMERIC 
ASSIGN Decade = ROUND(AGE/10)+1 
LIST AGE Decade

SECONDS

Description
This function returns the number of seconds between <var1> and <var2> in numeric format.

Syntax
SECONDS(<var1>, <var2>)
<var1> and <var2> represent variables in time or date/time format.

Comments
If the time stored in <var1> is later (more recent) than the time in <var2>, the result will be the difference in seconds expressed as a negative number. Both variables must contain data in date, time, or date/time format. If any of the variables or values included in the formula is not a date, the result will be null.

Example

READ {.\Projects\Sample\Sample.prj}:Oswego 
DEFINE IncubationSeconds NUMERIC 
ASSIGN IncubationSeconds = SECONDS(TimeSupper, DateOnset) 
LIST Timesupper DateOnset IncubationSeconds

SIN, COS, TAN

Description
These functions return the respective trigonometric value for the specified variable.

Syntax
SIN(<variable>)
The <variable> can be an existing numeric variable, a defined variable containing numbers, or a numeric constant.

Comments
The variable is interpreted as the angle in radians. To convert degrees to radians, multiply by pi (3.1415926535897932) divided by 180.

Example

READ {.\Projects\Sample\Sample.prj}:Oswego
DEFINE SinA NUMERIC 
DEFINE SinB NUMERIC 
DEFINE CosA NUMERIC 
DEFINE TanA NUMERIC 
ASSIGN SinA=SIN(AGE) 
ASSIGN SinB=SIN(AGE)*3.14/180 
ASSIGN CosA=COS(AGE) 
ASSIGN TanA=TAN(AGE) 
LIST SinA CosA TanA SinB

SUBSTRING

Description
This function returns a string that is a specified part of the value in the string parameter.

Syntax
SUBSTRING(<variable>, [First], [Length])

  • The <variable> represents a variable in text format.
  • The [First] represents the position of the first character to extract from the file.
  • The [Length] represents the number of characters to extract.

Comments
This function cannot be used with non-string variables.

Example 1
This example uses SUBSTRING on a Full Name to extract only the Last Name. This example assumes the name is known, the position of the space between the first and last names is known, and the length of the last name is known. 

READ {.\Projects\Sample\Sample.prj}:Oswego 
DEFINE FullName TEXTINPUT 
ASSIGN FullName = "James Smith" 
DEFINE LastName TEXTINPUT 
ASSIGN LastName = SUBSTRING(FullName,7,5) 
LIST FullName LastName

Example 2
This example uses SUBSTRING on a Full Name to extract only the Last Name. This example uses FINDTEXT to determine the position of the space between the first and last names. It then uses that position value, along with STRLEN to determine the length of the last name portion of the string.

READ {.ProjectSampleSample.prj}:Oswego 
DEFINE FullName TEXTINPUT 
ASSIGN FullName = "James Smith" 
DEFINE BlankPosition NUMERIC
ASSIGN BlankPosition = FINDTEXT(" ", FullName) 

DEFINE LastNameLength NUMERIC
ASSIGN LastNameLength = STRLEN(FullName) - BlankPosition + 1

DEFINE LastName TEXTINPUT 
ASSIGN LastName = SUBSTRING(FullName,BlankPosition,LastNameLength)
LIST FullName BlankPosition LastNameLength LastName

Example 3
This example combines the separate functions used in Example 2 (above) into a single statement using only the variable FullName and a single space as the delimeter between first and last names.

READ {.ProjectSampleSample.prj}:Oswego 
DEFINE FullName TEXTINPUT 
ASSIGN FullName = "James Smith" 
DEFINE LastNameSimplified TEXTINPUT
ASSIGN LastNameSimplified = SUBSTRING(FullName, FINDTEXT(" ", FullName), STRLEN(FullName)-FINDTEXT(" ", FullName)+1 ) 

LIST FullName LastNameSimplified

SYSBARCODE

This function returns the value of a barcode scanned with a data collection device’s default barcode scanner app.

Syntax

SYSBARCODE

Comments
SYSBARCODE is a Check Code function applicable to forms developed for data collection using the Mobile Companion for Android.  In order to use this function, the device must have a camera, as with many cell phones and tablet computers, and the device must have an app for scanning barcodes.

Example

This example uses a form that comes with the Epi Info Mobile Companion for Android app as an example when the app is downloaded. See the form named “Sample_Barcode”. This form has a Text field named “patient_barcode” and a command button named “ScanPatientBarcode”.
When clicked, the Check Code calls the SYSBARCODE function which triggers the devices default barcode scanning app to open. When a barcode is scanned successfully, the app assigns the value to the “patient_barcode” field. The Check Code for the command button is shown here:

Field ScanPatientBarcode
	Click
		ASSIGN patient_barcode = SYSBARCODE
	End-Click
End-Field

SYSALTITUDE

This function returns the current altitude of the data collection device.

Syntax

SYSALTITUDE

Comments
SYSALTITUDE is a Check Code function applicable to forms developed for data collection using the Mobile Companion for Android.  In order to use this function, the device must have location services available, as with many cell phones and tablet computers, and the location services must be enabled.

Example

This example uses a form with a command button and three number fields.  In this example, the command button is named “GetInterviewCoordinates” and the three number fields are named “InterviewLatitude”, “InterviewLongitude”, and “InterviewAltitude”.  When the button is clicked, the Check Code assigns the device’s latitude, longitude, and altitude to the three number fields.   

Field GetInterviewCoordinates
	Click
		ASSIGN InterviewLatitude = SYSLATITUDE
		ASSIGN InterviewLongitude = SYSLONGITUDE
		ASSIGN InterviewAltitude = SYSALTITUDE
	End-Click
End-Field

SYSLATITUDE

This function returns the current latitude of the data collection device’s location.

Syntax

SYSLATITUDE

Comments
SYSLATITUDE is a Check Code function applicable to forms developed for data collection using the Mobile Companion for Android.  In order to use this function, the device must have location services available, as with many cell phones and tablet computers, and the location services must be enabled.

Example

This example uses a form with a command button and three number fields.  In this example, the command button is named “GetInterviewCoordinates” and the three number fields are named “InterviewLatitude”, “InterviewLongitude”, and “InterviewAltitude”.  When the button is clicked, the Check Code assigns the device’s latitude, longitude, and altitude to the three number fields.   

Field GetInterviewCoordinates
	Click
		ASSIGN InterviewLatitude = SYSLATITUDE
		ASSIGN InterviewLongitude = SYSLONGITUDE
		ASSIGN InterviewAltitude = SYSALTITUDE
	End-Click
End-Field

SYSLONGITUDE

This function returns the current longitude of the data collection device.

Syntax

SYSLONGITUDE

Comments
SYSLONGITUDE is a Check Code function applicable to forms developed for data collection using the Mobile Companion for Android.  In order to use this function, the device must have location services available, as with many cell phones and tablet computers, and the location services must be enabled.

Example

This example uses a form with a command button and three number fields.  In this example, the command button is named “GetInterviewCoordinates” and the three number fields are named “InterviewLatitude”, “InterviewLongitude”, and “InterviewAltitude”.  When the button is clicked, the Check Code assigns the device’s latitude, longitude, and altitude to the three number fields.   

Field GetInterviewCoordinates
	Click
		ASSIGN InterviewLatitude = SYSLATITUDE
		ASSIGN InterviewLongitude = SYSLONGITUDE
		ASSIGN InterviewAltitude = SYSALTITUDE
	End-Click
End-Field

SYSTEMDATE

Description
This function returns the date stored in the computer’s clock.

Syntax
SYSTEMDATE

Comments
The SYSTEMDATE cannot be changed (assigned) from Classic Analysis. To use the SYSTEMDATE for computations, a new variable must be defined.

Example
To calculate next week’s date:

READ {.\Projects\Sample\Sample.prj}:Surveillance 
DEFINE TodaysDate DATEFORMAT 
ASSIGN TodaysDate = SYSTEMDATE + 7 
LIST TodaysDate

SYSTEMTIME

Description
This function returns the time stored in the computer’s clock at the time the command is executed.

Syntax
SYSTEMTIME

Comments
The SYSTEMTIME cannot be changed from Classic Analysis (assigned). To use the system time for computations, a new variable must be defined.

Example
To calculate a time two hours after the current time:

READ {.ProjectsSampleSample.prj}:Surveillance 
DEFINE TimeNow DATEFORMAT 
DEFINE LaterTime DATEFORMAT 
ASSIGN TimeNow = SYSTEMTIME 
ASSIGN LaterTime = SYSTEMTIME + (120) 
LIST TimeNow LaterTime

TRUNC

Description
This function removes decimals from a numeric variable and returns the integer part of the number. This follows the same logic as rounding toward zero.

Syntax
TRUNC(<variable>)
The <variable> can be an existing numeric variable, a defined variable containing numbers, or a numeric constant.

Comments
The result will be returned in numeric format.

Example

READ {.\Projects\Sample\Sample.prj}:ADDFull 
DEFINE ADDSCTruncated Numeric 
ASSIGN ADDSCTruncated = TRUNC(ADDSC) 
LIST ADDSC ADDSCTruncated

TXTTODATE

Description
This function returns a date value that corresponds to the string.

Syntax
TXTTODATE(<variable>)
The <variable> represents a variable in text format.

Comments
The text variable can be in any format that can be recognized as a date (e.g., “Jan 1, 2000”, “1/1/2000”).

Example

READ {.ProjectsSampleSample.prj}:Surveillance 
DEFINE DateEntered TEXTINPUT 
DEFINE DateConverted DATEFORMAT 
DEFINE ComparedToNow TEXTINPUT 
DIALOG "Please enter a date. Date can be in any format that can be recognized as a date. Try entering 1/15/2000 or 25 June 2025 or another date of your choice." DateEntered  TEXTINPUT TITLETEXT="Enter a Date"
ASSIGN DateConverted = TXTTODATE(DateEntered)
IF DateConverted  >= SYSTEMDATE THEN
	ASSIGN ComparedToNow = "DateEntered is Now or Later"
ELSE
	ASSIGN ComparedToNow = "DateEntered is History"
END
LIST DateEntered DateConverted ComparedToNow

TXTTONUM

Description
This function returns a numeric value that corresponds to the string.

Syntax
TXTTONUM(<variable>)
The <variable> represents a variable in text format.

Example

READ {.ProjectsSampleSample.prj}:Surveillance
DEFINE TxtVar TEXTINPUT 
DEFINE NumVar NUMERIC 
DEFINE SumOfVars NUMERIC
DEFINE AppendText TEXTINPUT

ASSIGN TxtVar = "12345" 
ASSIGN NumVar = 67890

ASSIGN SumOfVars = TXTTONUM(TxtVar) + NumVar
ASSIGN AppendText = TxtVar & NumVar & " is a string of numeric characters!"
LIST TxtVar NumVar SumOfVars AppendText 
DISPLAY DBVARIABLES LIST AppendText NumVar SumOfVars TxtVar

UPPERCASE

Description
This function returns a string (text) variable that has been converted to uppercase.

Syntax
UPPERCASE(<variable>)
The <variable> represents a variable in text format.

Comments
Only lowercase letters are converted to uppercase; all uppercase letters and non-letter characters remain unchanged.

Example

READ {.\Projects\Sample\Sample.prj}:Surveillance 
DEFINE LastNameUpper TEXTINPUT 
ASSIGN LastNameUpper = UPPERCASE(LASTNAME) 
LIST LASTNAME LastNameUpper

YEAR

Description
This function extracts the year from a date.

Syntax
YEAR(<variable>)
The <variable> represents a variable in date format.

Comments
The date argument is any expression that can represent a date. If the date variable contains null, null is returned.

Example

READ {.\Projects\Sample\Sample.prj}:Surveillance 
DEFINE CurrentYear NUMERIC
DEFINE BirthYear NUMERIC
DEFINE BornYearsAgo NUMERIC
ASSIGN CurrentYear =YEAR(SYSTEMDATE)
ASSIGN BirthYear =YEAR(BirthDate)
ASSIGN BornYearsAgo = CurrentYear - BirthYear
LIST CurrentYear BirthYear BornYearsAgo

YEARS

Description
This function returns the number of years from <var1> to <var2> in numeric format. If any of the variables or values included in the formula is not a date, the result will be null.

Syntax

 YEARS(<var1>, <var2>)  <var1> and <var2> are represented in date format.

Comments
If the date stored in <var1> is later (more recent) than the date in <var2>, the result will be the difference in years expressed as a negative number.

Example

READ {.\Projects\Sample\Sample.prj}:Surveillance 
DEFINE AgeYears NUMERIC 
ASSIGN AgeYears =YEARS(BirthDate, EventDate) 
MEANS AgeYears 
LIST BirthDate EventDate AgeYears

Page last reviewed: September 16, 2022, 12:00 pm
Content source: Office of Public Health Data, Surveillance, and Technology