Group by

[vedant]

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.

[PeterClemmensen · Accepted solution]

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;

[Astounding]

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.