Subscribe Bookmark RSS Feed

Substring, Munger, or... formula?

owiuser

Community Trekker

Joined:

Sep 23, 2011

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
Solution

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

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

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?

Solution

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

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