per year calculation

[makarand]

Thanks guys for solving https://communities.sas.com/t5/Base-SAS-Programming/Start-Period-and-End-Period-Calculation/m-p/2868...

refered from this question.

how can i limit the calulation to just a year

ie i want to find out average of Cust_Left for that year only for each month.

Output I want is <Avg_Left >

for 11-Apr 22

for 11-May > avg(apr+may)

for 11-Dec > avg(apr+ may+ .. + dec)

for 12-Jan > avg(jan)

for 12-Feb > avg(jan+feb)

Month	Project	Start_Period	New_Cust	Cust_Left	Avg_Left	End_Period
11-Apr	a	0	131	22	22	109
11-May	a	109	70	85	53.5	94
11-Jun	b	94	55	70	59	79
11-Jul	a	79	79	63	60	95
11-Aug	a	95	154	96	67.2	153
11-Sep	b	153	185	90	71	248
11-Oct	aa	248	181	103	75.571	326
11-Nov	a	326	161	114	80.375	373
11-Dec	s	373	231	97	82.222	7
12-Jan	aaaa	507	183	86	86	604
Feb-12	aa	604	86	24	55	666
Mar-12	a	666	24	20	43.333
Apr-12	s	670	20	18	37	672
May-12	a	672	18	47	39	643
Jun-12	b	643	47	74	44.833
Jul-12	a	616	74	49	45.428

How should i approach this problem any suggestion?

thanks a ton in advance 🙂

[RahulG · Accepted solution]

Data want (drop = sum_left cnt);

Retain sum_left cnt;

Set have;

If month =1 then do;

 sum_left =0; cnt=0;

End;

sum_left + cust_left;

Cnt+1;

Avg_left= sum_left/cnt;

Run;

You need to take care of extracting month value from date colum