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

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

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

Posted in reply to novinosrin

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;

View solution in original post


All Replies
Super User
Posts: 2,068

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

your output sample plz?

Contributor
Posts: 37

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

Posted in reply to novinosrin
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

Posted in reply to novinosrin
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

Posted in reply to novinosrin
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

Posted in reply to novinosrin

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

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

Posted in reply to novinosrin
perfect....thanks!
☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 120 views
  • 0 likes
  • 2 in conversation