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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.