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.
@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 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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.