Retain to calculate across rows: Number of times touch failure

[Ronein]

Hello

I have multiple rows for each customer.

Task1- For each customer create binary indicator that get value 1 if the customer touch grade 11 and 0 otherwise

Task2-For each customer create a new variable called "No_Failures"   that count number of months that he/she touch grade 11

I know to calculate it by proc sql.

My question is how to calculate it using RETAIN

Data raw_tbl;
input ID YYMM  grade;
cards;
1 1901 2
1 1902 2
1 1903 3
1 1904 3
1 1905 2
1 1906 4
2 1901 9
2 1902 10
2 1903 10
2 1904 11
2 1905 11
2 1906 11
3 1901 10
3 1902 11
3 1903 11
3 1904 11
3 1905 11
3 1906 11
;
run;

 PROC SQL;
	create table required_a  as
	select ID,
           sum(case when grade=11 then 1 else 0 end ) as No_Failures,
           max(case when grade=11 then 1 else 0 end ) as Ind_Touch_Failure ,
/*		   case when calculated No_Failures>=1 then 1 else 0 end as Ind_Touch_Failure*/
	from  raw_tbl
	group by ID
;
QUIT;

[PeterClemmensen · Accepted solution]

This uses retain and gives the same output

data want;
   set raw_tbl;
   by ID;
   if first.id then do;
      No_Failures=0;
      Ind_Touch_Failure=0;
   end;
   if grade=11 then do;
      No_Failures=No_Failures+1;
      Ind_Touch_Failure=1;
   end;
   if last.id;
   retain No_Failures Ind_Touch_Failure 0;
   keep id No_Failures Ind_Touch_Failure;
run;

[Ronein]

Great and thank you.

As I see  we can also use retain without set  initial value of 0.

[PeterClemmensen]

This is simpler though.

data want;
   No_Failures=0;Ind_Touch_Failure=0;
   do until (last.id);
      set raw_tbl;
      by id;
      if grade=11 then do;
         No_Failures+1;
         Ind_Touch_Failure=1;
      end;
   end;
run;

[ed_sas_member]

Hi @Ronein 

In addition to @PeterClemmensen 's answer, you don't need to specify a RETAIN statement as the statement No_Failures + 1 already makes a cumulative sum (the RETAIN is "included", assuming the first value of the counter is 0).

In case you want to initialize your counter to a different number than 0, you can use the RETAIN statement to assign this initial value. But no need in your case.

Best,

[Ronein]

Thank you so much

[Jagadishkatam]

please try

data want;
set raw_tbl;
by id yymm;
retain Ind_Touch_Failure No_Failures;
if first.id then do; Ind_Touch_Failure=.;No_Failures=.;end;
if grade=11 then Ind_Touch_Failure=1;
else Ind_Touch_Failure=0;
if grade=11 then No_Failures+1;
else No_Failures=0;
if last.id;
run;