first. last. and comparing with previous observation

[RAVI2000]

Hello all,

I would like to output records of AEOUT which are being collected incorrectly for the same AEDECOD. For instance, consider the 1st row obs AETOXGR = 2 and has AEOUT="NOT RECOVERED/NOT RESOLVED.
The next 2nd row obs has AETOXGR=3 with AEOUT = "NOT RECOVERED/NOT RESOLVED". This is incorrect, the 2nd row obs should instead have "NOT RECOVERED/NOT RESOLVED" since the AETOXGR is increasing 2 to 3.

And vice-versa, where the 2nd row obs AETOXGR = 3 and AEOUT="NOT RECOVERED/NOT RESOLVED" and the 3rd row obs with AETOXGR = 2 have AEOUT="NOT RECOVERED/NOT RESOLVED",
instead the 3rd row obs should have been "RECOVERING/RESOLVING" since the AETOXGR is being decreased from 3 to 2.

Below is the sample data.

SUBJID  GRPID   AESTDTC      AEENDTC     AEDECOD                AEOUT                   SSEQ  AETOXGR
100012 	 1      2021-03-25  2021-04-13 	Anaemia            	NOT RECOVERED/NOT RESOLVED 	1.1  	2
100012 	 1      2021-04-14  2021-04-21 	Anaemia            	NOT RECOVERED/NOT RESOLVED 	1.2  	3
100012 	 1      2021-04-22  2021-04-27 	Anaemia            	NOT RECOVERED/NOT RESOLVED 	1.3  	2
100012 	 1      2021-04-28  2021-05-04 	Anaemia            	RECOVERING/RESOLVING 		1.4  	3
100012 	 1      2021-05-05  2021-05-10 	Anaemia            	NOT RECOVERED/NOT RESOLVED 	1.5  	2
100012 	 1      2021-05-11  2021-05-17 	Anaemia            	RECOVERING/RESOLVING 		1.6  	3
100012 	 1      2021-05-17  2021-05-25 	Anaemia            	NOT RECOVERED/NOT RESOLVED 	1.7  	2
100012 	 1      2021-05-26  2021-06-03 	Anaemia            	RECOVERING/RESOLVING 		1.8  	3
100012 	 1      2021-06-04  2021-06-09 	Anaemia            	RECOVERING/RESOLVING 		1.9  	2
100012 	 1      2021-06-10  2021-06-15 	Anaemia            	NOT RECOVERED/NOT RESOLVED 	1.10  	1
100012 	 1      2021-06-16  2021-07-05 	Anaemia            	NOT RECOVERED/NOT RESOLVED 	1.11  	2
100012 	 1      2021-07-06  2021-08-05 	Anaemia            	RECOVERING/RESOLVING 		1.12  	3
100012 	 1      2021-08-06  2021-08-15 	Anaemia            	NOT RECOVERED/NOT RESOLVED 	1.13  	2
100012 	 1      2021-08-16  2021-08-22 	Anaemia            	RECOVERING/RESOLVING 		1.14  	3
100012 	 1      2021-08-23  2021-08-29 	Anaemia            	NOT RECOVERED/NOT RESOLVED 	1.15  	2
100012 	 1      2021-08-30  2021-09-06 	Anaemia            	RECOVERING/RESOLVING 		1.16  	3
100012 	 1      2021-09-07  2021-09-13 	Anaemia            	NOT RECOVERED/NOT RESOLVED 	1.17  	2
100012 	 1      2021-09-14  2021-10-03 	Anaemia            	RECOVERING/RESOLVING 		1.18  	3
100012 	 1      2021-10-04  2021-10-10 	Anaemia            	NOT RECOVERED/NOT RESOLVED 	1.19  	2
100012 	 1      2021-10-11  			Anaemia            	NOT RECOVERED/NOT RESOLVED 	1.20  	3
100012 	 3      2021-02-21  2021-02-21 	Thrombocytopenia   	RECOVERED/RESOLVED 			2.1  	4
100012 	 4      2021-03-03  2021-03-08 	Thrombocytopenia 	RECOVERING/RESOLVING 		3.1  	3
100012 	 4      2021-03-09  2021-03-15 	Thrombocytopenia 	RECOVERING/RESOLVING 		3.2  	2
100012 	 4      2021-03-16  2021-03-23 	Thrombocytopenia 	NOT RECOVERED/NOT RESOLVED 	3.3  	1
100012 	 4      2021-03-24  2021-04-07 	Thrombocytopenia 	RECOVERING/RESOLVING 		3.4  	2
100012 	 4      2021-04-08  2021-04-21 	Thrombocytopenia 	NOT RECOVERED/NOT RESOLVED 	3.5  	1
100012 	 4      2021-04-22  2021-04-27 	Thrombocytopenia 	RECOVERING/RESOLVING 		3.6  	2
100012 	 4      2021-04-28  2021-05-04 	Thrombocytopenia 	NOT RECOVERED/NOT RESOLVED 	3.7  	1
100012 	 4      2021-05-05  2021-05-10 	Thrombocytopenia 	NOT RECOVERED/NOT RESOLVED 	3.8  	2
100012 	 4      2021-05-11  2021-06-09 	Thrombocytopenia 	RECOVERING/RESOLVING 		3.9 	3
100012 	 4      2021-06-10  2021-06-15 	Thrombocytopenia 	NOT RECOVERED/NOT RESOLVED 	3.10 	2
100012 	 4      2021-06-16  2021-07-05 	Thrombocytopenia 	RECOVERING/RESOLVING 		3.11 	3
100012 	 4      2021-07-06  2021-07-27 	Thrombocytopenia 	NOT RECOVERED/NOT RESOLVED 	3.12 	2
100012 	 4      2021-07-28  2021-08-29 	Thrombocytopenia 	NOT RECOVERED/NOT RESOLVED 	3.13 	3
100012 	 4      2021-08-30  2021-10-21 	Thrombocytopenia 	RECOVERING/RESOLVING 		3.14 	4

