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!
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
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.
Thanks Kurt,
I think I can use this one if I change a few things, for now it says 'by variables not properly sorted'.
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
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;
I hate to repeat myself, but: "untested, for lack of usable data". "Usable data" also includes "edge cases" like the one you described.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.