Trying to flag observations that = neg in one variable and one apart in another variable

[KPCklebspn]

dataset have:

ID result  count    

A  pos      1

A  neg      2

A  neg      3 

B  neg      1

B  neg      2

B  pos      3

dataset want:

ID result  count    flag

A  pos      1         .

A  neg      2         1

A  neg      3         1

B  neg      1         1

B  neg      2         1

B  pos      3         .

Basically, within a level, if there are 2 negs one after the other (i.e. if you subtract the count values from each other you get 1),  I want to flag both observations with a 1. How do I do this?

THank you

[novinosrin · Accepted solution]

data have;
input ID $ result $ count;
cards;
A  pos      1
A  neg      2
A  neg      3 
B  neg      1
B  neg      2
B  pos      3
;



data want;
set have;
by id result notsorted;
retain flag;
if first.result then flag=.;
if not (first.result and last.result) and result='neg' then flag=1;
run;

[KPCklebspn]

Thx so much! This works, but I'm not clear on how the 4th row of the dataset (B neg 1) got flagged with count 1. I know the 5th row got flagged as 1 because it's not the first or last result within the ID and it's also =neg. How does retain work?

[novinosrin]

retain statement prevents the new variable flag from reinitialising to missing at the top of the datastep. The check is basically only to find out/determine of if there are duplicate consecutive  neg in a set(group). If so flag as 1 and retain the value for the set. 

[KPCklebspn]

Sorry, last follow up q. If the dataset looked like below and we used your code, why wouldn't the 2nd row be flagged as 1? It's not the first AND last result, and result = neg.... so you'd think it would get flag=1 but it doesn't.  THank you!

data have;
input ID $ result $ count;
cards;
A  pos      1
A  neg      2
A  pos      3
A  neg      3
B  neg      1
B  neg      2
B  pos      3
;

[novinosrin]

That's because I had to meet your requirement -"Basically, within a level, if there are 2 negs one after the other (i.e. if you subtract the count values from each other you get 1),  I want to flag both observations with a 1. How do I do this?"

So in your new one below

data have;
input ID $ result $ count;
cards;
A  pos      1
A  neg      2/* not/(first.result and last.result) is false*/
A  pos      3
A  neg      3

---

@KPCklebspn wrote:

Sorry, last follow up q. If the dataset looked like below and we used your code, why wouldn't the 2nd row be flagged as 1? It's not the first AND last result, and result = neg.... so you'd think it would get flag=1 but it doesn't.  THank you!

data have;
input ID $ result $ count;
cards;
A  pos      1
A  neg      2
A  pos      3
A  neg      3
B  neg      1
B  neg      2
B  pos      3
;

---

[novinosrin]

Hi @KPCklebspn  I owe an apology. Actually I did some due diligence. You don't need a retain statement, however it's not that much of a deal anyway.

Just test this and let me know:

data want;
set have;
by id result notsorted;
if not (first.result and last.result) and result='neg' then flag=1;
run;

If the above works, accept my sincere apologies