cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
owiuser
Level I

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 Community Manager

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

View solution in original post

5 REPLIES 5
rw9
rw9
Level I

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
Level III

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 Community Manager

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
Level I

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

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