topic Re: Substring, Munger, or... formula? in Discussions

Substring, Munger, or... formula?

owiuser — Wed, 16 Apr 2014 15:15:09 GMT

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

Re: Substring, Munger, or... formula?

rw9 — Wed, 16 Apr 2014 15:20:11 GMT

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.

Re: Substring, Munger, or... formula?

Wed, 16 Apr 2014 15:29:16 GMT

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

Re: Substring, Munger, or... formula?

reeza — Wed, 16 Apr 2014 15:33:48 GMT

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.

Re: Substring, Munger, or... formula?

Jeff_Perkinson — Wed, 16 Apr 2014 15:33:51 GMT

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

Re: Substring, Munger, or... formula?

owiuser — Wed, 16 Apr 2014 15:48:55 GMT

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