turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Group totals based on 3 month interval

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-27-2017 02:43 PM

**data** test;

infile datalines;

input Year $ Month $ Dept1N Dept2N ;

return;

datalines;

2014 1Jun2014 2500 2100

2014 1Jul2014 2330 2220

2014 1Aug2014 1500 2140

2014 1Sep2014 2500 2670

2014 1Oct2014 4500 4100

2014 1Nov2014 2600 7100

;

**run**;

Output

Year | Month | Dept1N | Dept2N |

2014 | 1Jun2014 | 2500 | 2100 |

2014 | 1Jul2014 | 2330 | 2220 |

2014 | 1Aug2014 | 1500 | 2140 |

2014 | 1Sep2014 | 2500 | 2670 |

2014 | 1Oct2014 | 4500 | 4100 |

2014 | 1Nov2014 | 2600 | 7100 |

I need to add a 3 month Avg column based on the following

Dept1N(sum of the 1st 3 rows, then 2nd 3 rows...etc. Example 2500+2330+1500=6330)

Dept2N(2100+2220+2140=6460)

Add Dept1N+Dept2N (6330+6460=12790

To get my 3 month avg 6460/12790 = 50.50

Can this be best handles in proc report or here in the datastep and how??

Thus the calculation needs to evaluate every 3 months

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Q1983

06-27-2017 03:02 PM - edited 06-27-2017 03:03 PM

In a data step use the LAG function if you are sure you have no missing months. If you have missing months, a SQL query or PROC EXPAND are your best option.

Do you have missing months that you need to deal with?

If no,then the following is probably the quickest method:

`average3 = currentVar + lag(currentVar) + lag2(currentVar);`

EDIT: I suppose if this is an average you should divide by 3 or use the MEAN function.

`average3 = mean(curentVar, lag1(currentVar), lag2(currentVar));`

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Q1983

06-27-2017 04:53 PM

If my understanding of your question is correct, this should get you 80% of the way there. I don't understand the examples you gave. You listed the 3 month sum for department 2 divided by the 3 month sum for both departments and called it average? I believe this calculation is the percentage of department 2's three-month sales. Anyway, here is the code to append the static 3 month averages to the base file:

```
data test;
infile datalines;
input Year $ Month $ Dept1N Dept2N ;
return;
datalines;
2014 1Jun2014 2500 2100
2014 1Jul2014 2330 2220
2014 1Aug2014 1500 2140
2014 1Sep2014 2500 2670
2014 1Oct2014 4500 4100
2014 1Nov2014 2600 7100
;
run;
*Create identifiers for each 3 month segment;
data want;
set test;
three_month_count=ceil(_n_/3);
run;
*Calculate static three month averages for each department;
proc summary data = want nway nmiss;
class three_month_count;
var Dept1N Dept2N;
output out = want2 ( drop = _type_ _freq_ ) mean = avg_Dept1N avg_Dept2N;
run;
*Merge the three month averages back to the base file;
data append_averages;
merge want want2;
by three_month_count;
```

*Add any calculations you need here;
run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Q1983

06-27-2017 05:00 PM

Best way would be do by @Reeza suggestion.

But yu can achieve same by proc sql as shown below

proc sql; select *, sum(dept1N) as dept1_3months_total, sum(dept2N) as dept2_3months_total, calculated dept1_3months_total+ calculated dept2_3months_total as dept1_dept2total, (calculated dept1_3months_total/calculated dept1_dept2total)*100 as mothavg from (select min(month) as min_date format =date9. from test) a cross join (select * from test)b group by ceil((intck('month', min_date, month)+1)/3);