topic Creating a new variable for a group based on maximum value of another variable in SAS Programming

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

eemrun — Wed, 18 Jul 2018 05:06:35 GMT

I have a dataset that looks like the following

Customer Type Value

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 1 20 1
B 1 15 2

B 2 30 2

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

novinosrin — Wed, 18 Jul 2018 04:06:10 GMT

your output sample plz?

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

eemrun — Wed, 18 Jul 2018 04:21:05 GMT

Apologies. Updated original post.

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

novinosrin — Wed, 18 Jul 2018 04:24:13 GMT

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

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

eemrun — Wed, 18 Jul 2018 04:43:09 GMT

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

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

novinosrin — Wed, 18 Jul 2018 05:04:28 GMT

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

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

eemrun — Wed, 18 Jul 2018 05:07:10 GMT

Fudge it! That was a bad mistake. Took me this long to see that! it should be 1. Apologies my friend.

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

novinosrin — Wed, 18 Jul 2018 05:17:35 GMT

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;

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

novinosrin — Wed, 18 Jul 2018 05:24:30 GMT

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;

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

eemrun — Thu, 19 Jul 2018 23:35:03 GMT

perfect....thanks!