topic Re: Group by in SAS Programming

Group by

vedant — Tue, 03 Jan 2017 07:25:16 GMT

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.

Re: Group by

PeterClemmensen — Tue, 03 Jan 2017 08:04:20 GMT

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;

Re: Group by

Astounding — Tue, 03 Jan 2017 08:23:20 GMT

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.