topic Re: Rolling Dates in SAS Enterprise Guide

Rolling Dates

deleted_user — Mon, 22 May 2006 20:02:39 GMT

I have a question regarding Rolling dates.

I have a column of payments and another one with dates associated with the payment.

example[/u
payments date
1200 5/5/05
1000 4/1/06
500 1/3/06

How do I calculate the rolling 12 months?

Message was edited by: Magnus at May 24, 2006 2:23 PM
Message was edited by: Magnus at May 24, 2006 2:26 PM

Re: Rolling Dates

DaveShea — Mon, 12 Jun 2006 04:07:16 GMT

Hi Magnus,

I'm not sure if this is specifically an Enterprise Guide question or not (I'm new to EG but am long-in-the-tooth on SAS).

Unless this is already solved, you may need to clarify it a little. For example you refer to two columns of dates but in your example I can only see one column.

Given a date eg 05May2006, what do you mean by "the rolling 12 months", do you mean 04May2007 or something more tricky, also, are you solely an EG point'n'click user or are you happy about writing a little datastep code?

Please let us know.

Cheers.

Re: Rolling Dates

prholland — Tue, 04 Jul 2006 11:05:38 GMT

Magnus,

This is pure Base SAS, not EG, so it will have to be pasted into a Code window (although it could also be constructed in a Query task), but I think it solves your problem:

data example;
informat payments best.
date mmddyy8.
;
format date date9.;
input payments date;
datalines;
1200 5/5/05
1000 4/1/06
500 1/3/06
150 6/7/06
run;

proc sql;
create table rolling as
select a.date
,sum(b.payments) as rolling_total
from example a
,example b
where b.date between a.date and mdy(month(a.date),day(a.date),year(a.date)-1)+1
group by
a.date
;
quit;

.........Phil