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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: