BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KPCklebspn
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

6 REPLIES 6
novinosrin
Tourmaline | Level 20

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
Obsidian | Level 7
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
Tourmaline | Level 20

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
Obsidian | Level 7

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
Tourmaline | Level 20

 

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
Tourmaline | Level 20

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 994 views
  • 2 likes
  • 2 in conversation