topic Re: Using count function with CASE in SAS Programming

Using count function with CASE

Solly7 — Wed, 15 Sep 2021 10:30:26 GMT

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
;

Re: Using count function with CASE

Kurt_Bremser — Wed, 15 Sep 2021 10:49:48 GMT

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;

Re: Using count function with CASE

Solly7 — Wed, 15 Sep 2021 10:54:33 GMT

Thanks a lot Kurt, it works perfectly fine!

Re: Using count function with CASE

Solly7 — Wed, 15 Sep 2021 12:00:20 GMT

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?

Re: Using count function with CASE

Kurt_Bremser — Wed, 15 Sep 2021 12:12:42 GMT

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.