topic Re: search for transition pattern in multiple columns in SAS Programming

search for transition pattern in multiple columns

kevsma — Fri, 19 Jan 2024 21:00:53 GMT

Hello, this is similar to another thread i posted before here: https://communities.sas.com/t5/SAS-Programming/How-to-check-for-an-array-of-columns-and-search-for-specific/m-p/887340#M350582

and i am sorry but i can't figure out how to flag the rows when a specific pattern occurs. Basically I want to flag any row that have a sequence of the following pattern: 0 --> D or 4 or 6 or 9 --> 0 ---> 2. Data structure is the same as in the pasted link above. There could be other values in between those sequence (which is okay) so it's not necessarily they are right next to each other.

Below is the code I've trying to make it work but it is not. Somehow it's not capturing the second time 0 appears in the rows. Any suggestions? Thanks!!

DATA TEST3;
  SET TEST2;
  ARRAY COLNAME st1-st42; 
  FLG = 0;
  FLG1 = 0;
  FLG2 = 0;

  i=whichc('0', of colname[*]);

  if i then do i = i + 1 to dim(colname) until (flg);
    flg = colname[i] IN ('D', '4', '6', '9');
  end;

  if flg then do i = i + 1 to dim(colname) until (flg1);
    flg1 = colname[i] = '0';
  end;

  if flg1 then do i = i + 1 to dim(colname) until (flg2);
    flg2 = colname[i] = '2';
  end;

  if flg and flg1 and flg2; 
  DROP FLG FLG1 FLG2 i;
run;

Re: search for transition pattern in multiple columns

ballardw — Fri, 19 Jan 2024 22:22:40 GMT

I really cannot follow what this means: have a sequence of the following pattern: 0 --> D or 4 or 6 or 9 --> 0 ---> 2.

Also your previous post in that thread did not actually provide workable data. Some of use made up something but not to work with this question.

Suggestion: Following the example set by others in that other thread create data and then walk us through examples of the various cases to show the the desired output. Include as many different cases as you can provide. Include some that are not one of your sequences. Show the results.

Re: search for transition pattern in multiple columns

kevsma — Fri, 19 Jan 2024 22:34:12 GMT

Sorry this is confusing to you.

Below i pasted a screenshot of my data table, each row represents a unique ID and I have 43 columns with values indicating the status for each time point. All I need is to flag out the rows when the specific pattern occurs: 0 to (4 or D or 6 or 9) then back to 0 and eventually to 2.

I highlighted a few examples below that would fit the criterion here but the rest don't either because no 4/6/9/D in between the 0s or no 0 before 2, etc. I hope this helps clarify a bit.

Re: search for transition pattern in multiple columns

Patrick — Sat, 20 Jan 2024 01:26:05 GMT

It would really help if you could share sample data not via screen shot but in useable form - ideally via working SAS data step code that creates it.

For row 7 in below sample data: The current logic will return a match. Is that what you want to happen? If not then can you please further specify the selection logic.

data have;
  infile datalines truncover dsd;
  input (st1-st7) ($);
datalines;
6,0,,D,0,2
0,1,0,5
0,,0,D,0,2
0,D,0,0,2
,,0,6,0,2
,0,6,6,0,2
0,6,9,D,0,6,2
;

data want;
  set have;
  flag = prxmatch('/0.*?[D46].*?0.*?2/oi',strip(cats(of st1-st7))) > 0;
run;

proc print data=want;
run;

Re: search for transition pattern in multiple columns

Tom — Sat, 20 Jan 2024 21:52:21 GMT

Why bother to make a PICTURE of data? Can't you just copy the actual TEXT?

Re: search for transition pattern in multiple columns

s_lassen — Mon, 22 Jan 2024 08:01:49 GMT

I tested your code. It seems to capture the transitions just fine in the test data that @Patrick wrote. Is it because your data contains something that is not shown in the picture? (For instance, if the second 0 contains leading blanks).

Re: search for transition pattern in multiple columns

kevsma — Tue, 23 Jan 2024 19:29:15 GMT

you are right! I just tested mine and it returns the same results as @Patrick wrote. it looks like I forgot to dedup before running the codes, that's why i was seeing those odd entries. mystery solved!

Re: search for transition pattern in multiple columns

kevsma — Tue, 23 Jan 2024 19:30:30 GMT

thanks so much @Patrick ! your code works like a charm and is much more concise than mine, I absolutely like your solution. and yes, i'll paste in text data lines next time, sorry for the inconvenience it has caused. thanks again for the help!