BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vedant
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

2 REPLIES 2
PeterClemmensen
Tourmaline | Level 20

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
PROC Star

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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