Contributor
Posts: 20

# How to find Average if by using EG

Dear all

I really need you all guys  help. I have data set like below;

 Contract Contract_Date Contract_Amount 1 01-Jan-13 10000 2 02-Jan-13 20000 3 01-Feb-14 30000 4 02-Feb-14 25000 5 10-Jun-14 22000 6 11-Jun-14 22000

and I want to create a new column name "AVG_Contract_AMT". I want to add the average of Contract_Amount which Contract_Date <= each row Contract_date like this

 Contract Contract_Date Contract_Amount AVG_Contract_AMT 1 01-Jan-13 10000 10000 2 02-Jan-13 20000 15000 3 01-Feb-14 30000 20000 4 02-Feb-14 25000 21250 5 10-Jun-14 22000 21400 6 11-Jun-14 22000 21500

I have never do any coding in EG like this before. Is there any examples?

And In case I want to find Z Value ((X-AVG/SD))  for each contract,but AVG of data and SD must have contract_Date <= each row contract date like below

 Contract Contract_Date Contract_Amount AVG_Contract_AMT SD Z 1 01-Jan-13 10000 10000 100 0 2 02-Jan-13 20000 15000 100 50 3 01-Feb-14 30000 20000 100 100 4 02-Feb-14 25000 21250 79.05694 47.43 5 10-Jun-14 22000 21400 66.3325 9.045 6 11-Jun-14 22000 21500 60.55301 8.257

Please suggest the solution for me. Thank you in advance.

Best Regards,

Ros

Super User
Posts: 10,557

## Re: How to find Average if by using EG

Posted in reply to cmajorros

proc sort data=have;

by contract_date;

run;

data want (drop=sum_amount);

set have;

retain sum_amount 0;

sum_amount + contract_amount;

avg_contract_amt = sum_amount / _N_;

run;

From there you can then do the other calculations

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,849

## Re: How to find Average if by using EG

Posted in reply to cmajorros

What is C ? and SD means STD ?

```data have;
input Contract     Contract_Date : date11.     Contract_Amount     ;
format      Contract_Date date9.;
cards;
1     01-Jan-13     10000
2     02-Jan-13     20000
3     01-Feb-14     30000
4     02-Feb-14     25000
5     10-Jun-14     22000
6     11-Jun-14     22000
;
run;
proc sql;
create table want as
select *,(select avg(Contract_Amount) from have where Contract_Date le a.Contract_Date) as AVG_Contract_AMT,
(select std(Contract_Amount) from have where Contract_Date le a.Contract_Date) as SD
from have as a ;
quit;

```

Xia Keshan

Discussion stats
• 2 replies
• 948 views
• 0 likes
• 3 in conversation