Hello everybody;
I want to sum obs at specific day and four days before. For instance, consider the following table:
table 1:
name | date | time | Intraday | daily | sum |
A | 12/1/2013 | 9:30:00 | 1 | ||
A | 12/1/2013 | 10:00:00 | 2 | 3 | |
A | 12/2/2013 | 10:30:00 | 3 | ||
A | 12/2/2013 | 11:00:00 | 2 | 5 | |
A | 12/3/2013 | 10:00:00 | 2 | ||
A | 12/3/2013 | 10:30:00 | 1 | ||
A | 12/3/2013 | 11:00:00 | 1 | 4 | |
A | 12/4/2013 | 11:30:00 | 1 | ||
A | 12/4/2013 | 12:00:00 | 3 | ||
A | 12/4/2013 | 12:30:00 | 3 | 7 | |
A | 12/7/2013 | 9:30:00 | 1 | ||
A | 12/7/2013 | 10:00:00 | 1 | 2 | 21 |
A | 12/8/2013 | 10:30:00 | 2 | ||
A | 12/8/2013 | 11:00:00 | 3 | 5 | 23 |
A | 12/17/2013 | 11:30:00 | 1 | ||
A | 12/17/2013 | 12:00:00 | 1 | 2 | 23 |
A | 12/18/2013 | 12:30:00 | 2 | 2 | 20 |
A | 12/28/2013 | 9:30:00 | 1 | ||
A | 12/28/2013 | 10:00:00 | 1 | 2 | 18 |
B | 12/8/2013 | 10:30:00 | 2 | ||
B | 12/8/2013 | 11:30:00 | 2 | ||
B | 12/8/2013 | 12:30:00 | 1 | 5 | |
B | 12/14/2013 | 9:30:00 | 1 | ||
B | 12/14/2013 | 10:30:00 | 1 | 2 | |
B | 12/15/2013 | 12:00:00 | 2 | ||
B | 12/15/2013 | 12:30:00 | 1 | 3 | |
B | 12/24/2013 | 9:30:00 | 1 | 1 | |
B | 12/25/2013 | 10:00:00 | 2 | ||
B | 12/25/2013 | 10:30:00 | 2 | 4 | 15 |
B | 12/26/2013 | 12:00:00 | 2 | 2 | 12 |
B | 12/30/2013 | 9:30:00 | 3 | ||
B | 12/30/2013 | 10:00:00 | 2 | ||
B | 12/30/2013 | 10:30:00 | 1 | 6 | 16 |
B | 1/3/2014 | 11:00:00 | 1 | ||
B | 1/3/2014 | 11:30:00 | 1 | ||
B | 1/13/2014 | 12:00:00 | 1 | 3 | 16 |
Please consider the sum variable which is the aggregate of daily variable for that day and four days before.
For example the value 21 at 12/7/2013 is 3+5+4+7+2 values of daily column. In other words, I want to generate variable sum in table 1.
How can I do that?
Thanks in advance.
That is not called four days before,should call fout non-missing value before.
data have;
infile cards expandtabs truncover;
input name $ date $ time $ Intraday daily;
cards;
A 12/1/2013 9:30:00 1
A 12/1/2013 10:00:00 2 3
A 12/1/2013 10:30:00 3
A 12/2/2013 11:00:00 2 5
A 12/3/2013 10:00:00 2
A 12/3/2013 10:30:00 1
A 12/3/2013 11:00:00 1 4
A 12/4/2013 11:30:00 1
A 12/4/2013 12:00:00 3
A 12/4/2013 12:30:00 3 7
A 12/7/2013 9:30:00 1
A 12/7/2013 10:00:00 1 2
A 12/8/2013 10:30:00 2
A 12/8/2013 11:00:00 3 5
A 12/17/2013 11:30:00 1
A 12/17/2013 12:00:00 1 2
A 12/18/2013 12:30:00 2 2
A 12/28/2013 9:30:00 1
A 12/28/2013 10:00:00 1 2
B 12/8/2013 10:30:00 2
B 12/8/2013 11:30:00 2
B 12/8/2013 12:30:00 1 5
B 12/14/2013 9:30:00 1
B 12/14/2013 10:30:00 1 2
B 12/15/2013 12:00:00 2
B 12/15/2013 12:30:00 1 3
B 12/24/2013 9:30:00 1 1
B 12/25/2013 10:00:00 2
B 12/25/2013 10:30:00 2 4
B 12/26/2013 12:00:00 2 2
B 12/30/2013 9:30:00 3
B 12/30/2013 10:00:00 2
B 12/30/2013 10:30:00 1 6
B 1/3/2014 11:00:00 1
B 1/3/2014 11:30:00 1
B 1/13/2014 12:00:00 1 3
;
run;
data want;
set have;
by name;
array x{0:4} _temporary_;
if first.name then do;i=0; call missing(of x{*});end;
if not missing(daily) then do;
i+1;
x{mod(i,5)}= daily;
end;
if i ge 5 and not missing(daily) then sum=sum(of x{*});
drop i;
run;
That is not called four days before,should call fout non-missing value before.
data have;
infile cards expandtabs truncover;
input name $ date $ time $ Intraday daily;
cards;
A 12/1/2013 9:30:00 1
A 12/1/2013 10:00:00 2 3
A 12/1/2013 10:30:00 3
A 12/2/2013 11:00:00 2 5
A 12/3/2013 10:00:00 2
A 12/3/2013 10:30:00 1
A 12/3/2013 11:00:00 1 4
A 12/4/2013 11:30:00 1
A 12/4/2013 12:00:00 3
A 12/4/2013 12:30:00 3 7
A 12/7/2013 9:30:00 1
A 12/7/2013 10:00:00 1 2
A 12/8/2013 10:30:00 2
A 12/8/2013 11:00:00 3 5
A 12/17/2013 11:30:00 1
A 12/17/2013 12:00:00 1 2
A 12/18/2013 12:30:00 2 2
A 12/28/2013 9:30:00 1
A 12/28/2013 10:00:00 1 2
B 12/8/2013 10:30:00 2
B 12/8/2013 11:30:00 2
B 12/8/2013 12:30:00 1 5
B 12/14/2013 9:30:00 1
B 12/14/2013 10:30:00 1 2
B 12/15/2013 12:00:00 2
B 12/15/2013 12:30:00 1 3
B 12/24/2013 9:30:00 1 1
B 12/25/2013 10:00:00 2
B 12/25/2013 10:30:00 2 4
B 12/26/2013 12:00:00 2 2
B 12/30/2013 9:30:00 3
B 12/30/2013 10:00:00 2
B 12/30/2013 10:30:00 1 6
B 1/3/2014 11:00:00 1
B 1/3/2014 11:30:00 1
B 1/13/2014 12:00:00 1 3
;
run;
data want;
set have;
by name;
array x{0:4} _temporary_;
if first.name then do;i=0; call missing(of x{*});end;
if not missing(daily) then do;
i+1;
x{mod(i,5)}= daily;
end;
if i ge 5 and not missing(daily) then sum=sum(of x{*});
drop i;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.