| absval |
Arithmetic |
Return the absolute (unsigned) values of a field. |
absval (-3) |
ABS |
| accrint |
Financial |
Calculate accrued interest for a security that pays periodic interest. |
accrint 19980228 19980831 19980501 10 1000 2 0 |
Interest |
| across |
Miscellaneous |
Apply summary function across rows |
total across FIELD1 FIELD2 FIELD3 |
|
| all |
Relational |
Returns a 1 for all records in the file. |
all |
|
| and |
Logical |
The logical "and" returns the Boolean value 1 when both arguments are true and returns 0 otherwise. |
(LTV gt 80) and (PMICODE in ',00') |
|
| any |
Summary |
Select (any) value from each category Note: If all values are the same, this is equivalent to low, high, mode, etc. |
any PROPTYPE |
|
| avg |
Summary |
Average value of a field or expression. (Statistical mean) |
avg RATE |
AVERAGE, MEAN |
| avglife |
Financial |
Calculate average Life from a series of principal payments. |
avglife 150 90 100 200 |
|
| badcodes |
Text |
Flag 'bad' code values. Check if values in a field are invalid codes (as defined in the 'Field Codes' table) |
badcodes 'PROPTYPE' |
|
| bcomma |
Text |
Extract the first part of a field up to the first comma. |
bcomma 'Smith, John' |
|
| bdrop |
Text |
If last characters of field match the text string, replace with blanks. |
'999' bdrop '999234' |
|
| between |
Relational |
Select values within an interval, not including the bounds. Order of bounds makes no difference. |
RATE between 5 10 |
include, roo, roc, rco, rcc |
| blank |
Text |
Determine if a field is all blank. |
blank ' ' |
|
| bottom |
Summary |
Return statistics on most frequently occurring records. |
1 bottom 3 STATE CURBAL |
|
| bstrip |
Text |
Extract the last part of a field after the last occurance of particular character. Useful for extracting the last name in a 'last name last' field. |
',' bstrip NAME |
lname, lnf |
| calctable |
Miscellaneous |
Run a calculation table. Similar to a 'case' statement. |
calctable 'Calculate Dates' |
|
| cat |
Text |
Concatenate two character fields or strings together. |
'CAT' cat 'FISH' |
CONCATENATE |
| ceiling |
Arithmetic |
Round up a number to the next highest integer or round up to nearest multiple of some factor. |
ceiling RATE |
CEILING.MATH |
| codecheck |
Text |
Check if values in a field are valid codes as defined in the 'Field Codes' table. Note that you must use quotes around the field name. |
codecheck 'PROPTYPE' PROPTYPE |
badcodes |
| codemap |
Text |
Convert values to descriptions based on Field Code Descriptions in the 'Field Codes' table. Note that you must use quotes around the Field Code Key. If function will NOT map values if "Code Descriptions" are disabled under File Options |
codemap 'PROPTYPE' PROPTYPE |
badcodes, codecheck |
| combine |
Text, Miscellaneous |
The combine function concatenates one of more fields or strings together. |
combine 'CAT' 'FISH' |
CONCATENATE |
| combinedate |
Date |
Combine separate year, month, and day fields into a single date field |
combinedate ORIGYY ORIGMM ORIGDD |
|
| confidence |
Summary |
Confidence Interval for a population mean |
confidence RATE (STATE in 'VA') |
|
| constant |
Miscellaneous |
Replicate value as a field. |
constant 123 |
|
| corp |
Text |
Find "corporate sounding" names. |
corp 'The Carlisle Group, Inc.' |
|
| correl |
Summary |
Correlation Coefficient between two fields (-1 <= r <= 1) |
FICO correl LTV |
|
| count |
Summary |
Count number of records |
count LOANID |
|
| covar |
Summary |
Covariance between two fields (may be negative) |
FICO covar LTV |
|
| cpr |
Financial |
Calculate average prepayment rate for a pool of loans |
cpr ORIGBAL CURBAL RATE AGE TERM |
|
| cprgen |
Financial |
Generate constant prepayment rate over a range |
1 12 cprgen ORIGBAL FPDATE PAYOFFDATE RATE PANDI |
|
| crossvalue |
Miscellaneous |
Get value from another record |
ossvalue CROSSEDNUM LOANID ORIGBAL |
|
| cumproduct |
Sequential |
Calculate cumulative product, ignoring leading zeros. |
cumproduct .9 .8 .5 |
|
| cumrnd |
Arithmetic |
Round a field to nearest multiple of left argument so that the cumulative sums of the rounded values equals the rounded value of the cumulative sums. |
1 cumrnd .1 .4 .7 .4 .2 .1 .6 .3 |
|
| cumtotal |
Sequential |
Calculate cumulative total subject to ordering and grouping. |
cumtotal PRINC |
|
| cumulative |
Group |
Convert Summary Function to Cumulative Function' |
cumulative total CURBAL |
|
| day |
Date |
Return the day from the date. |
day 20010314 |
|
| dayfix |
Date |
Reset the days in a date to the last day of the month if too high or to the first day of the month if too low. |
dayfix MTDATE |
|
| daysdiffbusinessbond |
Date |
Subtract business days using the US Bond Market calendar. |
daysdiffbusinessbond DATE_FIELD 20210112 |
date, holiday, business |
| daysdiffbusinessfederal |
Date |
Subtract business days using the US federal Market calendar. |
daysdiffbusinessfederal DATE_FIELD 20210112 |
date, holiday, business |
| daysminusbusinessbond |
Date |
Subtract business days using the US Bond Market calendar. |
daysminusbusinessbond DATE_FIELD 5 |
date, holiday, business |
| daysminusbusinessfederal |
Date |
Subtract business days using the US Federal Holiday calendar. |
daysminusbusinessfederal DATE_FIELD 5 |
date, holiday, business |
| daysplusbusinessbond |
Date |
Add business days using the US Bond Market calendar. |
daysplusbusinessbond DATE_FIELD 5 |
date, holiday, business |
| daysplusbusinessfederal |
Date |
Add business days using the US Federal Holiday calendar. |
daysplusbusinessfederal DATE_FIELD 5 |
date, holiday, business |
| db |
Financial |
Compute declining balance depreciation on an asset. |
db 1000000 100000 6 1 7 |
|
| ddb |
Financial |
Compute double declining balance depreciation on an asset. |
ddb 2400 300 10 1 2 |
|
| ddiff |
Date |
Actual days difference between two dates. |
CLOSEDATE ddiff PTDATE |
DAYS |
| ddiff360 |
Date |
Number of days difference between two dates on 30/360 basis. Calendar basis day count calcuations according to Bond Market Association Uniform Practices/Standard Formulas (SF-44) This function does not distinguish between business days, holidays and weekends. |
FPDATE ddiff360 ORIGDATE |
|
| ddiffbus |
Date |
Business days between two dates |
20080216 ddiffbus 20080209 |
|
| ddrop |
Date |
Resets the days in a date to 01, makes the date a First of month date |
ddrop MTDATE |
|
| deb |
Text |
Delete extraneous blanks from a character field |
deb ' Paul Mansour ' |
|
| default |
Miscellaneous |
Defaults the values of the entire field if the field does not exist. |
default 'ORIGTERM' 360 |
|
| div |
Arithmetic |
Divide. If denominator is 0, result is zero (tolerant divide). Same as function 'divide' and 'zdiv'. |
RATE div 1200 |
|
| dminus |
Date |
Subtract days from a date on actual day count basis If Date1 is not a legal date, result is 0 |
PTDATE dminus 10 |
|
| dollarde |
Financial |
Converts a dollar price, expressed as a fraction, into a dollar price expressed as a decimal number. |
32 dollarde 99.03 |
|
| dollarfr |
Financial |
Converts a dollar price, expressed as a fraction, into a dollar price expressed as a decimal number. |
32 dollarde 99.03 |
|
| dplus |
Date |
Add days to a date on an actual day-count basis. If Date1 is not a legal date, result is 0 |
ASOFDATE dplus 15 |
|
| drop |
Text |
Drop characters from begining (or end) of a field |
3 drop 'ABCDE' |
RIGHT |
| dropleft |
Text |
Drop characters from begining of a field. Leading blanks will be omitted. |
3 dropleft 'ABCDE' |
left, right, replace |
| dropright |
Text |
Drop characters from end of a field. Trailing blanks will be omitted. |
3 dropright 'ABCDE' |
left, right, replace |
| dtb |
Text |
Delete trailing blanks from a character field Note: this function essentially minimizes the width of a character field. |
dtb Name |
|
| dtm |
Date |
Convert a date to a 'month' serial number for date arithmetic. The result is the number of months since time 0. |
(dtm PTDATE) minus (dtm FPDATE) |
|
| dtoq |
Date |
Convert a date field to a character field showing Quarter. |
dtoq ORIGDATE |
|
| dupes |
Miscellaneous |
Flag multiple occurances (duplicates) Note: This does not take into account any selection criteria or omitted loans. |
2 dupes LOANID |
|
| element |
Text |
Check for existence of character(s) in a text string. Returns 1 if true. |
PAYHIST element '345' |
|
| eom |
Date |
Compute end of month from a date. Also handles leap years. |
eom PTDATE |
EOMONTH |
| eq |
Relational |
Flag Equal values |
2 eq 1 2 3 |
|
| everyitem |
Summary |
Return every item from a field |
everyitem STATE |
|
| exist |
Summary |
Deterimne if a value exists within the whole field. A 1 will be returned if any value in a comma separate list is found. |
exist DEFCODE 'A1' |
|
| fdrop |
Text |
If first characters of field match the text string, replace with blanks. |
'999' fdrop '999234' |
|
| fieldexists |
Summary |
Determine if a field exists. |
fieldexists 'CURBAL' |
|
| fieldtype |
Summary |
Determine Field Type; if field doesn't exist indicate what it is. |
fieldtype 'CURBAL' |
|
| first |
Miscellaneous |
Flag, or select, first X number of loans in data base. Currently, this ignores the OMIT statements. |
first 10 |
|
| firstoccur |
Relational |
Flag first occurance of each item in a field. |
firstoccur LOANID |
|
| floor |
Arithmetic |
Round down (truncate) numeric field or round down to nearest multiple of some factor. |
floor 5.73 |
FLOOR.MATH |
| fmt |
Miscellaneous |
Convert a numeric field to text. If the field is already text, nothing is done. |
'I20' fmt LOANID |
|
| fname |
Text |
Extract the first name from a field containing first name first and last name last. |
fname BORROWER |
|
| fnb |
Text |
First non blank item from a list of fields |
fnb LOANID ALTLOANID |
|
| fnf |
Text |
Put first name first |
fnf ',' remchar 'Mansour, Steve' |
|
| fntriv |
Summary |
First non blank or non-zero item in a field |
fntriv LIEN2AMOUNT |
|
| fnz |
Arithmetic |
Return first non-zero value from list of fields or expressions |
fnz 0 3 4 |
|
| formatdate |
Date |
Format a date as a character field, with the exception of converting to serial. Serial result will be numeric. This function may used to calculate a temporary field for a data list report. |
1 formatdate MTDATE |
|
| formatdate2 |
Date |
Format dates by specification. Result is text and invalid dates return blank. |
'd' formatdate2 MTDATE |
|
| fstrip |
Text |
Extract the first part of a field up to the first occurance of a particular character. Useful for extracting the first name in a field. |
',' fstrip NAME |
fname, bstrip, lnf |
| fv |
Financial |
Returns the future value of periodic payments and a constant interest rate |
fv 9.5 180 840.86 100000 |
|
| ge |
Relational |
Test whether left argument is greater than or equal to right argument. |
RATE ge 8 |
|
| getfield |
Text |
Get a field from another CAS file. The function does not test for key or row count. |
'c:\folder\filename.cas' getfield 'LOANID' |
|
| getlimitsjumbo |
Financial |
Return jumbo limits for a zipcode. |
getlimitsjumbo ZIPCODE UNITS |
|
| grand |
Summary |
Calculate overall summary of all records. For use in frequency/tables reports. All grouping statements are ignored. |
grand 'total CURBAL' |
|
| gt |
Relational |
Test whether left argument is greater than the right argument. |
RATE gt 8 |
|
| hash |
Text |
Return a base64 hash representation of a text field. Uses MD5 by default. Note* This is a resource intensive function that may take a while on large files. |
hash STATE |
|
| high |
Summary |
Highest/maximum/greatest value in a field or expression. |
high (2 3 5 1 2) |
|
| ibmstate |
Miscellaneous |
Convert IBM State Codes ('01' '02' etc.) to standard 2-character postal codes |
ibmstate '02' |
|
| ident |
Summary |
Are all values in the field the same? |
ident STATE |
|
| if |
Logical |
Returns one value if a condition is true and another value if it is false. |
if (ORIGINATOR in 'BANKA') (ORIGDATE mplus 360) (MATURITY) |
|
| in |
Relational |
Membership in a list. Where there is only 1 item in the list 'in' is equivalent to 'eq'. The function case sensitive. |
STATE in 'NY,CT,NJ' |
EXACT |
| include |
Relational |
Select values in an interval, including the bounds. Order of bounds makes no difference. |
RATE include 5 10 |
between, roo, roc, rco, rcc |
| index |
Text |
Extract characters in a text field by position |
1 2 7 8 9 index 'ABCDEFGHIJ' |
|
| ipmt |
Financial |
Compute interest payment for a particular period |
ipmt 9.5 180 360 100000 |
|
| irrcf |
Financial |
Calculate the internal rate of return of a series of cash flows |
STATE in 'NY,CT,NJ' |
|
| isbusinessdaybond |
Date |
1 if holiday or holiday observance. |
isbusinessdaybond 20211225 |
date, holiday, business |
| isbusinessdayfederal |
Date |
1 if holiday or holiday observance. |
isbusinessdayfederal 20211225 |
date, holiday, business |
| isfha |
Financial |
Determine if a loan falls into FHA limits. The function supports 2013-2016 limits. |
isfha BALANCE ZIPCODE UNITS |
|
| isholiday |
Date |
Return name of US Federal holiday. |
isholiday 20211225 |
date, holiday, business |
| isjumbo |
Financial |
Determine if a mortgage is conforming using current or specified year limits according to Fannie Mae and Freddie Mac guidelines. Valid up to and including 2017. Data for Mariana Islands cannot be determined using zip codes. These figures are not included in this function. |
isjumbo BALANCE ZIPCODE UNITS |
|
| kurt |
Statistical |
Kurtosis of a sample distribution |
kurt RATE |
|
| largest |
Relational |
Find the largest records in each category |
largest 10 CURBAL |
|
| lastoccur |
Relational |
Flag last occurrence of each item in a field. When a field value repeats 2 or more times, flags the last occurrence. |
lastoccur LOANID |
|
| le |
Relational |
Test whether left argument is less than or equal to right argument. |
RATE le 8.5 |
|
| length |
Text |
Find Length of string up to last nonblank character |
length 'ABC' |
LEN |
| listin |
Relational |
'listin' is similar to 'in', except 'listin' expects the left argument to be a text field with multiple values separated by commas. If the the left argument contains only single values, then 'listin' is equivalent to 'in'. |
ERRORLIST listin 'E1' |
|
| ljust |
Text |
Move text to the leftmost position. Leading blanks are moved to the end of the field. |
ljust ' Steve ' |
|
| lname |
Text |
Extract the last name from a field which has first name first and last name last. Suffixes (JR, SR, II, III, IV, MD) are stripped out. |
lname BORROWER |
|
| lnf |
Text |
Put Last name first. |
lnf 'Steve Mansour' cat ',' |
|
| log |
Arithmetic |
The log user function returns the logarithm of the argument. |
log 1 plus RATE divide 1200 |
|
| lookup |
Miscellaneous |
Populate a field by looking up values in another CAS file (the 'Lookup file') or data table (the Lookup table). |
PENALTY gt (STATE lookup 'c:\subdir\file.cas' 'STATE' 'MAXPENALTY') |
|
| lookup2d |
Miscellaneous |
Populate a field by looking up values in another CAS file (the 'Lookup file'). The first field in the lookup file defines the left axis (must be text). The subsequent field names define the right axis. |
lookup 'C:\LOOKUP\BUY\DOWN.CAS' RATERANGE TERMRANGE |
|
| lookupn |
Miscellaneous |
Populate a field by looking up values in another CAS file (the 'Lookup file') based on multiple keys. Note: All field names should be in quotes. |
PENALTY gt (STATE lookup 'c:\subdir\file.cas' 'STATE' 'MAXPENALTY') |
|
| low |
Summary |
Lowest/minimum/smallest value in a field or expression. |
low (2 3 5 1 2) |
|
| lowercase |
Text |
Convert all uppercase characters in a text field to lowercase. |
lowercase NAME |
|
| lt |
Relational |
Test whether left argument is less than the right argument. |
RATE lt 8.5 |
|
| makebusinessdaybond |
Date |
Return the current or first US Bond Market business day. |
makebusinessdaybond 20211225 |
date, holiday, business |
| makebusinessdayfederal |
Date |
Return the current or first US federal business day. |
makebusinessdayfederal 20211225 |
date, holiday, business |
| makechar |
Miscellaneous |
Convert a numeric field to a text field. Supplying a left argument will first make the field numeric. This might cause some values to be zero. |
makechar LOANID |
|
| makedate |
Date |
Convert a text or numeric field to a date field using a specified-date format. Any character except '1234567890' may be used as a delimiter. If a date format is not specified, CAS will make an intelligent guess. |
makedate FIELD |
|
| makenum |
Miscellaneous |
Convert a field from text to numeric, ignoring various symbols such as $ and %. If field is already numeric, leave it alone. If field contains erroneous data, make it 0. The makenum function will always default to US currency. For European currency set the optional left argument to 1 |
makenum LOANID |
|
| makeunique |
Miscellaneous |
Make values in a field unique by appending 1,2,3...etc. |
makeunique LOANID |
|
| makezip |
Miscellaneous |
Convert a text or numeric field to a well-formatted 5 character zipcode, adding leading zeros where neccessary. |
makezip 5501 |
|
| map |
Text |
Map a field from one value to another |
OWNOCC map '1/P;2/S;3/S' |
|
| mapcount |
Text |
Count character maps within a string. A 'map' is a consecutive string of the same character. For example, The string '011122210011' contains three '1' maps, and one '2' map. |
mapcount '01112221011' '1' |
|
| max |
Arithmetic |
Choose the larger value |
FLOOR max RATE plus MARGIN |
|
| mdiff |
Date |
Months difference between two dates ignoring days. |
1 plus MTDATE mdiff FPDATE |
DATEDIF |
| median |
Summary |
The middle value when the number of records is odd; the mean of the two middle values when the number of records is even. |
median APPVAL |
|
| mersmin |
Miscellaneous |
Create MERS System Morgtage Identification Number (MIN) MERS Procedures Manual - Release 11.0 - 5/23/05 Mortage Identification Number |
'9494567-' createmin '012456789' |
|
| middle |
Miscellaneous |
Get the middle value of three fields |
middle TERM RTERM 0 |
|
| min |
Arithmetic |
Choose the smaller value |
ORIGBAL min CURBAL |
|
| minus |
Arithmetic |
Subtract one value from another |
2 minus 2 |
|
| mminus |
Date |
Subtract months from a date. If DATE1=0, result is 0 |
MTDATE mminus ORIGTERM |
|
| mode |
Summary |
Most frequently occurring value. (Statistic) If more than one, choose value which occurs first. |
mode STATE |
|
| month |
Date |
Return the month from a date |
month ORIGDATE |
|
| monthname |
Date |
Return month name. |
monthname 20210601 |
Date |
| mplus |
Date |
Add a specified number of months to a date. If Date1 is not a legal date, result is 0 |
ORIDATE mplus ORIGTERM |
|
| mtd |
Date |
Convert months since time 0 to a date. |
mtd MONTHS |
|
| ne |
Relational |
Flag unequal values |
2 ne 1 2 3 |
eq, in |
| next |
Sequential |
Get next (nth) ordered item. If ordering is not specified get from next physical record. If last item in group, result is 0 or blank. |
next LOANBAL DATE LOANID |
|
| nnz |
Arithmetic |
Get the nth non zero item. |
1 nnz VALUE1 VALUE2 VALUE3 VALUE4 |
|
| normdist |
Statistical |
Returns the normal cumulative distribution for the specified mean and standard deviation. If mean and/or standard deviation are not specified, calculates sample mean and standard deviation from the data. |
normdist FIELD |
|
| norminv |
Statistical |
Returns the inverse of thestandard normal cumulative distribution (mean = 0, standard deviation = 1) |
normsinv .975 |
|
| normsdist |
Statistical |
Returns the standard normal cumulative distribution (mean = 0, standard deviation = 1) |
normsdist 1.96 |
|
| normsinv |
Statistical |
Returns the inverse of thestandard normal cumulative distribution (mean = 0, standard deviation = 1) |
normsinv .975 |
|
| not |
Relational |
Reverse the values of a selection statement. |
not STATE in 'NY,NJ,CT' |
|
| notlistin |
Relational |
Items in field not in specified list. |
FIELD notlistin 'A,B' |
|
| nper |
Financial |
Returns the number of periods for an investment based on periodic constant payments and a constant interest rate. |
nper 9.5 841 100000 |
|
| npv |
Financial |
Present value for varying monthly cash flows |
6.25 npv 150 90 100 200 |
|
| nra |
Financial |
Compute Next Rate Adjustment Date given any rate adjustment date as a reference point, an 'As Of Date', and a rate adjustment period. |
nra FRADATE PTDATE RFREQ |
|
| nscal |
Miscellaneous |
Compute Northern/Southern California Convert 'CA' to 'CA-N' if zipcode is greater than 93600 else to 'CA-S' |
nscal STATE ZIPCODE |
|
| number |
Sequential |
Number the records from 1 to N |
number |
|
| numrecs |
Miscellaneous |
Return total number of records in database |
numrecs |
|
| nzmin |
Arithmetic |
Choose the smaller value, ignoring zero |
PRPRICE nzmin PROPVAL |
|
| nzwa |
Summary |
Weighted Average of a field or expression excluding zero. |
nzwa (2 0 4) |
wa, Weighted Average, Average |
| nzwadate |
Summary |
Calculate an 'average' date excluding zero. |
nzwadate MATDATE CURBAL |
|
| occurrences |
Miscellaneous |
Number of times a particular item occurs in a field |
occurrences '10001' |
|
| okdate |
Date |
Check for valid date. Must be between 1900/01/01 and 2099/12/31 |
okdate 20010315 |
|
| okphone |
Miscellaneous |
Test for valid phone numbers. The phone number field should be 12 characters wide in the form of: 570-963-2036. The delimiter (-) can be any character. |
okphone PHONENUMBER |
|
| okstate |
Miscellaneous |
Check for valid 2 character postal Codes. |
okstate 'PA' |
|
| or |
Logical |
The logical 'or' returns the Boolean value 1 if either or both arguments is true, and returns 0 otherwise. |
(RATE gt 7) or (ORIGRATE gt7) |
|
| partition |
Sequential |
Partitions a weighted list into approximately n equal parts |
3 partition 65 34 38 21 33 1 91 78 39 72 |
|
| payhistclean |
Financial |
Result is a paystring forced to be valid. A worst-case scenario is assumed. |
payhistclean'000012500000' |
payment history, payhist |
| payhistmax |
Financial |
Maximum numeric digit. |
payhistmax '000012500700' |
payment history, payhist |
| payhistpaymentmade |
Financial |
Result is a corresponding string of 0's and 1's indicating if payment was made in that period or not. An invalid payment strings yields an asterisk. |
payhistpaymentmade '000000000000' |
payment history, payhist |
| payhistpaymentsmade |
Financial |
Return a corresponding string indicating the number of payments made in each period. An invalid payment strings yields an asterisk. |
payhistpaymentsmade '000000000000' |
payment history, payhist |
| payhisttimes30 |
Financial |
Result is the number of times 30 days or more delinquent in the last |
payhisttimes30 '00001220000000000' |
payment history, payhist |
| payhisttimes60 |
Financial |
Result is the number of times 60 days or more delinquent in the last |
payhisttimes60 '00111123000000000' |
payment history, payhist |
| payhisttimes90 |
Financial |
Result is the number of times 90 days or more delinquent in the last |
payhisttimes60 '001111233300000000' |
payment history, payhist |
| payhistvalidate |
Financial |
Result is boolean indicating if the string is a valid payment history string or not. |
payhistvalidate '000012500000' |
payment history, payhist |
| pct |
Summary |
When used in a frequency report, this is a percentage across a row - not up and down the column (see function 'percent'). The first selection statement modifies the numerator, and the second, optional statement modifies the denominator. For a percentage based on number of records, use a character field. |
pct CURBAL (PROPTYPE in 'SF,SFD,SFA') |
|
| percent |
Group |
Calculate the percentage of total of current group. The first optional selection criteria modifies the NUMERATOR The second optional selection criteria modifies the DENOMINATOR Note: This is a 'column oriented' percentage, and as as such is additive. See the function 'pct' for a 'row oriented' percentage. |
percent CURBAL |
|
| percentc |
Group |
Calculate the percentage of total of current group. The first optional selection criteria modifies the NUMERATOR The second optional selection criteria modifies the DENOMINATOR Note: This is a 'column oriented' percentage, and as as such is additive. See the function 'pct' for a 'row oriented' percentage. |
percent CURBAL |
|
| phrasesearch |
Text |
Search for occurances of any of the prhases in the right argument. Case will be ignored. |
NAME prhasesearch 'Adam A,Gram Parsons' |
|
| plus |
Arithmetic |
Function will add two values. |
2 plus 2 |
add, subtract |
| pmt |
Financial |
Compute principal and interest payment. Replaces pandi and pandi360. |
pmt 9.5 360 100000 |
|
| pmt1 |
Financial |
Compute principal and interest payment. Replaces pandi and pandi360. |
pmt 9.5 360 100000 |
|
| power |
Arithmetic |
Raise a field to a power |
RATE power 2 |
|
| ppmt |
Financial |
Compute principal payment for a particular period |
ppmt 9.5 180 360 100000 |
|
| previous |
Sequential |
Get previous (nth) ordered item. If ordering is not specified get from previous physical record. If first item in group, result is 0 or blank. |
previous LOANBAL DATE LOANID |
|
| prevworkday |
Date |
Get previous work day (exclude weekends and holidays). |
prevworkday 20050706 |
|
| price |
Financial |
Price at Loan Level |
price 8.25 6 9.5 360 |
|
| propercase |
Text |
propercase TextField |
propercase NAME |
|
| ptile |
Summary |
Find the nth percentile of a set of data. The 25th percentile is the first quartile; The 50th percentile is the median; The 75th percentile is the third quartile. |
ptile CURBAL 50 |
|
| pv |
Financial |
Returns the present value of an investment; the total amount that a series of future payments is worth now. |
pv 9.5 360 840.86 |
|
| ramp |
Miscellaneous |
Ramp from starting point to ending point |
ramp .2 5 1 |
|
| randnum |
Relational |
Generate a random numbers. |
randnum 1 100 |
|
| random |
Relational |
Generate a random sample of records |
random 5 |
|
| randpct |
Relational |
Generate a random percentage of records |
randpct 10 |
|
| rank |
Sequential |
Order numeric items. |
rank FICO |
|
| rate |
Financial |
Returns the interest rate per period of a loan or annuity for level cashflows (last payment may differ) |
rate 360 841 100000 |
|
| ratecon |
Financial |
Convert rate to bond equivalent yield, mortgage yield or effective annual yield. |
ratecon MORTYIELD 12 2 |
|
| ratio |
Summary |
This produces the ratio of two percentages where the Numerator is the percentage: |
ratio CURBAL (RATETYPE in 'A') (DELQ gt 1) |
|
| ratiototal |
Group |
Ratio of row percentages to total percentage |
ratiototal CURBAL (LTV>80) |
|
| rcc |
Relational |
Determine if field values fall within a numeric range including the lower and upper bound. |
RATE rcc 5 6 |
roc, rco, roo, include, between |
| rco |
Relational |
Determine if field values fall within a numeric range excluding the upper bound. |
RATE rco 5 6 |
roo, rcc, include, between |
| rec |
Miscellaneous |
Apply summary function to each record in group. |
(ISSNAM rec mode) MOODID (PORT in 'D1') |
|
| recavg |
Miscellaneous |
Average for unique values of a key field. |
recavg CURBAL LOANID |
|
| recfntriv |
Miscellaneous |
Combination of rec and fntriv. Get first non blank for all the records with the same key. |
LOANID recfntriv LOANPURP |
|
| recmax |
Miscellaneous |
Get maximum value for all records with the same key. |
recmax MTDATE LOANID |
|
| recmin |
Miscellaneous |
Get mininum value for all records with the same key. |
recmin MTDATE LOANID |
|
| record |
Miscellaneous |
Select the nth Record |
record 25 |
|
| rectotal |
Miscellaneous |
Total a field for unique values of a key field. |
rectotal CURBAL LOANID |
|
| regexindex |
Text |
Locate starting position(s) of one or more search matches. |
```0 regexindex NAME '((?<!\w)WI\w+) |
RO |
| regexlength |
Text |
Return the length of each search match in for the row. |
```0 regexindex NAME '((?<!\w)WI\w+) |
RO |
| regexmatch |
Text |
Return the number of matches in for the row. |
```0 regexmatch NAME '((?<!\w)WI\w+) |
RO |
| regexreplace |
Text |
Replace search matches with replace text. This function has special case uses that are enabled by changing the ResultRule parameter. |
0 regexreplace '123XXXXXXXXRRRGGG' 'X+R' 'R' |
|
| regexsearch |
Text |
Return string(s) that match regular expression search criteria. |
```0 regexsearch NAME '((?<!\w)WI\w+) |
RO |
| remainingcount |
Group |
Records remaining not including current group |
remainingcount LOANID |
|
| remchar |
Text |
Remove blanks or specific chararacters from a field, 'squeezing' text to the left. |
'-/%' remchar FIELDNAME |
|
| replacechar |
Text |
Replace characters in text field. |
replacechar LOANID '/' '-' |
|
| replacestring |
Text |
Find and replace character string in text field. |
replacestring DELQ '111' '369' |
|
| residue |
Arithmetic |
Remainder when right argument is divided by left argument. |
3 residue 11 |
|
| rjust |
Text |
Move text to the rightmost position. Trailing blanks are moved to the beginning of the field |
rjust 'Steve ' |
|
| roc |
Relational |
Determine if field values fall within a numeric range excluding the lower bound. |
RATE roc 5 6 |
roc, rco, roo, include, between |
| rollbal |
Financial |
Roll balance forward or backward to a specified date To round to nearest penny on each iteration, (useful for tying out with a servicer) use a left argument to specify the first roll-to dates for forward and backward rolling. |
rollbal 16500000 8.420 125936.42 20040701 20040801 2 |
|
| roo |
Relational |
Determine if field values fall within a numeric range excluding both bounds. |
RATE roo 5 6 |
roc, rco, rcc, include, between |
| root |
Arithmetic |
Find root of a vlue. |
root NUMFIELD |
|
| rotate |
Text |
Rotate or reverse the characters in a text field. |
rotate 'ABCDE' |
|
| round |
Arithmetic |
Round Y to nearest multiple of X. |
round 5.2 6.5 7.8 |
|
| rounddown |
Arithmetic |
Round down (truncate) numeric field or round down to nearest multiple of some factor. |
rounddown 5.73 |
FLOOR.MATH |
| roundup |
Arithmetic |
Round up a number to the next highest integer or round up to nearest multiple of some factor. |
roundup RATE |
CEILING.MATH |
| rsq |
Summary |
R-Squared Statistic between two fields |
FICO rsq LTV |
|
| scale |
Arithmetic |
Move decimal in a numeric field. |
2 scale PANDI |
|
| sdev |
Statistical |
Sample standard deviation from the mean. |
sdev RATE |
|
| sequence |
Sequential |
Order items within a group |
sequence STATE |
|
| skew |
Statistical |
Skewness of a sample distribution |
skew RATE |
|
| sln |
Financial |
Compute Straight Line Depreciation on an asset. |
sln 30000 7500 10 |
|
| smallest |
Relational |
Find the smallest records in each category |
smallest 10 CURBAL |
|
| ss |
Text |
String search |
NAME ss 'PAUL' |
|
| startingcount |
Group |
Records remaining including current group |
startingcount LOANID |
|
| statetocode |
Text |
Convert full state name to two character state code. |
statetocode STATE |
state, fips |
| statetofips |
Text |
Convert two character state code to state fips code. |
statetofips STATE |
state, fips |
| statetoname |
Text |
Convert two character state code to full name. |
statetoname STATE |
state, fips |
| stringsort |
Text |
Sort delimited values within each row of a column. The sorting is case insensitive, |
',' stringsort FIELD 1 |
|
| substr |
Text |
Select a substring from a field. Note if length is a field, result will be padded with blanks |
substr PAYHIST 4 9 |
|
| substrpos |
Text |
Locate starting position of sub-string in a field. |
substrpos 'ABC' 'BC' |
|
| sum |
Summary |
Sum the values in a field or expression. |
sum CURBAL |
SUM, SUMIF |
| sumchars |
Text |
Count the number of occurances of particular characters in a field. |
PAYHIST sumchars '01' |
|
| sumrnd |
Arithmetic |
Round values so that the sum of the rounds equals the round of the sum. For example, this will assure that numbers will total 100%. |
sumrnd 42.35 38.45 19.20 |
|
| table |
Miscellaneous |
Look up value in a table based on row and column keys. If row or column does not exist, pad with zero or blank. If only one key specified, select from the dimension that has multiple items. |
'RATING' table 'AAA+' 'MOODY' |
|
| take |
Text |
Take characters from begining (or end) of a field |
3 take 'ABCDE' |
|
| takeleft |
Text |
Take characters from begining of a field. Leading blanks will be omitted. |
3 takeleft 'ABCDE' |
left, right |
| takeright |
Text |
Take characters from end of a field. Trailing blanks will be omitted. |
3 takeright 'ABCDE' |
right, left |
| tbilleq |
Financial |
Return Bond-Equivalent yield on a treasury bill |
tbilleq 19990331 19990601 9.14 |
|
| tbillprice |
Financial |
Compute yield on a treasury bill |
tbillyield 20061115 20070223 97.569 |
|
| tbillyield |
Financial |
Compute yield on a treasury bill |
tbillyield 20061115 20070223 97.569 |
|
| tc |
Text |
Returns backspace, newline, linefeed and horizontal tab |
tc |
|
| times |
Arithmetic |
Multiplication. If dyadic, performs multiplication If monadic, returns the sign of the right argument If argument is positive, returns 1, if zero, returns 0, and if negative returns -1. |
2 times 3 |
|
| timestamp |
Date |
Returns current date/time according to user specified format. |
timestamp 'd' |
|
| tochar |
Miscellaneous |
Convert a numeric field to a text field |
tochar LOANID |
|
| today |
Date |
Returns today's date. |
tochar LOANID |
|
| tokenmatch |
Text |
Returns a comma separated list of matching tokens from field 1 in field 2. |
',' tokenmatch SEARCHTERMS SEARCHFIELD |
|
| top |
Summary |
Return statistics on most frequently occuring records. |
1 top 3 STATE CURBAL |
|
| topx |
Summary |
Return top Nth of the most frequently occuring records. |
1 topx 3 STATE CURBAL |
|
| total |
Summary |
Sum the values in a field or expression. |
total CURBAL |
SUM, SUMIF |
| uligenerate |
Miscellaneous |
Generate a ULI with a check digit. |
uligenerate LEIANDIDFIELD |
|
| ulivalidate |
Miscellaneous |
Check if a ULI with a check digit is valid. |
validzip '18510' 'PA' |
|
| uniqpct |
Group |
Calculate percentage of total unique items in each group. |
uniqpct LESSEE |
|
| unique |
Summary |
Number of unique items in a field. |
unique NAME |
|
| uniquevals |
Summary |
List all (or some) unique values. |
uniquevals PROPTYPE |
|
| uppercase |
Text |
Convert all lower case characters to upper case. |
uppercase NAME |
|
| validzip |
Miscellaneous |
Test for zipcode and state consistancy. |
uppercase NAME |
|
| vlookup |
Miscellaneous |
Searches for a value in one column of a table, and then returns a value in the same row from another column. |
'DivScore' vlookup 1.5 'EUS' 'DIVSCORE' |
|
| wa |
Summary |
Weighted average of a field or expression |
wa RATE CURBAL |
|
| wadate |
Summary |
Calculate a Weighted Average Date. |
wadate MATDATE CURBAL |
|
| wal |
Financial |
Calculate weighted average life of a fixed-rate mortgage or bond |
wal 9.5 360 |
|
| weekday |
Date |
Return a number identifying the day of the week. |
weekday 20000331 |
|
| weekdayname |
Date |
Return day name. |
weekdayname 20210601 |
Date |
| word |
Text |
Extract a word or string from a character field. |
word ADDRESS ',' N |
|
| wordcount |
Text |
Count the number of 'words' in a text field |
wordcount ADDRESS |
|
| wordsearch |
Text |
Search for occurances of any of the words in any of the items of the right argument. Case will be ignored. |
NAME wordsearch 'Adam Smith,Gram Parsons' |
|
| xirr |
Financial |
Calculate the internal rate of return of a series of cash flows with dates |
20080101 20080301 20081030 20090215 20090401 |
|
| xwal |
Financial |
Calculate weighted average life of a fixed-rate mortgage or bond |
wal 9.5 360 |
|
| year |
Date |
Return the year from a date |
year 20010314 |
|
| yearmonth |
Date |
Return the year and month (as a six digit integer) from a date |
yearmonth ORIGDATE |
|
| yesno |
Miscellaneous |
Converts boolean field of 1's and 0's to 'Y's and 'N's. |
yesno MATCH |
|
| yield |
Financial |
Calculate at yield Loan Level |
yield 107.88 6 9.5 360 |
|
| ymd |
Date |
Extract the year month or day from a date. or combine year, month, day into single date field. |
1 ymd ORIGDATE |
|
| zdrop |
Text |
Replace leading '0's from character field Y with blanks. |
zdrop LOANID |
zfill |
| zfill |
Text |
Zero fill a character field. (Replaces all blanks with '0's) This function is usually run in conjunction with 'rjust' and 'take' in order to make loan numbers match in two separate files. |
zfill LOANID |
zdrop |
| zipdistance |
Logical |
Find zipcode (in this file) within a distance of this ZIP code. |
zipdistance ZIP 5 20 |
|
| ziplookup |
Miscellaneous |
Lookup details about a zipcode. Data obtained from www.zip-codes.com. Check function help in your application for release date. |
ziplookup (Zip Field) (Lookup Field Name) ziplookup ZIPFIELD 'POPULATION' |
|