topic Re: flag and count by id when condition is met in SAS Programming

flag and count by id when condition is met

Miracle — Sat, 09 May 2020 16:37:15 GMT

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

Re: flag and count by id when condition is met

ed_sas_member — Sat, 09 May 2020 15:54:28 GMT

Hi @Miracle

Could you please specify the desired output?

Best,

Re: flag and count by id when condition is met

Miracle — Sat, 09 May 2020 16:35:02 GMT

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.

Re: flag and count by id when condition is met

Shmuel — Sat, 09 May 2020 18:33:34 GMT

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;

Re: flag and count by id when condition is met

Ksharp — Sun, 10 May 2020 11:46:06 GMT

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;

Re: flag and count by id when condition is met

Miracle — Sun, 10 May 2020 14:57:35 GMT

Thanks @Shmuel .

Hi @Ksharp, not quite but thanks.

Again thanks to you both 🤗