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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.