BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
eemrun
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

9 REPLIES 9
eemrun
Obsidian | Level 7
Apologies. Updated original post.
novinosrin
Tourmaline | Level 20

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

eemrun
Obsidian | Level 7
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
novinosrin
Tourmaline | Level 20

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

eemrun
Obsidian | Level 7
Fudge it! That was a bad mistake. Took me this long to see that! it should be 1. Apologies my friend.
novinosrin
Tourmaline | Level 20
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;
novinosrin
Tourmaline | Level 20

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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