topic Re: How to find the top 10 and last 10 values from a variable? in SAS Programming

How to find the top 10 and last 10 values from a variable?

Babloo — Thu, 26 Jul 2018 11:18:05 GMT

I have a data as follows and I need to find the top 10 and last 10 values from a variable. Appreciate if someone of you help me to achieve the desired output.

Id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

Desired output:

top_10	last_10
1	15
2	16
3	17
4	18
5	19
6	20
7	21
8	22
9	23
10	24

Re: How to find the top 10 and last 10 values from a variable?

Kurt_Bremser — Thu, 26 Jul 2018 11:37:15 GMT

Read into an array, and use the nobs=option to help in determining the last 10 items:

data want (keep=first_10 last_10);
array first {10};
array last {10};
n = 0;
do until (eof);
  n + 1;
  set have nobs=nobs end=eof;
  if n <= 10 then first{n} = id;
  if n >= nobs - 9 then last{n+10-nobs} = id;
end;
do n = 1 to 10;
  first_10 = first{n};
  last_10 = last{n};
  output;
end;
run;

Re: How to find the top 10 and last 10 values from a variable?

Babloo — Thu, 26 Jul 2018 11:48:17 GMT

I ran your code and it is not producing the desired results. It is generating missing values for the variables to first_10 and last_10.

Re: How to find the top 10 and last 10 values from a variable?

PaigeMiller — Thu, 26 Jul 2018 11:48:39 GMT

PROC RANK can do this, and can also handle ties if that is a problem. You could also use PROC SORT to find the top 10 and bottom 10, but it will not handle ties. The code provided by @Kurt_Bremser does not seem to handle ties either.

Re: How to find the top 10 and last 10 values from a variable?

Babloo — Thu, 26 Jul 2018 12:01:41 GMT

Could you please help me understand how will you do in proc sort?

Re: How to find the top 10 and last 10 values from a variable?

Kurt_Bremser — Thu, 26 Jul 2018 12:10:39 GMT

@Babloo wrote:

I ran your code and it is not producing the desired results. It is generating missing values for the variables to first_10 and last_10.

Maybe that's because you once again failed to provide proper example data?

data have;
do id = 1 to 24;
output;
end;
run;

data want (keep=first_10 last_10);
array first {10};
array last {10};
n = 0;
do until (eof);
  n + 1;
  set have nobs=nobs end=eof;
  if n <= 10 then first{n} = id;
  if n >= nobs - 9 then last{n+10-nobs} = id;
end;
do n = 1 to 10;
  first_10 = first{n};
  last_10 = last{n};
  output;
end;
run;

proc print data=want noobs;
run;

Result:

first_10    last_10

    1          15  
    2          16  
    3          17  
    4          18  
    5          19  
    6          20  
    7          21  
    8          22  
    9          23  
   10          24

Just what you wanted.

Re: How to find the top 10 and last 10 values from a variable?

PaigeMiller — Thu, 26 Jul 2018 12:17:14 GMT

@Babloo wrote:

Could you please help me understand how will you do in proc sort?

If you run PROC SORT then the first 10 values are the lowest numbers and the last 10 values are the biggest numbers. You extract the top ten and bottom ten from the list and merge them together to get your desired result.

Re: How to find the top 10 and last 10 values from a variable?

Ksharp — Thu, 26 Jul 2018 12:38:20 GMT

Assuming the table has been sorted.

data have;
input Id;
cards;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
;

data first_10 last_10;
 set have nobs=nobs;
 if _n_ lt 11 then output first_10;
 if _n_ gt nobs-10 then output last_10;
run;
data want;
 merge first_10(rename=(id=first_10)) last_10(rename=(id=last_10));
run;

Re: How to find the top 10 and last 10 values from a variable?

FreelanceReinh — Thu, 26 Jul 2018 14:00:49 GMT

@Babloo: From your sample data it is not clear whether you mean the first and last ten or the smallest and largest ten values. Hence the confusion and different approaches.

Re: How to find the top 10 and last 10 values from a variable?

PaigeMiller — Thu, 26 Jul 2018 14:21:58 GMT

@FreelanceReinh wrote:

@Babloo: From your sample data it is not clear whether you mean the first and last ten or the smallest and largest ten values. Hence the confusion and different approaches.

Yes, @FreelanceReinh, very good point. This is a very poor example, or the words used to explain the example need to be clearer.

Re: How to find the top 10 and last 10 values from a variable?

novinosrin — Thu, 26 Jul 2018 14:23:10 GMT

data have;
input Id;
cards;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
;

data want;
first_10=1;
last_10=nobs-9;
output;
do _n_=2 to nobs;
set have point=_n_ nobs=nobs;
first_10=_n_;
last_10=last_10+1;
if first_10>10 then stop;
output;
end;
drop id;
run;