Epi Info™ User Guide

Functions and Operators

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  {C:\My_Project_Folder\Sample\Sample.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 {C:\My_Project_Folder\Sample\Sample.prj}:Oswego 
DEFINE CurrentDay NUMERIC
ASSIGN CurrentDay = DAY(01/15/2007)
LIST CurrentDay

DAY

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

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

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

READ {C:\My_Project_Folder\Sample\Sample.prj}:Oswego 
DEFINE var1 TEXTINPUT
ASSIGN var1="C:\epi_info\epimap.exe"
IF EXISTS(Var1) =(+) then
DIALOG "Hello"
END
IF Exists("C:\Epi_Info\EpiInfo.mnu")=(+) then
DIALOG "File epiInfo.mnu exists"
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 {C:\My_Project_Folder\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

READ {C:\My_Project_Folder\Sample\Sample.prj}:RHepatitis 
DEFINE NewUpdate DATEFORMAT
ASSIGN NewUpdate=FILEDATE("C:\epi_info\Sample.mdb")
IF FILEDATE("C:\epi_info\Sample.mdb") > NewUpdate THEN
DIALOG "This information may be out of date. Please check the source." TITLETEXT="Warning" 
END
LIST NewUpdate

FINDTEXT

Description
This function returns the position in a variable in which the 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 test was not found.

Example

READ {C:\My_Project_Folder\Sample\Sample.prj}:Surveillance 
DEFINE Var11 NUMERIC
VAR11=FINDTEXT("M",LASTNAME)
LIST LASTNAME Var11

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 'C:\Epi_Info\Refugee.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, "VB\s #,###.##")
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 {C:\My_Project_Folder\Sample\Sample.prj}:Oswego
DEFINE Local DATEFORMAT
ASSIGN Local = SYSTEMTIME
LIST Local
DEFINE hour1 NUMERIC
ASSIGN hour1=hour(local)
LIST Local hour1

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 {C:\My_Project_Folder\Sample\Sample.prj}:Oswego
DEFINE hour1 NUMERIC
ASSIGN hour1=HOURS(Timesupper,Dateonset)
LIST hour1
LIST hour1 Timesupper 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 {C:\My_Project_Folder\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 results will be numeric.

Example

READ {C:\My_Project_Folder\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 {C:\My_Project_Folder\Sample\Sample.prj}:Oswego
DEFINE Min1 NUMERIC
ASSIGN Min1=MINUTES(timesupper,dateonset)
LIST Min1

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 {C:\My_Project_Folder\Sample\Sample.prj}:Oswego
DEFINE CurrMonth NUMERIC
ASSIGN CurrMonth = MONTH(01/01/2005)
LIST CurrMonth

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 {C:\My_Project_Folder\Sample\Sample.prj}:Surveillance
DEFINE AgeMonths NUMERIC
ASSIGN AgeMonths = MONTHS(BirthDate,01/01/2000)
LIST 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 {C:\My_Project_Folder\Sample\Sample.prj}:Surveillance
DEFINE day1 NUMERIC
DEFINE month1 NUMERIC
DEFINE year1 NUMERIC
ASSIGN day1= day(BirthDate)
ASSIGN month1 = month(BirthDate)
ASSIGN year1 = year(BirthDate)
define date2 DATEFORMAT
ASSIGN date2= NUMTODATE(year1,month1,day1)
LIST month1 day1 year1 date2 BirthDate 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 {C:\My_Project_Folder\Sample\Sample.prj}:Surveillance
DEFINE Var3 DATEFORMAT
ASSIGN Var3=SYSTEMTIME
DEFINE Hour1 NUMERIC
DEFINE Minute1 NUMERIC
DEFINE Second1 NUMERIC
ASSIGN Hour1=HOUR(VAR3)
ASSIGN Minute1=MINUTE(VAR3)
ASSIGN Second1=SECOND(VAR3)
DEFINE Time2 DATEFORMAT
ASSIGN Time2=NUMTOTIME(HOUR1,MINUTE1,SECOND1)
LIST Var3 Hour1 Minute1 Second1 Time2

RECORDCOUNT

Description
This function returns the number of records in the current View. In Analysis, this takes into account any SELECT statement and value of the Process (Deleted) setting.

Syntax
RECORDCOUNT

Example

READ {C:\My_Project_Folder\Sample\Sample.prj}:Oswego
IF RECORDCOUNT=0 THEN
DIALOG "No records found."
QUIT
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 {C:\My_Project_Folder\Sample\Sample.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.

Value	TRUNC	ROUND
Differences Between TRUNC and ROUND
0.123456	0	0
7.99999999	7	8
45.545	45	46

Example

READ {C:\My_Project_Folder\Sample\Sample.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 {C:\My_Project_Folder\Sample\Sample.prj}:Oswego
DEFINE Sec1 NUMERIC
ASSIGN Sec1=SECONDS(Timesupper,DateOnset)
LIST Timesupper DateOnset Sec1

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 {C:\My_Project_Folder\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

READ {C:\My_Project_Folder\Sample\Sample.prj}:Oswego
DEFINE Text1 TEXTINPUT
ASSIGN Text1 ="James Smith"
DEFINE LName TEXTINPUT
ASSIGN LName = SUBSTRING(Text1,7,5)
LIST Text1 LName

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 {C:\My_Project_Folder\Sample\Sample.prj}:Surveillance
DEFINE TodayDate DATEFORMAT
ASSIGN TodayDate =SYSTEMDATE + 7
LIST TodayDate

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 {C:\My_Project_Folder\Sample\Sample.prj}:Surveillance
DEFINE Later DATEFORMAT
ASSIGN Later =SYSTEMTIME
LIST Later
ASSIGN Later =SYSTEMTIME+(120)
LIST Later

TRUNC

Description
This function removes decimals from a numeric variable, returning 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 {C:\My_Project_Folder\Sample\Sample.prj}:ADDFull
DEFINE Trc1 Numeric
ASSIGN Trc1 = TRUNC(ADDSC)
LIST Trc1 ADDSC

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 {C:\My_Project_Folder\Sample\Sample.prj}:Surveillance
DEFINE Var1 TEXTINPUT 
ASSIGN Var1="05/20/2006"
DEFINE Var2 DATEFORMAT
ASSIGN Var2=TXTTODATE(Var1)
DISPLAY DBVARIABLES
LIST Var1 Var2

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 {C:\My_Project_Folder\Sample\Sample.prj}:Oswego
DEFINE Var1 TEXTINPUT
ASSIGN Var1="12345"
DEFINE Var2 NUMERIC
ASSIGN Var2=TXTTONUM(Var1)
LIST Var1 Var2
DISPLAY DBVARIABLES

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 {C:\My_Project_Folder\Sample\Sample.prj}:Surveillance
DEFINE LastName2 TEXTINPUT
ASSIGN LastName2 = UPPERCASE(LASTNAME)
LIST LastName2 LASTNAME

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 {C:\My_Project_Folder\Sample\Sample.prj}:Surveillance
DEFINE CurrentYear NUMERIC
ASSIGN CurrentYear =YEAR(01/01/2006)
LIST CurrentYear

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 {C:\My_Project_Folder\Sample\Sample.prj}:Surveillance
DEFINE SurveyDate DATEFORMAT
ASSIGN SurveyDate=05/15/2001
DEFINE AgeYears NUMERIC
ASSIGN AgeYears =YEARS(BirthDate,SurveyDate)
MEANS AgeYears
LIST AgeYears BirthDate SurveyDate

File Formats Help:

How do I view different file formats (PDF, DOC, PPT, MPEG) on this site?

Page last reviewed: August 18, 2016
Page last updated: August 18, 2016
Content source:

User Guide

Related Links

Epi Info™ User Guide

Functions and Operators

Functions

ABS Function

Value

ABS Function

DAY

DAY

EXISTS

EXP

FILEDATE

FINDTEXT

FORMAT

Format Specification

Description

HOUR

HOURS

LN

LOG

MINUTES

MONTH

MONTHS

NUMTODATE

Day

Month

Year

Date Created

NUMTOTIME

Hour

Minute

Second

Time Created

RECORDCOUNT

RND

ROUND

Value

TRUNC

ROUND

SECONDS

SIN, COS, TAN

SUBSTRING

SYSTEMDATE

SYSTEMTIME

TRUNC

TXTTODATE

TXTTONUM

UPPERCASE

YEAR

YEARS

User Guide

Related Links

File Formats Help: