topic Re: Trying to flag observations that = neg in one variable and one apart in another variable in SAS Data Management

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

KPCklebspn — Wed, 05 Dec 2018 01:10:26 GMT

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

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

novinosrin — Wed, 05 Dec 2018 01:45:15 GMT


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;

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

KPCklebspn — Wed, 05 Dec 2018 02:17:41 GMT

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?

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

novinosrin — Wed, 05 Dec 2018 02:25:40 GMT

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.

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

KPCklebspn — Wed, 05 Dec 2018 02:35:00 GMT

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
;

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

novinosrin — Wed, 05 Dec 2018 02:41:19 GMT

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
;

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

novinosrin — Wed, 05 Dec 2018 03:24:45 GMT

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