first. last. and comparing with previous observation

RAVI2000 — Tue, 15 Nov 2022 15:13:04 GMT

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.

Re: first. last. and comparing with previous observation

LinusH — Tue, 15 Nov 2022 15:18:17 GMT

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?

Re: first. last. and comparing with previous observation

RAVI2000 — Tue, 15 Nov 2022 15:53:38 GMT

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

Re: first. last. and comparing with previous observation

LinusH — Tue, 15 Nov 2022 16:09:41 GMT

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).

Re: first. last. and comparing with previous observation

RAVI2000 — Tue, 15 Nov 2022 16:40:36 GMT

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

Re: first. last. and comparing with previous observation

Kurt_Bremser — Tue, 15 Nov 2022 16:59:22 GMT

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.

Re: first. last. and comparing with previous observation

RAVI2000 — Wed, 16 Nov 2022 15:57:48 GMT

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?

Re: first. last. and comparing with previous observation

Kurt_Bremser — Thu, 17 Nov 2022 08:34:10 GMT

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

topic Re: first. last. and comparing with previous observation in SAS Programming

first. last. and comparing with previous observation

Re: first. last. and comparing with previous observation

Re: first. last. and comparing with previous observation

Re: first. last. and comparing with previous observation

Re: first. last. and comparing with previous observation

Re: first. last. and comparing with previous observation

Re: first. last. and comparing with previous observation

Re: first. last. and comparing with previous observation