BookmarkSubscribeRSS Feed
cmajorros
Calcite | Level 5

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

2 REPLIES 2
Kurt_Bremser
Super User

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

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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