Dear Everyone,
Can I please seek your help on how do I flag and count by id and loc when test=0 is met to get the last 2 columns - countL and countR?
Thanking you in advance.
id vis test loc countL countR
1 0 0 0
1 1 1 Left 0 0
1 1 1 Right 0 0
1 2 1 Left 0 0
1 2 0 Right 0 1
1 3 0 Left 1 0
1 3 0 Right 0 2
1 4 1 Left 0 0
1 5 1 Left 0 0
1 6 0 2 3
1 7 0 3 4
1 8 1 Left 0 0
1 8 1 Left 0 0
1 8 1 Right 0 0
1 9 0 4 5
1 9 0 Right 0 6
1 10 0 5 7
1 11 1 Left 0 0
1 12 1 Left 0 0
1 13 0 6 8
1 13 1 Right 0 0
1 14 0 7 9
1 14 1 Right 0 0
1 15 0 Left 8 0
1 15 0 Right 0 10
1 16 0 Left 9 0
2 0 0 0
2 1 1 Left 0 0
2 2 1 Left 0 0
2 3 1 Left 0 0
2 3 1 Right 0 0
2 4 0 1 1
2 4 1 Right 0 0
2 4 1 Right 0 0
2 5 0 2 2
2 6 1 Left 0 0
2 6 1 Right 0 0
2 7 1 Left 0 0
2 7 1 Right 0 0
2 8 0 Left 3 0
2 8 0 Right 0 3
2 9 1 Left 0 0
2 10 1 Left 0 0
2 11 1 Left 0 0
2 12 1 Left 0 0
2 12 1 Right 0 0
2 13 0 Left 4 4
2 13 1 Right 0 0
2 14 0 Left 5 0
2 14 0 Right 0 4
2 15 0 Left 6 0
2 16 0 Left 7 0
2 17 1 Left 0 0
2 18 0 Left 8 0
2 18 1 Right 0 0
2 19 1 Left 0 0
2 19 0 Right 0 5
2 20 1 Left 0 0
2 20 0 Right 0 6
2 21 1 Left 0 0
2 21 0 Right 0 7
2 22 0 Left 9 9
2 22 1 Right 0 0
2 23 0 10 8
2 24 0 Left 11 0
2 24 1 Right 0 0
2 25 0 Left 12 0
2 25 1 Right 0 0
It seems to me that next code result with what you want:
data have;
retain id vis test loc;
infile cards truncover dlm='09'x;
length test $5;
input id vis test loc $ ; /* countL countR */
cards;
1 0 .
1 1 1 Left
1 1 1 Right
1 2 1 Left
1 2 0 Right
1 3 0 Left
1 3 0 Right
1 4 1 Left
1 5 1 Left
1 6 0
1 7 0
1 8 1 Left
1 8 1 Left
1 8 1 Right
1 9 0
1 9 0 Right
1 10 0
1 11 1 Left
1 12 1 Left
1 13 0
1 13 1 Right
1 14 0
1 14 1 Right
1 15 0 Left
1 15 0 Right
1 16 0 Left
2 0
2 1 1 Left
2 2 1 Left
2 3 1 Left
2 3 1 Right
2 4 0
2 4 1 Right
2 4 1 Right
2 5 0
2 6 1 Left
2 6 1 Right
2 7 1 Left
2 7 1 Right
2 8 0 Left
2 8 0 Right
2 9 1 Left
2 10 1 Left
2 11 1 Left
2 12 1 Left
2 12 1 Right
2 13 0 Left
2 13 1 Right
2 14 0 Left
2 14 0 Right
2 15 0 Left
2 16 0 Left
2 17 1 Left
2 18 0 Left
2 18 1 Right
2 19 1 Left
2 19 0 Right
2 20 1 Left
2 20 0 Right
2 21 1 Left
2 21 0 Right
2 22 0 Left
2 22 1 Right
2 23 0
2 24 0 Left
2 24 1 Right
2 25 0 Left
2 25 1 Right
; run;
data want;
set have;
by id;
retain cL cR 0;
if first.id then do;
cL=0; cR=0;
end;
if test=0 then do;
if loc='Left' then cL+1; else
if loc='Right' then cR+1; else
do; cL+1; cR+1; end;
end;
if test=0 then do;
if missing(loc) then
do; countL=cL; countR=cR; end;
else do;
if loc='Left' then countL = cL; else countL=0;
if loc='Right' then countR = cR; else countR=0;
end;
end;
else do; countL=0; countR=0; end;
drop cL cR;
run;
Hi @ed_sas_member thanks for your reply.
Based on the first four columns data, I would like to add two new columns - countL and countR.
Thanks in advance.
It seems to me that next code result with what you want:
data have;
retain id vis test loc;
infile cards truncover dlm='09'x;
length test $5;
input id vis test loc $ ; /* countL countR */
cards;
1 0 .
1 1 1 Left
1 1 1 Right
1 2 1 Left
1 2 0 Right
1 3 0 Left
1 3 0 Right
1 4 1 Left
1 5 1 Left
1 6 0
1 7 0
1 8 1 Left
1 8 1 Left
1 8 1 Right
1 9 0
1 9 0 Right
1 10 0
1 11 1 Left
1 12 1 Left
1 13 0
1 13 1 Right
1 14 0
1 14 1 Right
1 15 0 Left
1 15 0 Right
1 16 0 Left
2 0
2 1 1 Left
2 2 1 Left
2 3 1 Left
2 3 1 Right
2 4 0
2 4 1 Right
2 4 1 Right
2 5 0
2 6 1 Left
2 6 1 Right
2 7 1 Left
2 7 1 Right
2 8 0 Left
2 8 0 Right
2 9 1 Left
2 10 1 Left
2 11 1 Left
2 12 1 Left
2 12 1 Right
2 13 0 Left
2 13 1 Right
2 14 0 Left
2 14 0 Right
2 15 0 Left
2 16 0 Left
2 17 1 Left
2 18 0 Left
2 18 1 Right
2 19 1 Left
2 19 0 Right
2 20 1 Left
2 20 0 Right
2 21 1 Left
2 21 0 Right
2 22 0 Left
2 22 1 Right
2 23 0
2 24 0 Left
2 24 1 Right
2 25 0 Left
2 25 1 Right
; run;
data want;
set have;
by id;
retain cL cR 0;
if first.id then do;
cL=0; cR=0;
end;
if test=0 then do;
if loc='Left' then cL+1; else
if loc='Right' then cR+1; else
do; cL+1; cR+1; end;
end;
if test=0 then do;
if missing(loc) then
do; countL=cL; countR=cR; end;
else do;
if loc='Left' then countL = cL; else countL=0;
if loc='Right' then countR = cR; else countR=0;
end;
end;
else do; countL=0; countR=0; end;
drop cL cR;
run;
data have;
infile cards truncover expandtabs ;
input id vis test loc $ ; /* countL countR */
n+1;
cards;
1 0 .
1 1 1 Left
1 1 1 Right
1 2 1 Left
1 2 0 Right
1 3 0 Left
1 3 0 Right
1 4 1 Left
1 5 1 Left
1 6 0
1 7 0
1 8 1 Left
1 8 1 Left
1 8 1 Right
1 9 0
1 9 0 Right
1 10 0
1 11 1 Left
1 12 1 Left
1 13 0
1 13 1 Right
1 14 0
1 14 1 Right
1 15 0 Left
1 15 0 Right
1 16 0 Left
2 0
2 1 1 Left
2 2 1 Left
2 3 1 Left
2 3 1 Right
2 4 0
2 4 1 Right
2 4 1 Right
2 5 0
2 6 1 Left
2 6 1 Right
2 7 1 Left
2 7 1 Right
2 8 0 Left
2 8 0 Right
2 9 1 Left
2 10 1 Left
2 11 1 Left
2 12 1 Left
2 12 1 Right
2 13 0 Left
2 13 1 Right
2 14 0 Left
2 14 0 Right
2 15 0 Left
2 16 0 Left
2 17 1 Left
2 18 0 Left
2 18 1 Right
2 19 1 Left
2 19 0 Right
2 20 1 Left
2 20 0 Right
2 21 1 Left
2 21 0 Right
2 22 0 Left
2 22 1 Right
2 23 0
2 24 0 Left
2 24 1 Right
2 25 0 Left
2 25 1 Right
; run;
data test_0 test_1;
set have;
if test=1 then output test_1;
else output test_0;
run;
data temp;
set test_0;
by id;
if first.id then do;_cl=0;_cr=0;end;
cl=0;cr=0;
if loc='Left' then do;_cl+1;cl=_cl;end;
if loc='Right' then do;_cr+1;cr=_cr;end;
drop _:;
run;
data want;
set temp test_1;
run;
proc sort data=want;by n;run;
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.