Solved
Contributor
Posts: 37

# Creating a new variable for a group based on maximum value of another variable

[ Edited ]

I have a dataset  that looks like the following

Customer Type Value

A               2        10

A               2        10

A               1         20
B               1         15

B               2         30

The eventual goal is to not have multiple types of customer. I want to have a condition where the max(value) is used to determine the type of a customer. For customer A, the max(value) is 20. So based on that, I want to take Type=2 and change it for all 3 instances for customer A (potentially create a new variable Type_1). I have created the max value for each customer group but cannot figure out how to set the logic for changing the type.

Any ideas?

Output

Customer Type Value Type_1

A               2        10      1

A               2        10      1

A               1         20     1
B               1         15     2

B               2         30     2

Accepted Solutions
Solution
‎07-19-2018 07:34 PM
Super User
Posts: 2,068

## Re: Creating a new variable for a group based on maximum value of another variable

easiest perhaps

``````proc sort data=have;
by customer descending value;
run;
data want;
set have;
by customer;
retain type_1;
if first.customer then type_1=type;
run;``````

All Replies
Super User
Posts: 2,068

Contributor
Posts: 37

## Re: Creating a new variable for a group based on maximum value of another variable

Apologies. Updated original post.
Super User
Posts: 2,068

## Re: Creating a new variable for a group based on maximum value of another variable

shouldn't type_1 value for customerb be 1 instead of 2? I'm not getting the logic

Contributor
Posts: 37

## Re: Creating a new variable for a group based on maximum value of another variable

the type_1 value should be based on the highest of 'value' field. since highest 'value' for customer B is 30, then type_1 should be 2 for customer B
Super User
Posts: 2,068

## Re: Creating a new variable for a group based on maximum value of another variable

Ok then why is it 2 for customerA  when highest value 20 has 1 for type variable?

Contributor
Posts: 37

## Re: Creating a new variable for a group based on maximum value of another variable

Fudge it! That was a bad mistake. Took me this long to see that! it should be 1. Apologies my friend.
Super User
Posts: 2,068

## Re: Creating a new variable for a group based on maximum value of another variable

``````data have;
input Customer \$ Type Value ;
cards;
A               2        10
A               2        10
A               1         20
B               1         15
B               2         30
;

proc sql;
create table want(drop=m) as
select *,max(m) as type_1
from (select *,(max(value)=value)*type as m from have group by customer)
group by customer;
quit;``````
Solution
‎07-19-2018 07:34 PM
Super User
Posts: 2,068

## Re: Creating a new variable for a group based on maximum value of another variable

easiest perhaps

``````proc sort data=have;
by customer descending value;
run;
data want;
set have;
by customer;
retain type_1;
if first.customer then type_1=type;
run;``````
Contributor
Posts: 37