topic Re: Rank until it reaches a specific percentage. in SAS Enterprise Guide

Rank until it reaches a specific percentage.

AK100 — Tue, 14 Apr 2020 11:51:27 GMT

Hello lads,

I have a quick question, I have built a cumulative column which sums up the percentages until it reaches 100. Now what I want to do is to make a new column named RANKING, which only puts a 1 as a value for the first N rows who together/combined reach a minimum of 75%. Everything else has to stay blank.

So like this:

DATA HAVE

ID -- Location -- Section -- Date -- - Type -- perc% --Cuml.perc%

01 -- Germany -- BB --1/1/20 -- VW -- 20% --20%

02 -- Germany -- AA --1/1/20 -- AU -- 20% --40%

03 -- Germany -- AA --1/1/20 -- AU -- 30% --70%

04 -- Germany -- BB --1/1/20 -- AU -- 10% --80%

05 -- Germany -- BB --1/1/20 -- MC -- 10% --90%

06 -- Germany -- BB --1/1/20 -- VW -- 10% --100%

07 -- Germany -- AA --2/1/20 -- OP -- 25% --25%

08 -- Germany -- AA --2/1/20 -- AU -- 40% --65%

09 -- Germany -- BB --2/1/20 -- MC -- 20% --85%

10 -- Germany -- BB --2/1/20 -- MC -- 5% --90%

11 -- Germany -- AA --2/1/20 -- OP -- 5% --95%

12 -- Germany -- AA --2/1/20 -- VW -- 5% --100%

DATA WANT:

ID -- Location -- Section -- Date -- - Type -- perc% --Cuml.perc% --RANKING

01 -- Germany -- BB --1/1/20 -- VW -- 20% --20% -- 1

02 -- Germany -- AA --1/1/20 -- AU -- 20% --40% -- 1

03 -- Germany -- AA --1/1/20 -- AU -- 30% --70% -- 1

04 -- Germany -- BB --1/1/20 -- AU -- 10% --80% -- 1

05 -- Germany -- BB --1/1/20 -- MC -- 10% --90% --

06 -- Germany -- BB --1/1/20 -- VW -- 10% --100% --

07 -- Germany -- AA --2/1/20 -- OP -- 25% --25% -- 1

08 -- Germany -- AA --2/1/20 -- AU -- 40% --65% -- 1

09 -- Germany -- BB --2/1/20 -- MC -- 20% --85% -- 1

10 -- Germany -- BB --2/1/20 -- MC -- 5% --90% --

11 -- Germany -- AA --2/1/20 -- OP -- 5% --95% --

12 -- Germany -- AA --2/1/20 -- VW -- 5% --100% --

I hope its clear guys, I appreciate your help. Thank you in advance!

Re: Rank until it reaches a specific percentage.

Kurt_Bremser — Tue, 14 Apr 2020 12:07:25 GMT

data want;
set have;
by location date;
retain ranking;
if first.date then ranking = 1;
output;
if cum_perc > 75 then ranking = .;
run;

Untested, for lack of usable data.

Re: Rank until it reaches a specific percentage.

kelxxx — Tue, 14 Apr 2020 12:11:02 GMT

data want;
set have;
ranking=lag(cumlperc);
if cumlperc le 0.75 then ranking=1;
else if ranking lt 0.75 then ranking=1;
else ranking=.;
run;

I hope this can help you

Re: Rank until it reaches a specific percentage.

AK100 — Tue, 14 Apr 2020 12:14:07 GMT

Thanks, this worked perfectly

Re: Rank until it reaches a specific percentage.

AK100 — Tue, 14 Apr 2020 12:16:45 GMT

Thanks Kurt,

I think I can use this one if I change a few things, for now it says 'by variables not properly sorted'.

Re: Rank until it reaches a specific percentage.

Kurt_Bremser — Tue, 14 Apr 2020 12:16:35 GMT

As I said, untested.

Re: Rank until it reaches a specific percentage.

AK100 — Fri, 01 May 2020 10:28:44 GMT

Hello @Kurt_Bremser and @kelxxx

The code you gave me worked perfectly until I got a first variable who directly was greater then or equal to 0.75. That one did not get a ranking 1. It only gets a ranking when the first codes few coded together reach 0.75 but like I said now I have one who directly reaches 0.75+. This is the code I used right now, but it doesnt work:

data matselectie1;
	set percentage_count;
	Selectie=lag(totpercentage);

	if totpercentage le 0.75 then
		Selectie=1;
	else if FIRST.totpercentage=1 ge 0.75 then
		Selectie=1;
	else if Selectie lt 0.75 then
		Selectie=1;
	else Selectie=.;
run;

Re: Rank until it reaches a specific percentage.

Kurt_Bremser — Fri, 01 May 2020 10:56:04 GMT

I hate to repeat myself, but: "untested, for lack of usable data". "Usable data" also includes "edge cases" like the one you described.