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
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;
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;
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.
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
;
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
;
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
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!
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.