Selecting the last two values of a group

[LoPez_Diaz]

Hello , 

I'm using SAS EG7.1 , and I have a dataset composed of three columns :  Date, Mat , Tx

for a given date we can have multiple rows with different MAT and TX values.

I want to select the last two available TX values for every MAT value.

The data is sorted by date ascending.

My dataset has the following format : 

DATE	MAT	TX
10/01/2017	5	2,34
13/01/2017	5	2,34
13/01/2017	7	2,86
17/01/2017	5	2,34
20/01/2017	5	2,34
24/01/2017	3	2,19
24/01/2017	5	2,34
26/01/2017	7	2,91
27/01/2017	3	2,19
27/01/2017	5	2,40
31/01/2017	6	2,62
31/01/2017	4	2,29
03/02/2017	6	2,62
03/02/2017	4	2,29
07/02/2017	5	2,40
07/02/2017	6	2,60
07/02/2017	3	2,17
10/02/2017	6	2,60
10/02/2017	5	2,40
10/02/2017	3	2,17

The Output I want is : 

DATE	MAT	TX
07/02/2017	3	2,17
10/02/2017	3	2,17
31/01/2017	4	2,29
03/02/2017	4	2,29
07/02/2017	5	2,40
10/02/2017	5	2,40
07/02/2017	6	2,60
10/02/2017	6	2,60
13/01/2017	7	2,86
26/01/2017	7	2,91

Thank you in advance for your help.

[ballardw · Accepted solution]

---

@LoPez_Diaz wrote:

Hello , 

I'm using SAS EG7.1 , and I have a dataset composed of three columns :  Date, Mat , Tx

for a given date we can have multiple rows with different MAT and TX values.

I want to select the last two available TX values for every MAT value.

The data is sorted by date ascending.

My dataset has the following format : 

DATE	MAT	TX
10/01/2017	5	2,34
13/01/2017	5	2,34
13/01/2017	7	2,86
17/01/2017	5	2,34
20/01/2017	5	2,34
24/01/2017	3	2,19
24/01/2017	5	2,34
26/01/2017	7	2,91
27/01/2017	3	2,19
27/01/2017	5	2,40
31/01/2017	6	2,62
31/01/2017	4	2,29
03/02/2017	6	2,62
03/02/2017	4	2,29
07/02/2017	5	2,40
07/02/2017	6	2,60
07/02/2017	3	2,17
10/02/2017	6	2,60
10/02/2017	5	2,40
10/02/2017	3	2,17

The Output I want is : 

DATE	MAT	TX
07/02/2017	3	2,17
10/02/2017	3	2,17
31/01/2017	4	2,29
03/02/2017	4	2,29
07/02/2017	5	2,40
10/02/2017	5	2,40
07/02/2017	6	2,60
10/02/2017	6	2,60
13/01/2017	7	2,86
26/01/2017	7	2,91

Thank you in advance for your help.

---

Not particularly fancy but works for your example data. Note that I use decimals instead of commas for numeric values due to NLS.

The first sort, second data step and second sort are the parts you need for your data.

data have;
   input DATE :ddmmyy10. MAT TX  ;
   format date ddmmyy10.;
datalines;
10/01/2017 5 2.34 
13/01/2017 5 2.34 
13/01/2017 7 2.86 
17/01/2017 5 2.34 
20/01/2017 5 2.34 
24/01/2017 3 2.19 
24/01/2017 5 2.34 
26/01/2017 7 2.91 
27/01/2017 3 2.19 
27/01/2017 5 2.40 
31/01/2017 6 2.62 
31/01/2017 4 2.29 
03/02/2017 6 2.62 
03/02/2017 4 2.29 
07/02/2017 5 2.40 
07/02/2017 6 2.60 
07/02/2017 3 2.17 
10/02/2017 6 2.60 
10/02/2017 5 2.40 
10/02/2017 3 2.17 
;
proc sort data=have;
  by mat descending date;
run;
data want;
   set have;
   retain count;
   by mat descending date;
   if first.mat then count=1;
   else count+1;
   if count le 2;
   drop count;
run;
proc sort data=want;
   by mat date;
run;

[LoPez_Diaz]

Hello Ballardw ,
This works great !! Thank you for your fast reply !!
Have a nice day!

[novinosrin]

Hi @LoPez_Diaz  Too late as I missed to notice your thread, but FWIW

data have;
   input DATE :ddmmyy10. MAT TX  ;
   format date ddmmyy10.;
datalines;
10/01/2017 5 2.34 
13/01/2017 5 2.34 
13/01/2017 7 2.86 
17/01/2017 5 2.34 
20/01/2017 5 2.34 
24/01/2017 3 2.19 
24/01/2017 5 2.34 
26/01/2017 7 2.91 
27/01/2017 3 2.19 
27/01/2017 5 2.40 
31/01/2017 6 2.62 
31/01/2017 4 2.29 
03/02/2017 6 2.62 
03/02/2017 4 2.29 
07/02/2017 5 2.40 
07/02/2017 6 2.60 
07/02/2017 3 2.17 
10/02/2017 6 2.60 
10/02/2017 5 2.40 
10/02/2017 3 2.17 
;

proc sql;
create table want(drop=t) as
select *
from
(select *, date<max(date) as t from have group by mat) 
group by mat, t
having max(date)=date and t=1 or t=0
order by mat,date;
quit;

[LoPez_Diaz]

This also works ! Thank you ! I was looking for a sql procedure to it , and this is it !
Thank you again