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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.