NOTE: obs 21 have sseq as 2.1 and obs 22 has sseq 3.1 with same AEDECOD(Thrombocytopenia)). SSEQ is derived as : "All event records of same AEDECOD from the same subject are linked by the AESTDTC and AEENDTC. If an event is followed by another event with same AEDECOD with and AESTDTC the same day or 1 day after the AEENDTC of the previous record.

I have tried the below code.

data adae2 adae3;
	set adae1;
	retain grade;
	by subjid aedecod aestdtc aeendtc;
	if first.aedecod then grade=aetoxgr;
	
	if last.aedecod and aetoxgr lt grade then adae3;
run;

I was only able to satisfy one condition. Please suggest an appropriate method.

[LinusH]

Bit lazy to through all your logic, but I don't understand this part of your code:
"then adae3;"
Maybe you wish to assign grade here?

[RAVI2000]

ohh, i missed OUTPUT option in between. i want to output all the records to adae3

[LinusH]

Show the complete code that you actually have executed.
If you wish to understand how your code works with the actual data, you can make use of the data step debugger, available in Enterprise Guide and SAS Display Manager (aka Base SAS).

[RAVI2000]

I have provided it in the above, thats all i have used for now!

[Kurt_Bremser]

The next 2nd row obs has AETOXGR=3 with AEOUT = "NOT RECOVERED/NOT RESOLVED". This is incorrect, the 2nd row obs should instead have "NOT RECOVERED/NOT RESOLVED" since the AETOXGR is increasing 2 to 3.

The second row does already have the "correct" value, so this sentence makes no sense at all.

[RAVI2000]

You're right @Kurt_Bremser, I was looking at other obs and gave the wrong example. Could you please consider the other cases where it is incorrect like obs 3 and so on?

[Kurt_Bremser]

Post usable example data in a data step with datalines, as you have been shown here and as you have done yourself here.