Is there a way in SAS to flag if there are certain values after a value across columns?

[SP01]

Hi all, Here is my current data with ten columns:

co1 col2 col3 col4 col5 col6 col7 col8 col9 col10
PM  MM  JM  MM PM   PB 
PM  MM  JM  MM PM   PB   JM 
PM  MM  JM  MM PM  MB   PM  JM 
PM  MM  JM  MM PM  PM   MM  MB 

Goal:
I want to flag all records which end with either 'PB' or 'MB' value and there are no values after them across columns and flag if there are values after 'PB' or 'MB'

Any help is appreciated. Thanks!

[PaigeMiller]

---

@SP01 wrote:

I want to flag all records which end with either 'PB' or 'MB' value and there are no values after them across columns and flag if there are values after 'PB' or 'MB'

---

So if I am understanding this and also if you wrote it properly, this is equivalent to creating a flag any time there is a 'PB' or 'MB' regardless of what is after it. Is that what you mean?

[SP01]

Hi Miller, I am sorry for the confusion. I want to create a flag when there is any value other than 'PB' or 'MB' after 'PB' or 'MB' across columns.

[PaigeMiller]

So in your example data, please tell us for each row whether we should set a flag, or not.

[SP01]

Yes, we should set a flag is there are values other than 'PB' or 'MB' after 'PB' or 'MB' as flag = 1, else flag = 0

[PaigeMiller]

 please tell us for each row in your example data whether we should set a flag, or not.

[SP01]

Yes, we should.

[russt_sas]

If I understand your question you could  do something like the following:

data one;
input col1 $ col2 $ col3 $ col4 $ col5 $ col6 $ col7 $ col8 $ col9 $ col10 $;
cards;
PM MM JM MM PM PB . . JM .
PM MM JM MM PM PB JM . . .
PM MM JM MM PM MB PM JM . .
PM MM JM MM PM PM MM MB . PB
PM MM JM MM PM PM MM DB . AB
;

data new;
set one;
flag=0;
array list(*) col1-col10;
do i=1 to dim(list);
if list(i) IN ('PB','MB') then do;
do j=i to dim(list);
if list(j) ne ' ' then flag=1;
end;
end;
end;
drop i J;
run;

proc print;
run;

[SP01]

Thank you for the solution. This logic is only flagging records that end with 'PM' or 'MB'

[ballardw]

---

@SP01 wrote:

Thank you for the solution. This logic is only flagging records that end with 'PM' or 'MB'

---

Which is why @PaigeMiller asked for clarification.

Your initial requirement starts "I want to flag all records which end with either 'PB' or 'MB' value and there are no values after them" which matches the solution.

Poorly phrased if the actual requirement "is flag if any value is 'PB' or 'MB' "

[SP01]

I agree with this. Going forward, I will try to be more precise.

[s_lassen]

So you want to flag the observations where the last-non-missing column is not 'PB' og 'MB'?

I think this does it:

data want;                                                                                                                              
  set have;                                                                                                                             
  flag=scan(catx(' ',of col1-col10),-1) not in('PB','MB');                                                                              
run;

[Ksharp]

data one;
input col1 $ col2 $ col3 $ col4 $ col5 $ col6 $ col7 $ col8 $ col9 $ col10 $;
cards;
PM MM JM MM PM PB . . JM .
PM MM JM MM PM PB JM . . .
PM MM JM MM PM MB PM JM . .
PM MM JM MM PM PM MM MB . PB
PM MM JM MM PM PM MM DB . AB
;

data new;
set one;
length temp $ 2000;
temp=catx('|',of col:);
if prxmatch('/(JM|PB)$/i',strip(temp)) then flag=1;
 else flag=0;
drop temp;
run;

proc print;
run;