nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Parsing help needed

wendytseng

Newbie parsing question. How should I go about getting the date and the text after "Charge" in the Description column of the Aug_7.pdf data set (screen shot below and pdf attached) used in the PDF import example in the JMP 15 Training Starter? Thank you!

Case Notes.png

Aug_7.pdf

Accepted answers

DonMcCormack

In all but the simplest cases, I'd use Regex for extraction information from free text. For example,

Regex(
	:Name( "Description (A)ttempted, (C)ompleted, (F)elony, (M)isdemeanor" ),
	"\d{1,2}/\d{1,2}/\d{4}"
)

will get the dates. And

Regex(
	:Name( "Description (A)ttempted, (C)ompleted, (F)elony, (M)isdemeanor" ),
	"(Charge|chrg) of ([^,]+)",
	"\2",
	Ignore Case
)

will give you everything after Charge or chrg up to the next comma, regardless of case.

If you've never used regular expressions the learning curve is a bit steep and the language seemingly esoteric unless you've done some UNIX programming. It is incredibly powerful for these types of situations, though. Craige Hales built a phenomenal interactive regex builder as part of Text Explorer. You can look there for more examples. My favorite tutorial is here. If you don't want to go down this path, and I can understand why, there are a few technical people who can help.

All comments

brady_brady

The date you can get by recoding with "last word" (depending on the delimiters you choose... you may have to use extract segment.)

To separate out the charge: you could use regex, but you could also do something like this:

1) Recode "on chrg of" and "on charge of" strings to a character that does not occur in the text. Say, a tilde. "~"

2) Now use text to columns with the tilde as a separator.

If you did steps 1-2 above, you could then recode the resultant column (call it col X) with "last word" to get the date in its own column, then recode col X again with extract segment to strip off the date.

Again, you could (likely should) do this with regex... but it isn't horrible to point-and-click using a couple of tricks, and it is important to at least know some point-click hacks because for most customers, regex is a nonstarter.

-Brady

DonMcCormack

In all but the simplest cases, I'd use Regex for extraction information from free text. For example,

Regex(
	:Name( "Description (A)ttempted, (C)ompleted, (F)elony, (M)isdemeanor" ),
	"\d{1,2}/\d{1,2}/\d{4}"
)

will get the dates. And

Regex(
	:Name( "Description (A)ttempted, (C)ompleted, (F)elony, (M)isdemeanor" ),
	"(Charge|chrg) of ([^,]+)",
	"\2",
	Ignore Case
)

will give you everything after Charge or chrg up to the next comma, regardless of case.

brady_brady

I agree with Don. Regex is one of those things like JSL... painful to learn but incredibly powerful.

Regex is something I always promise myself I will learn, then don't learn, then curse myself (several times a year) for not having learned.

Jason_Wiggins

If you want to get text where there are no Charge or Chrg you can throw in a ? which is a 0 or 1 quantifier. Or you may want to parse out Vicitim similar to what Don Showed.

<SPAN class="token string">"(Charge|chrg)? of ([^,]+)"</SPAN>

I use https://www.debuggex.com/ to protype my regular experssions. It allows you to paste test data and try different regex scripts on test data to see what you get. There is a pretty good cheet sheet that I find helpful.

Jeff_Perkinson

As an aside, I'm not comfortable with us using this PDF in a public demonstration. I know that the data is from public records but it has names, ages and addresses that are real and are from here in Cary. It's possible that some of them may have some connection to SAS. It feels a bit off-brand to use data like this in public.

I spoke to @brady_brady and he's going to remove it from the Training Starter. For those who have already downloaded the Training Starter I'd recommend against using it in public.

Thanks.

Chris_Kirchberg

Good point Jeff. We should use data with no names or addresses.