Using count function with CASE

[Solly7]

Hi I need help in counting the distinct policy_no, where saved=1 as well as counting number of all distinct policies..see below data HAVE and WANT

data HAVE;
input Saved policy_no user : $20.;
datalines;
1 1 ALUPIYA
1 1 ALUPIYA
0 2 ALUPIYA
1 3 ALUPIYA
0 5 DLOUIS
1 4 DLOUIS
1 4 DLOUIS
1 6 DMORAKE
1 7 DMORAKE
1 8 DMORAKE
0 9 DMORAKE

;

data WANT;
input Total_Saved Total_policies user : $20.;
datalines;
2 3 ALUPIYA
1 2 DLOUIS
3 4 DMORAKE
;

[Kurt_Bremser · Accepted solution]

Try this:

data want;
retain total_saved total_policies sv;
set have;
by user notsorted policy_no;
if first.user
then do;
  total_saved = 0;
  total_policies = 0;
end;
if first.policy_no
then do;
  total_policies + 1;
  sv = 0;
end;
sv = max(sv,saved);
if last.policy_no then total_saved + sv;
if last.user;
drop sv saved policy_no;
run;

[Solly7]

Thanks a lot Kurt, it works perfectly fine!

[Solly7]

Hi Kurt, i have other variables other than those ones i have mentioned above that i need to include , for example I have Variable user_type(I ,O R)..how do I include it within the final dataset WANT?

[Kurt_Bremser]

Since you do have some kind of summary in this, you need to decide how you do that for additional variables. You will need more retained variables to keep the state you want.