SAS proc SQL: Extract number in front of the word ‘Month’ or ‘Months’

I want to extract a number from a string where it is directly followed by the word ‘Month’ or ‘Months’.

The search should be case insensitive and the text to look in typically looks like

No interest 18 months, no promo payments due = 18 
No interest  24 months, no promo payments due = 24 
No interest  12 months, no promo payments due = 12

In db2 I use this with:

xmlcast(xmlquery('let $r := fn:replace($s, ".* (d+) *month.*", "$1", "i") return if ($r castable as xs:integer) then xs:integer($r) else ()' passing ' '|| TABLE.PLAN_DESC  as "s") as int)

How can I convert this to SAS proc sql?

Answer

A simple solution

input(scan(substr(myText, 1, find(myText, 'month', 'i') -1), -1), 8.)

In this

• find with the i modifier looks case insensitive for the string month in myText
• substr returns the part in front of it
• scan with -1 as position returns the last word before month
• input applies the format 8. to read that word as a number of up to 8 digits

Disclaimer

• This code is not tested
• It will only find one occurance of month in a string
• It will also find a number in front of monthly, for instance