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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.