my data is like this-
pol_sysid src_custID pol_type
101 1 Auto
102 1 Prop
103 2 Auto
104 2 Auto
105 3 Prop
I want to have output like this-
src_custID pol_desc
1 Multi_policy
2 Multi_policy
3 Mono_prop
A person can have as many number of policies as he want. Kindly help.
I assume that the variable pol_desc indicates if there are multiple instances of the variable src_custID? 🙂
In that case
data have;
input pol_sysid $ src_custID $ pol_type $;
datalines;
101 1 Auto
102 1 Prop
103 2 Auto
104 2 Auto
105 3 Prop
;
proc sql;
create table want as
select src_custID
,case when count(src_custID) > 1 THEN 'Multi_policy'
else 'Mono_prop'
end as pol_desc
from have
group by src_custID;
quit;
I assume that the variable pol_desc indicates if there are multiple instances of the variable src_custID? 🙂
In that case
data have;
input pol_sysid $ src_custID $ pol_type $;
datalines;
101 1 Auto
102 1 Prop
103 2 Auto
104 2 Auto
105 3 Prop
;
proc sql;
create table want as
select src_custID
,case when count(src_custID) > 1 THEN 'Multi_policy'
else 'Mono_prop'
end as pol_desc
from have
group by src_custID;
quit;
It might be simpler to use a DATA step:
data want;
set have;
by src_custID;
if last.src_custID;
if first.src_custID=0 then pol_type='Multi_policy';
else pol_type = 'Mono_' || pol_type;
run;
This assumes (as in your example) that any 2+ policies constitute a "Multi_policy". The policies can be of the same type or different types.
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.