Desktop productivity for business analysts and programmers

How to find Average if by using EG

Reply
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;

ContractContract_DateContract_Amount
101-Jan-1310000
202-Jan-1320000
301-Feb-1430000
402-Feb-1425000
510-Jun-1422000
611-Jun-1422000

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

ContractContract_DateContract_AmountAVG_Contract_AMT
101-Jan-131000010000
202-Jan-132000015000
301-Feb-143000020000
402-Feb-142500021250
510-Jun-142200021400
611-Jun-142200021500

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

ContractContract_DateContract_AmountAVG_Contract_AMTSDZ
101-Jan-1310000100001000
202-Jan-13200001500010050
301-Feb-143000020000100100
402-Feb-14250002125079.0569447.43
510-Jun-14220002140066.33259.045
611-Jun-14220002150060.55301

8.257

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

Best Regards,

Ros

Super User
Posts: 7,431

Re: How to find Average if by using EG

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
Super User
Posts: 9,867

Re: How to find Average if by using EG

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

Ask a Question
Discussion stats
  • 2 replies
  • 603 views
  • 0 likes
  • 3 in conversation