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;
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;
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;
Great and thank you.
As I see we can also use retain without set initial value of 0.
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;
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,
Thank you so much
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;
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.