BookmarkSubscribeRSS Feed
owiuser

Community Trekker

Joined:

Sep 23, 2011

Substring, Munger, or... formula?

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

1 ACCEPTED SOLUTION

Accepted Solutions
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

Solution

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

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

-Jeff
5 REPLIES
rw9

Community Trekker

Joined:

Apr 5, 2013

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

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.

reeza

Community Trekker

Joined:

Jun 23, 2011

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

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?

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

Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

Solution

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

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

-Jeff
owiuser

Community Trekker

Joined:

Sep 23, 2011

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

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