topic summing a variable by condition and id in SAS Programming

summing a variable by condition and id

nataliavanessa0 — Thu, 13 May 2021 18:27:26 GMT

Hi ,
I am trying to sum one variable as long as another remains constant. I want to cumulative sum dur as long as a is constant. when a changes the sum restarts. when a new id, the sum restarts.
id a dur
1 1 30
1 1 30
1 2 10
2 1 20
2 2 10
2 2 10
3 2 10
3 1 10

and I would like to do this:
id a dur sum
1 1 30 30
1 1 30 60
1 2 10 10
2 1 20 20
2 2 10 10
2 2 10 20
3 2 10 10
3 1 10 10

Thanks

Re: summing a variable by condition and id

CarmineVerrell — Thu, 13 May 2021 18:34:59 GMT

You would need to sort your data and then use a data step.

Here is an example.

data indata;
infile cards;
input id a dur;
cards;
1 1 30
1 1 30
1 2 10
2 1 20
2 2 10
2 2 10
3 2 10
3 1 10
;
run;

Proc sort data=indata ;
by id a;
run;

Data outdata;
set indata;
by id a;
if first.a=1 then cumdur=0;
cumdur+dur;
if last.a;
run;

Re: summing a variable by condition and id

nataliavanessa0 — Thu, 13 May 2021 18:47:24 GMT

thank you @CarmineVerrell

But what if I don't want to combine all 1s and all 2s by ID, I just one to sum dur while a is constant but when it changes I want to restart the sum until it changes again?
something like this:

and I would like to do this:
id a dur sum
1 1 30 30
1 1 30 60
1 2 10 10
1 1 10 10

Thanks.

Re: summing a variable by condition and id

ballardw — Thu, 13 May 2021 18:54:31 GMT

@nataliavanessa0 wrote:
thank you @CarmineVerrell

But what if I don't want to combine all 1s and all 2s by ID, I just one to sum dur while a is constant but when it changes I want to restart the sum until it changes again?
something like this:

and I would like to do this:
id a dur sum
1 1 30 30
1 1 30 60
1 2 10 10
1 1 10 10

Thanks.

That would be the NOTSORTED option on the BY statement.

Data outdata;
   set indata;
   by notsorted id a;
   if first.a=1 then cumdur=0;
   cumdur+dur;
run;

Notsorted aplies to all the the variables on the by statement regardless of the position it appears in the By statement.