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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.