I have a dataset like this: Account Number Individual Number Vehicle Number Kilometres Driven 1245 1 1 2815 1245 2 1 29536 1245 3 1 1000 1245 1 2 77521 1245 2 2 96643 1245 3 2 2662 1245 1 3 86077 1245 2 3 84798 1245 3 3 67814 1122 1 1 88179 1122 2 1 93435 1122 1 3 84491 1122 2 3 76614 I want to take the minimum kilometres driven by account number and then rank the individuals. Which I have done here: proc sort data=lowestkm; by accountnumber kmdriven; run;
data lowestkm;
set file;
lowestcountaccount +1;
by accountnumber kmdriven;
if first.accountnumber then lowestcountaccount = 1;
run; Which gives me this: Account Number Individual Number Vehicle Number Kilometres Driven lowestcountaccount 1245 1 1 2815 3 1245 2 1 29536 4 1245 3 1 1000 1 1245 1 2 77521 6 1245 2 2 96643 9 1245 3 2 2662 2 1245 1 3 86077 8 1245 2 3 84798 7 1245 3 3 67814 5 1122 1 1 88179 3 1122 2 1 93435 4 1122 1 3 84491 2 1122 2 3 76614 1 The problem is, I can only have the individual flag occur once by each account. I was hoping to create a flag like this: The lowest ranking is 1, so individual number 3 is assigned "Y" for the first vehicle under the first account. The second lowest number after that is 77521, so individual number 1 is assigned "Y" for the second vehicle. Lastly what is left is individual number 2 which is assigned "Y" on the third vehicle. I can have up to 5 individual numbers and 5 vehicle numbers. Account Number Individual Number Vehicle Number Kilometres Driven Principal KM Driven 1245 1 1 2815 1245 2 1 29536 1245 3 1 1000 Y 1245 1 2 77521 Y 1245 2 2 96643 1245 3 2 2662 1245 1 3 86077 1245 2 3 84798 Y 1245 3 3 67814 1122 1 1 88179 Y 1122 2 1 93435 1122 1 3 84491 1122 2 3 76614 Y
... View more