BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
kevsma
Quartz | Level 8

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-s...

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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;

Patrick_0-1705713880417.png

 

 

 

View solution in original post

7 REPLIES 7
ballardw
Super User

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.

 

 

kevsma
Quartz | Level 8

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. 

 

kevsma_3-1705703571533.png

 

 

 

 

Tom
Super User Tom
Super User

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

Patrick
Opal | Level 21

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;

Patrick_0-1705713880417.png

 

 

 

kevsma
Quartz | Level 8

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!

s_lassen
Meteorite | Level 14

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).

kevsma
Quartz | Level 8

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!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 620 views
  • 2 likes
  • 5 in conversation