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
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;
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;
To verify that i understood the problem: you need to reset Var_AGG when month changes, correct?
I have to reset Var_AGG after 01JUL2019
@RandyStan did you try my code then?
Sorry for the late reply.
Thank you so much, your solution worked.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.