It's a little difficult to figure out what is going on. Instead of providing a data set, you should write a DATALINES statement that captures all your issues. People here, including myself, will not download or open an attachment. I've included my own data set based off your information, but I don't know if I captured all the idiosyncrasies of your data set.
data have;
input ename :$3. geogn :$15. year $ acc_pay acc_pay_tur acc_sta :$15. lags18;
infile datalines dlm = "," dsd;
datalines;
MOQ,BELGIUM,2018,4122,6.0917808219,Local standards,2.33
MOQ,BELGIUM,2019,1231,6.0917808219,Local standards,14.22
MOQ,BELGIUM,2010,4122,6.0917808219,Local standards,1.21
MOQ,BELGIUM,2011,1231,6.0917808219,Local standards,10.71
MOQ,BELGIUM,2014,2317,6.0917808219,Local standards,3.71
MOQ,BELGIUM,2015,781,13.70323168,Local standards,4.68
MOQ,AUSTRALIA,1999,4122,6.0917808219,Local standards,1.21
MOQ,AUSTRALIA,2000,1231,6.0917808219,Local standards,10.71
MOQ,AUSTRALIA,2002,2317,6.0917808219,Local standards,3.71
MOQ,AUSTRALIA,2004,781,13.70323168,Local standards,4.68
MOQ,AUSTRALIA,2005,1585,11.488588335,Local standards,4.58
MOQ,AUSTRALIA,2007,881,6.4921190893,IFRS,2.68
MOQ,AUSTRALIA,2008,893,4.3393461105,IFRS,1.78
;
run;
proc sort data = have
out = have_2;
by geogn descending year; /* get the most recent year on top for each geogn */
run;
data have_3;
set have_2;
by geogn descending year; /* do the following for each geogn by year */
y = 0; /* creating variable 'y' that is equal to 0 for the entire data set */
if first.geogn then counter = 1; /* if it's the first country, then set counter = 1 */
else counter + 1; /* Otherwise, if it's the same country, increment by 1 */
if counter in (1:5) then x = 1; /* if the counter is in the first 5, create a flag called x and set to 1 */
else x = 0; /* otherwise set it to 0. */
run;
Here's a helpful blog on by-group processing as well as first last processing.
https://blogs.sas.com/content/iml/2018/02/26/how-to-use-first-variable-and-last-variable-in-a-by-group-analysis-in-sas.html
Also, @Shmuel's method of using a retain statement is more efficient than what I included here.
... View more
