topic Re: Calculation between rows in SAS Programming

Calculation between rows

Ronein — Thu, 23 Dec 2021 07:11:20 GMT

Hello

I want to re-calculate column Y with the following rule:

The calculation is done per customer ID.

IF Y in time 1 is not null and Y in time 2 is null then Y in time 2 will be equal to Y in time 1.

What is the way to do it please?

Data have;
input ID time Y;
cards;
111 1 8
111 2 .
111 3 7
111 4 7
111 5 6
111 6 8
222 1 4
222 2 5
222 3 4
222 4 6
222 5 6
222 6 7
;
Run;

Re: Calculation between rows

PeterClemmensen — Thu, 23 Dec 2021 07:20:08 GMT

What does your desired result look like then?

Re: Calculation between rows

maguiremq — Thu, 23 Dec 2021 10:43:19 GMT

This sounds a lot like carrying the last observation forward. It doesn't follow your request parameters, but it gets what you want, I think.

Data have;
input ID time Y;
cards;
111 1 8
111 2 .
111 3 7
111 4 7
111 5 6
111 6 8
222 1 4
222 2 5
222 3 4
222 4 6
222 5 6
222 6 7
;
Run;

data want;
	set have;
	by id;
	retain y2;
		if first.id then call missing(y2);
		if not missing(y) then y2 = y;
run;

/* Removing the original variable for cleaner output (not shown) */

data want (drop = y rename = (y2 = y));
	set have;
	by id;
	retain y2;
		if first.id then call missing(y2);
		if not missing(y) then y2 = y;
run;

ID time Y y2 
111 1 8 8 
111 2 . 8 
111 3 7 7 
111 4 7 7 
111 5 6 6 
111 6 8 8 
222 1 4 4 
222 2 5 5 
222 3 4 4 
222 4 6 6 
222 5 6 6 
222 6 7 7

Re: Calculation between rows

Kurt_Bremser — Thu, 23 Dec 2021 11:04:27 GMT

The acronym for this is LOCF (Last Observation Carry Forward).

data want;
set have;
by id;
retain _y;
if first.id then _y = y;
if missing(y)
then y = _y;
else _y = y;
drop _y;
run;

Re: Calculation between rows

r_behata — Thu, 23 Dec 2021 16:13:00 GMT

data want;
	update have(obs=0) have;
	by id;
	output;
run;

Re: Calculation between rows

Ronein — Thu, 23 Dec 2021 16:30:55 GMT

111 1 8
111 2 8
111 3 7
111 4 7
111 5 6
111 6 8
222 1 4
222 2 5
222 3 4
222 4 6
222 5 6
222 6 7

Re: Calculation between rows

Ronein — Thu, 23 Dec 2021 16:31:44 GMT

May you please explain what this code is doing?

Re: Calculation between rows

Ronein — Thu, 23 Dec 2021 16:37:11 GMT

Hello,

I think that you didn't relate your code to the request of filling null value only in second row per cudtomer ID.It means that if 3rd,4th ,5th,6th rows are with null Y value then no need to change it.

The only potetnail change is in 2nd row per customer ID