Ranking by multiple variables and creating a flag to account for each subgroup

iloveinsurance — Tue, 04 May 2021 16:04:46 GMT

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

Re: Ranking by multiple variables and creating a flag to account for each subgroup

PeterClemmensen — Tue, 04 May 2021 17:52:06 GMT

I'm sorry, I don't follow this logic. Can you be more specific?

Re: Ranking by multiple variables and creating a flag to account for each subgroup

ChrisNZ — Tue, 04 May 2021 20:40:55 GMT

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;

topic Ranking by multiple variables and creating a flag to account for each subgroup in SAS Programming

Ranking by multiple variables and creating a flag to account for each subgroup

Re: Ranking by multiple variables and creating a flag to account for each subgroup

Re: Ranking by multiple variables and creating a flag to account for each subgroup