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 ;
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.