topic Re: Aggregating /Summing in SAS Programming

Aggregating /Summing

RandyStan — Thu, 23 Jan 2020 10:22:09 GMT

Dear All:

I asked a Summing question yesterday, but the problem is a little more complicated.

My data by ID is as follows

My data is as follows.

DATE ID Time VariableA VariableB

01JAN2019 X 9:30:05 B 100

01JAN2019 X 9:30:08 S -25

02JAN2019 X 10:45:45 S -25

02JAN2019 X 11:26:13 B 35

03JUL2019 X 13:15:58 B 205

03JUL2019 X 13:45:08 S - 45

05JAN2019 Y 14:45:05 S -145

05JAN2019 Y 15:15:06 B 130

06JAN2019 Y 13:02:09 B 150

07AUG2019 Y 10:55:08 B 200

07AUG2019 Y 12:13:57 S -150

09OCT2019 Y 11:33:45 B 50

I need to sum VARB but after 01JUL2019 the counter resets to 0. So the data I want is

DATE ID Time VariableA VariableB Var_AGG

01JAN2019 X 9:30:05 B 100 100

01JAN2019 X 9:30:08 S -25 75

02JAN2019 X 10:45:45 S -25 50

02JAN2019 X 11:26:13 B 35 85

03JUL2019 X 13:15:58 B 205 205

03JUL2019 X 13:45:08 S -45 160

05JAN2019 Y 14:45:05 S -145 -145

05JAN2019 Y 15:15:06 B 130 -15

06JAN2019 Y 13:02:09 B 150 135

07AUG2019 Y 10:55:08 B 200 200

07AUG2019 Y 12:13:57 S -150 50

09OCT2019 Y 11:33:45 B 50 100

The code I wrote for the simple summing is

data want ; set have ;
by date ID VariableB;
If first.ID then VAR_AGG = 0 ;
VAR_AGG + VARIABLEB ;
run;

But how do I reset the counter after 01JUL2019?

Thanks in a advance

Re: Aggregating /Summing

PeterClemmensen — Thu, 23 Jan 2020 11:27:29 GMT

You can do like this

data have;
input DATE :date9. ID $ Time :time8. VariableA $ VariableB;
format DATE date9. Time time8.;
datalines; 
01JAN2019  X  9:30:05  B         100 
01JAN2019  X  9:30:08  S         -25 
02JAN2019  X  10:45:45 S         -25 
02JAN2019  X  11:26:13 B         35 
03JUL2019  X  13:15:58 B         205 
03JUL2019  X  13:45:08 S         -45 
05JAN2019  Y  14:45:05 S         -145 
05JAN2019  Y  15:15:06 B         130 
06JAN2019  Y  13:02:09 B         150 
07AUG2019  Y  10:55:08 B         200 
07AUG2019  Y  12:13:57 S         -150 
09OCT2019  Y  11:33:45 B         50  
;

data want; 
	set have;
	by id date;
    var_agg = ifn (lag(Date) <= '01Jul2019'd & Date > '01Jul2019'd | first.id, 0, var_agg);
	var_agg + variableb;
run;

Re: Aggregating /Summing

andreas_lds — Thu, 23 Jan 2020 11:46:51 GMT

To verify that i understood the problem: you need to reset Var_AGG when month changes, correct?

Re: Aggregating /Summing

RandyStan — Thu, 23 Jan 2020 18:38:40 GMT

I have to reset Var_AGG after 01JUL2019

Re: Aggregating /Summing

PeterClemmensen — Thu, 23 Jan 2020 18:55:27 GMT

@RandyStan did you try my code then?

Re: Aggregating /Summing

RandyStan — Sun, 02 Feb 2020 13:58:02 GMT

Sorry for the late reply.

Thank you so much, your solution worked.