topic Re: Selecting the last two values of a group in SAS Programming

Selecting the last two values of a group

LoPez_Diaz — Mon, 09 Sep 2019 15:37:14 GMT

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.

Re: Selecting the last two values of a group

ballardw — Mon, 09 Sep 2019 16:00:29 GMT

@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;

Re: Selecting the last two values of a group

LoPez_Diaz — Mon, 09 Sep 2019 16:21:43 GMT

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

Re: Selecting the last two values of a group

novinosrin — Mon, 09 Sep 2019 16:44:23 GMT

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;

Re: Selecting the last two values of a group

LoPez_Diaz — Tue, 10 Sep 2019 08:03:47 GMT

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