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
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
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.
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.
Are you using JMP or Base SAS to read these files?
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
Thank you everybody! Jeff's suggestion worked perfectly. Dave