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
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;
your output sample plz?
shouldn't type_1 value for customerb be 1 instead of 2? I'm not getting the logic
Ok then why is it 2 for customerA when highest value 20 has 1 for type 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;
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;
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.
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.