Substring, Munger, or... formula?

[#1]

I’ve been wrestling with a database problem for a couple of days and I’m stumped.  I wonder if any of you might be able to help?  I’m dealing with several hundred tables that include records for wildlife sites  For each record, there is a field that includes the site identifier appended to the path in my computer and a .tif suffix, like these examples:

E:\Work\OWI\LIDAR\Owl_Landscapes\p99\p99sliced\rnd_SI_PK_UR3.tif

E:\Work\OWI\LIDAR\Owl_Landscapes\p99\p99sliced\rnd_SI_PK_20100195.tif

[Site identifier in bold italics]

I need to create a new field in each table that holds only the site identifier, minus the path and .tif suffix.  I’m somewhat familiar with substring and Munger formulas that I have used to extract parts of strings.  But in this case, some site identifiers have mixed text/numeric characters and the string length is different among different records, so I can’t get these formulas to work.  Can someone offer advice?

Dave

[#2]

Hi,

Check out perl regular expressions - base SAS = prxparse, prxchange etc.  Should be able to do this straightforward with that.

Just to add, it looks like you have a fixed start?  If so then tranwrd(substr(string,fixed_start),".tif","") should also work.

[#3]

I don't think standard SAS functions work in JMP.

Substring is what you're looking for.

The starting point is fixed, find the location of the period and substring between those. 

[#4]

Are you using JMP or Base SAS to read these files?

[#5 · Accepted solution]

Hi Dave,

Try the Word() function.

     Word( -2, :Column 1, "_." )

That formula will get you what you want. The "-2" argument says that the Word() function should find the second to last word using _ and . as delimiters.

-Jeff

[#6]

Thank you everybody! Jeff's suggestion worked perfectly.  Dave