Hi, I would like to count for 0 by Name only if it comes out before 1 so any 0 comes out after 1 should be ignored.
Could anyone help me how to do that ? Thanks
data have;
input Name $ A;
datalines;
John 0
John 0
John 0
John 0
John 0
John 0
John 0
John 0
John 1
John 1
John 1
John 1
John 1
John 1
John 0
John 0
John 0
John 0
David 0
David 0
David 0
David 1
David 0
David 0
David 0
David 0
Smith 1
Smith 1
Smith 1
Jason 0
Jason 1
Jason 0
Jason 0
Jason 1
Jason 0
;
run;
data want;
input Name $ Cnt;
datalines;
John 8
David 3
Smith 0
Jason 1
;
run;
data have;
input Name $ A;
datalines;
John 0
John 0
John 0
John 0
John 0
John 0
John 0
John 0
John 1
John 1
John 1
John 1
John 1
John 1
John 0
John 0
John 0
John 0
David 0
David 0
David 0
David 1
David 0
David 0
David 0
David 0
Smith 1
Smith 1
Smith 1
Jason 0
Jason 1
Jason 0
Jason 0
Jason 1
Jason 0
;
run;
data want;
set have;
by name notsorted;
if first.name then
do;
cnt=0;
_f=0;
end;
cnt+a=0;
_f+a=1;
if _f=1 and a=1 then
output;
drop _f a;
run;
data have;
input Name $ A;
datalines;
John 0
John 0
John 0
John 0
John 0
John 0
John 0
John 0
John 1
John 1
John 1
John 1
John 1
John 1
John 0
John 0
John 0
John 0
David 0
David 0
David 0
David 1
David 0
David 0
David 0
David 0
Smith 1
Smith 1
Smith 1
Jason 0
Jason 1
Jason 0
Jason 0
Jason 1
Jason 0
;
run;
data want;
set have;
by name notsorted;
if first.name then
do;
cnt=0;
_f=0;
end;
cnt+a=0;
_f+a=1;
if _f=1 and a=1 then
output;
drop _f a;
run;
Thank you so much Hai Kuo
Hai Kuo,
Please educate me on how your elegant code works. I have never seen your style before. How do the first two lines below work?
cnt+a=0;
_f+a=1;
if _f=1 and a=1 then
output;
Obs Name A action cnt _f
1 John 0 delete 1 0
2 John 0 delete 2 0
3 John 0 delete 3 0
4 John 0 delete 4 0
5 John 0 delete 5 0
6 John 0 delete 6 0
7 John 0 delete 7 0
8 John 0 delete 8 0
9 John 1 output 8 1
10 John 1 delete 8 2
11 John 1 delete 8 3
12 John 1 delete 8 4
13 John 1 delete 8 5
14 John 1 delete 8 6
15 John 0 delete 9 6
16 John 0 delete 10 6
17 John 0 delete 11 6
18 John 0 delete 12 6
19 David 0 delete 1 0
20 David 0 delete 2 0
21 David 0 delete 3 0
22 David 1 output 3 1
23 David 0 delete 4 1
24 David 0 delete 5 1
25 David 0 delete 6 1
26 David 0 delete 7 1
27 Smith 1 output 0 1
28 Smith 1 delete 0 2
29 Smith 1 delete 0 3
30 Jason 0 delete 1 0
31 Jason 1 output 1 1
32 Jason 0 delete 2 1
33 Jason 0 delete 3 1
34 Jason 1 delete 3 2
35 Jason 0 delete 4 2
Apologize for my own laziness, but maybe it is good for job security?
cnt+a=0;
_f+a=1;
are equivalent to:
cnt+(a=0);
_f+(a=1);
So as you can see now, it is just a boolean expression. When a=0 is true, then (a=0)=1, else 0. So as (a=1). cnt+(a=0) is to count the how many (a=0)s, while _f+(a=1) is to count the number of (a=1)s. And when it hits the first (a=1) (defined by _f=1 and a=1), then output.
Regards,
Haikuo
Brilliant. Thank you. I did the same thing COBOL style long hand. You accomplished in 3 lines what I used 10+ lines to do.
Didn't know you are an old-timer mainframe guy.
That I am. And I am trying to learn the new languages. In the olden days of dinosaurs, you could follow code step by step and intuit the logic and goals behind the code. In the new-fangled days of cars driving themselves, code is written in shorthand, Succint, compressed and computers can interpret the "shorthand" based on position of characters, or absence of characters, in a string of values.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.