Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Calulate Average of previous and Next value

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 08-21-2010 11:43 AM
(1053 views)

Hi,

I need to calculate Average as shown in below table,

by zone and time.

Average should be of previous and the next number

for ex: for zone A for time 00:30 it is average of demand of time 00:15 and 00:45.

Time Zone demand Average

0:15 A 10 10

0:30 A 20 20

0:45 A 30 17.5

0:15 B 15 15

0:30 B 18 17.5

0:45 B 20 17.5

0:15 C 17 17

0:30 C 19 19

0:45 C 21 11

0:15 D 3 3

0:30 D 8 11

0:45 D 19 8

please help regarding the code.

thanks

I need to calculate Average as shown in below table,

by zone and time.

Average should be of previous and the next number

for ex: for zone A for time 00:30 it is average of demand of time 00:15 and 00:45.

Time Zone demand Average

0:15 A 10 10

0:30 A 20 20

0:45 A 30 17.5

0:15 B 15 15

0:30 B 18 17.5

0:45 B 20 17.5

0:15 C 17 17

0:30 C 19 19

0:45 C 21 11

0:15 D 3 3

0:30 D 8 11

0:45 D 19 8

please help regarding the code.

thanks

5 REPLIES 5

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Explore using the LAG function - the SAS DOC section below demonstrates a DATA step approach to obtaining a "Leading Series" calculation.

Scott Barry

SBBWorks, Inc.

http://support.sas.com/documentation/cdl/en/etsug/60372/HTML/default/etsug_tsdata_sect051.htm

Suggested Google advanced search argument, this topic / post:

lag function site:sas.com

next observation value calculation site:sas.com

lead function site:sas.com

Scott Barry

SBBWorks, Inc.

http://support.sas.com/documentation/cdl/en/etsug/60372/HTML/default/etsug_tsdata_sect051.htm

Suggested Google advanced search argument, this topic / post:

lag function site:sas.com

next observation value calculation site:sas.com

lead function site:sas.com

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

While I don't understand your example, the code offered by Howard Schrier at:

http://www.sascommunity.org/wiki/Look-Ahead_and_Look-Back

will definitely give you a way to average the previous and next values.

HTH,

Art

http://www.sascommunity.org/wiki/Look-Ahead_and_Look-Back

will definitely give you a way to average the previous and next values.

HTH,

Art

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

You can also find DATA step examples at:

http://support.sas.com/kb/25/027.html

PROC EXPAND can also be used to calculate moving averages, see:

http://www2.sas.com/proceedings/forum2008/093-2008.pdf

http://support.sas.com/kb/25/027.html

PROC EXPAND can also be used to calculate moving averages, see:

http://www2.sas.com/proceedings/forum2008/093-2008.pdf

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

It really helped. Thank you!

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

You can use the **cmovave** transformation operation in PROC EXPAND:

```
proc expand data=have out=want;
by zone;
id time;
convert demand = avg / transform=(cmovave 3);
run;
```

If you want a pure DATA step approach, you can calculate a lag and lead, then divide. Attached is a program that will let you calculate leads efficiently.

```
%lead(data=have, out=have_lead, var=demand, by=zone);
data want;
set have_lead;
by zone time;
lag1_demand = lag(demand);
/* Calculate the denominator and do not set lags for the first value of zone */
if(first.zone) then do;
n = 1;
lag1_demand = .;
end;
else n = 3 - nmiss(lag1_demand, lead1_demand);
avg = sum(demand, lag1_demand, lead1_demand)/n;
drop lag1_demand lead1_demand n;
run;
```

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.