topic Re: How to find Average if by using EG in SAS Enterprise Guide

How to find Average if by using EG

cmajorros — Tue, 25 Nov 2014 04:30:42 GMT

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

Re: How to find Average if by using EG

Kurt_Bremser — Tue, 25 Nov 2014 06:48:17 GMT

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

Re: How to find Average if by using EG

Ksharp — Tue, 25 Nov 2014 12:53:04 GMT

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