topic Selecting Based on Pattern Matching in SAS Programming

Selecting Based on Pattern Matching

BCNAV — Mon, 04 Mar 2019 20:09:15 GMT

I have a series of aviation messages that come in as text. I need to be able to use a "like" type of command.

At present I have

(case when t1.MSG_RAW_TXT contains " CYQXZZZ" then 1 else 0 end) AS GANDER_VOICE

However, there can be messages in the MSG_RAW_TXT variable that are misidentified. The ones I want ALWAYS have 6 numbers before it....for example EIAAYSYX 030800 CYQXZZZ would be a valid message to set GANDER_VOICE=1 because of the 030800 in front of CYQXZZZ. This example: EIAAYSYX CYQXZZZ would not be a valid message to set GANDER_VOICE=1.

I am not sure how this could be done in SAS. A Microsoft SQL programmer at work here suggested something along the lines of:

Like ‘[0-9][0-9][0-9] [0-9] [0-9] [0-9] CYQXZZZ’

This would look for any sequence in the MSG_RAW_TXT variable that has a 6 digit number (each number is from 0-9 as stated above), then a space and then the CYQXZZZ. In this general case we would set GANDER_VOICE=1

Thanks for any advice

Re: Selecting Based on Pattern Matching

ErikLund_Jensen — Mon, 04 Mar 2019 20:32:15 GMT

Hi @BCNAV

You can extract the wanted word with a regular expression like this:


data _null_;
	MSG_RAW_TXT = 'EIAAYSYX 030800 CYQXZZZ xyz';
	b = prxchange('s/(.*\b\d{6}) (\w+)(.*)/$2/',-1,MSG_RAW_TXT);
	put b=;
run;

It might be a better approach to extract the airport to a new variable.You could do that in a data step, and then use that in your SQL, if for some reason you want the boolean variables.

Re: Selecting Based on Pattern Matching

mkeintz — Mon, 04 Mar 2019 20:34:22 GMT

You have a sequence of space separated words in a character variable, call it TXT. You can

Set gander=0.
Find the word position W of "CYQXZZZ" in TXT (the findw function).
If W>1 you can extract word number W-1 (the scan function), call it PRIOR_WRD
If
1. PRIOR_WRD is exactly 6 characters, and
2. non of the characters is a non-digit
3. then gander=1

data have;
  input txt $50.;
  put txt=;
datalines;
EIAAYSYX 030800 CYQXZZZ
EIAAYSYX CYQXZZZ
run;

data want (drop=prior_wrd);
  set have;
  w=findw(txt,'CYQXZZZ',' ','E');
  gander=0;
  if w>1 then do;
    prior_wrd=scan(txt,w-1);
    if length(prior_wrd)=6 and notdigit(trim(prior_wrd))=0 then gander=1;
  end;
run;

Re: Selecting Based on Pattern Matching

PGStats — Mon, 04 Mar 2019 20:35:17 GMT

Try

prxmatch("/\b\d{6}\sCYQXZZZ/o", t1.MSG_RAW_TXT) > 0 as GANDER_VOICE

Note: the "o" suffix in the pattern tells SAS that the pattern is a constant that should be compiled only once.