topic Re: Conditional summation and enumeration in SAS Programming

Conditional summation and enumeration

haukewiegand — Tue, 13 Nov 2018 21:31:12 GMT

Hello,

I am new to SAS (using SAS Enterprise 7.1) and I struggle to solve the following problem:

Example Dataset:

data have;
input ID Diff Duration;
datalines;
1 . 10
1 2 13
1 25 14
1 5 5
1 2 7
1 2 9
2 . 3
2 2 5
2 10 7
3 . 4
;

Task: I would like to sum up Duration and give an enumeration variable for a Sequence but only if IDs are similar and Diff is not . and ❤️ (so if Diff<3 I call it a Sequence). What makes the task difficult is that when comparing to subsequent rows the enumeration variable 'Sequence' should only continue counting if the next row does not belong to the current sequence (so if Diff is not . and <3) whereas the summation 'Cummulative_Duration' should start form the beginning in that case.

So the result should look like the following table:

ID Diff Duration Sequence Cummulative_Duration ;
1 . 10 1 10
1 2 13 1 23
1 25 14 2 14
1 5 5 3 5
1 2 7 3 12
1 2 9 3 21
2 . 3 1 3
2 2 5 1 8
2 10 7 2 7
3 . 4 1 4
;

Thanks in advance for taking your time to help me solving this (I tried already half a day ...)!

Re: Conditional summation and enumeration

r_behata — Tue, 13 Nov 2018 22:00:47 GMT

data have;
input ID Diff Duration;
datalines;
1 . 10
1 2 13
1 25 14
1 5 5
1 2 7
1 2 9
2 . 3
2 2 5
2 10 7
3 . 4
;
run;


data want;


set have;
	by id;

retain Sequence Cummulative_Duration;

if first.id then do;
	Sequence =1;
	Cummulative_Duration=Duration;
end;

else if Diff < 3 then do;
	Sequence =Sequence;
	Cummulative_Duration+Duration;
end;
	else do;
		Sequence+1;
		Cummulative_Duration=Duration;
	end;


run;

Re: Conditional summation and enumeration

novinosrin — Tue, 13 Nov 2018 22:38:02 GMT

Assuming i understand your req:

data have;
input ID Diff Duration;
datalines;
1 . 10
1 2 13
1 25 14
1 5 5
1 2 7
1 2 9
2 . 3
2 2 5
2 10 7
3 . 4
;
run;

data want;
if 0 then set have;
sequence=1;
cumulative=0;
do until(last.id);
set have;
by id;
if diff>=3 then do; sequence+1;cumulative=0;end;
cumulative+Duration;
output;
end;
run;

Re: Conditional summation and enumeration

mkeintz — Tue, 13 Nov 2018 22:33:49 GMT

data have;
input ID Diff Duration;
datalines;
1 . 10
1 2 13
1 25 14
1 5 5
1 2 7
1 2 9
2 . 3
2 2 5
2 10 7
3 . 4
;
data want;
  set have;
  by id;
  if diff<=3 then cumulative_duration+duration;
  else cumulative_duration=duration;
  sequence+(diff>3);
  if first.id then do;
    sequence=1;
    cumulative_duration=duration;
  end;
run;

Notice that the "if diff<=3 then cumulative_duration+duration;" statement doesn't have an equal sign, typically needed to assign a value. This means the expression "cumulative_duration+duration" is a summing expression, not an assignment. The summing expression means (1) perform the summation, (2) using a RETAINED value of cumulative_duration, and (3) if cumulative_duration is missing, treat it as a zero.
The "sequence+(diff>3)" is a summing statement, adding 1 to sequence whenever diff>3).

Re: Conditional summation and enumeration

haukewiegand — Tue, 13 Nov 2018 23:43:26 GMT

Thanks for the explanations! Probably that was where my trials failed ...