BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
5 REPLIES 5
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
art297
Opal | Level 21
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
ArtC
Rhodochrosite | Level 12
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
fangfangfu
Calcite | Level 5
It really helped. Thank you!
Stu_SAS
SAS Employee

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;

 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1779 views
  • 1 like
  • 6 in conversation