DATA Step, Macro, Functions and more

Group by

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Group by

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.


Accepted Solutions
Solution
‎01-03-2017 03:54 AM
PROC Star
Posts: 768

Re: Group by

[ Edited ]

I assume that the variable pol_desc indicates if there are multiple instances of the variable src_custID? Smiley Happy

 

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;

View solution in original post


All Replies
Solution
‎01-03-2017 03:54 AM
PROC Star
Posts: 768

Re: Group by

[ Edited ]

I assume that the variable pol_desc indicates if there are multiple instances of the variable src_custID? Smiley Happy

 

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;
Super User
Posts: 5,518

Re: Group by

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 192 views
  • 2 likes
  • 3 in conversation