sas Proc expand procedure

Question

I am trying to calculate 3 months moving average of the following data by Product by country( I only have two country variables here). Is there a way to do so?

Here is the sales table I have:

Date    Product  Country   Sales
201101  Sofa     US        100
201102  Sofa     US        200
201103  Sofa     US        250
201104  Sofa     US        300
201101  Sofa     CA        250
201102  Sofa     CA        300
201103  Sofa     CA        250
201104  Sofa     CA        300
201101  Chair    US        300
201102  Chair    US        300
201103  Chair    US        300
201104  Chair    US        300
201101  Chair    CA        300
201102  Chair    CA        300
201103  Chair    CA        300
201104  Chair    CA        300

I tried something like the following, but moving average is only calculated by country. Is there a way I can have it calculated by country, by product? Any ideas will be appreciated. thanks:)

PROC SORT DATA=Sales;
BY Country Product Date;
RUN;

PROC EXPAND DATA=Sales out =ma;
By Country Product;
CONVERT Value=Value_ma/transformin=(setmiss 0) transformout=(movave 3);
run;

i am not sure about the 3 months average, because your table provides 4 months and i dont know how the 3 month-avg should be calculated here, but you could use proc sql with avg and group by to get an total average over all months by product and country and then scale it in some way to 3 months, or maybe build something with proc means, but i guess you want something else here? — kl78
Thanks Kl78. I am plotting a trendline to show the 3 month moving Avg of sales by product by country. So the 1st and 2nd month has no moving avg. The 3rd month moving avg is based on the first 3 months. Then the 4th months moving Avg is based on 2nd, 3rd and 4th. and so on. — lisa
hm, ok. dont know much about proc expand, but you can make one column by combining product and country and then use the by only on the new column, like copr=catx("_",Product,country);, then group by copr and if result is ok, remove copr when displaying data. — kl78

kl78 kl78 · Accepted Answer · 2015-10-07T15:17:31

after my comment i tested a bit, i guess concating product and country gives the result you are looking for (i hope i still did not understood something wrong):

data have;
input Date $ Product $ Country $ Sales ; 
datalines; 
201101  Sofa     US        100 
201102  Sofa     US        200 
201103  Sofa     US        250 
201104  Sofa     US        300 
201101  Sofa     CA        250 
201102  Sofa     CA        300 
201103  Sofa     CA        250 
201104  Sofa     CA        300 
201101  Chair    US        300 
201102  Chair    US        300 
201103  Chair    US        300 
201104  Chair    US        300 
201101  Chair    CA        300 
201102  Chair    CA        300 
201103  Chair    CA        300 
201104  Chair    CA        300 
; 
run;

data have ;
set have; 
copr=catx("_",Product,country); 
run; 
PROC SORT DATA=have; 
BY copr Date; 
RUN;

PROC EXPAND DATA=have out =ma  ; 
 By copr; 
CONVERT sales=average /    transformin=(setmiss 0) transformout=(movave 3); 
run;

proc print data=ma; 
 var date product country average; 
where time > 1; 
run;

result:

sas Proc expand procedure

1 Answers