Computing average value past 6 months by id

[Giuse_Cava95]

Hi all, I wanted to find the average for the past six months for every ndg (a sort of id), i have data sample like this:

data	ndg	value
201601	1	7
201601	2	2
201601	3	3
201602	1	7
201602	2	10
201602	3	5
201603	1	9
201603	2	2
201603	3	5
201604	1	2
201604	2	1
201604	3	5
201605	1	8
201605	2	9
201605	3	8
201606	1	8
201606	2	4
201606	3	1
201607	1	3
201607	2	9
201607	3	4
201608	1	2
201608	2	2
201608	3	10
201609	1	8
201609	2	4
201609	3	8
201610	1	3
201610	2	9
201610	3	2
201611	1	4
201611	2	2
201611	3	1
201612	1	1
201612	2	2
201612	3	7
201701	1	5
201701	2	9
201701	3	9
201702	1	3
201702	2	10
201702	3	3
201703	1	4
201703	2	9
201703	3	8
201704	1	3
201704	2	2
201704	3	5
201705	1	9
201705	2	5
201705	3	6
201706	1	3
201706	2	1
201706	3	9
201707	1	1
201707	2	4
201707	3	9
201708	1	8
201708	2	6
201708	3	6
201709	1	7
201709	2	2
201709	3	9
201710	1	2
201710	2	10
201710	3	1
201711	1	6
201711	2	7
201711	3	6
201712	1	4
201712	2	1
201712	3	1

I tried using this code, but i can't group by ndgs.

proc sql;

create table want as
select h1.data format=yymmn6.,h1.ndg, mean(h2.value) as Value_avg,
count(*) as months

from work.test as h1 inner join work.test as h2 on
h2.data between intnx("month",h1.data,-6,"beginning") and
intnx("month",h1.data,-1,"beginning")

group by h1.data,h1.ndg;
select * from want;

quit;

Could you help me? Thank you all.

[whymath · Accepted solution]

you just need add one more join condition: h1.ndg=h2.ndg

[Giuse_Cava95]

Really thank you

[PaigeMiller]

FYI

PROC EXPAND can do this as well, without the hassle of getting SQL to work properly.

proc sort data=have;
    by ndg month;
run;
proc expand data=have out=want;
    by ndg;
    convert value=value_6_month_avg/transformout = (movave 6);
run;