Choose Language Hide Translation Bar
owiuser
Community Trekker

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

0 Kudos
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
Community Trekker

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.

0 Kudos
reeza
Community Trekker

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. 

0 Kudos

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

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

0 Kudos
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

Highlighted
owiuser
Community Trekker

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

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

0 Kudos