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 |
I'm sorry, I don't follow this logic. Can you be more specific?
If I understand correctly, this does what you need.
Untested as you did not provide data in usable form (data step or SQL create/insert step).
data WANT;
set HAVE;
by ACCOUNTNUMBER KM;
length DRIVERS VEHICLES $80 ;
if index(DRIVERS,DRIVER)=0 & index(VEHICLES,VEHICLE)=0 then do;
PRINCIPAL = 'Y';
DRIVERS = catx('|',DRIVERS ,DRIVER );
VEHICLES = catx('|',VEHICLES,VEHICLE);
end;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.