topic how to add a rolling date? in SAS Procedures

how to add a rolling date?

bluetail — Sat, 20 Aug 2011 14:13:34 GMT

month id res monthmarch res

1/1996 a 15 ----------- . 15

2/1996 a 10 ----------- . 10

3/1996 a 7 ----------- 1/1996 7

4 /1996 a 10 ----------- 2/1996 10

5/1996 a 6 ------------ 3/1996 6

6 /1996 a 11 ----------- 4/1996 11

7/1996 a 12 ----------- 5/1996 12

8/1996 a 9 ----------- 6/1996 9

9 /1996 a 10 ----------- 7/1996 10

10/1996 a 8 ----------- 8/1996 8

11/1996 a 10 ----------- 9/1996 10

12/1996 a 10 ----------- 10/1996 10

1/1997 a 15 ----------- 11/1996 15

2/1997 a 10 ----------- 12/1996 10

3/1997 a 7 ----------- 1/1997 7

4/1997 a 10 ----------- 1/1997 10

1/1996 b 12 ------------ . 12

2/1996 b 8 ------------ . 8

3/1996 b 10 ------------ 1/1996 10

4/1996 b 8 ------------ 2/1996 8

5/1996 b 17 ------------ 3/1996 17 ;

hello. i have the following problem - i need to construct a rolling year estimate, e.g. to create a column 'monthmarch' which is 2 months after 'month'( the 1st month is 01/1996 so i want it to be rolled forward by 2 months..). this is for companies with id a,b...z which financial year starts in march rather than in january...then i'd be able to sum the data in res.

thank you so much. maybe this is a simple command but i just cant think of anything. i tried this, but it only changes months within the same year.

data have;

set have;

format month date9.;

a=(month(date)+2);

if a>12 then a=1;

month=mdy(a,1,year(date));

drop a;

run;

Maria

how to add a rolling date?

art297 — Sat, 20 Aug 2011 15:04:13 GMT

I can't test this at the moment, but the following should give you an idea of one way that you could achieve what I think you are trying to do. The following assumes that your data are already sorted by id date:

data want;

set have;

by id;

format monthmarch date9.;

retain counter holdyear;

if first.id then counter=0;

if month(date) gt 2 then holdyear=year(date);

counter+1;

if counter gt 2 or month(date) gt 2 then

monthmarch=mdy(month(date)-2),1,holdyear);

else monthmarch=mdy(month(date)+10),1,holdyear);

run;

Re: how to add a rolling date?

bluetail — Sat, 20 Aug 2011 16:08:25 GMT

reports syntax errors so far...if i solve it i'll post. ♥

data want;

set have;

by id;

retain counter holdyear;

if first.id then counter=0;

if month(date) gt 2 then holdyear=year(date);

counter=counter+1;

if counter gt 2 or month(date) gt 2 then

monthmarch=mdy(month(date)-2),1,holdyear);

else monthmarch=mdy(month(date)+10),1,holdyear);

run;

Re: how to add a rolling date?

art297 — Sat, 20 Aug 2011 17:48:33 GMT

There were two extra parenthses. Try this:

data want (drop=counter holdyear);

set have;

by id;

retain counter holdyear;

format monthmarch mmyys7.;

if first.id then counter=0;

if month(date) gt 2 then holdyear=year(date);

counter=counter+1;

if counter gt 2 or month(date) gt 2 then

monthmarch=mdy(month(date)-2,1,holdyear);

else monthmarch=mdy(month(date)+10,1,holdyear);

run;

Re: how to add a rolling date?

bluetail — Sat, 20 Aug 2011 18:28:07 GMT

reading also about time intervals in SAS.. basically what i need, is to define an interval from march to march each year for the variable 'res', and then work with it (calculate sum, mean and var within each interval). maybe i could use the INTNX function & Year.3 to shift them...

Re: how to add a rolling date?

art297 — Sat, 20 Aug 2011 21:03:52 GMT

If you only need the fiscal year in order to do what you want you could use:

yearmarch=year(intnx('year.3',date,0));

However, per your example, you will still need logic lin order NOT to include an intial fiscal year, within an id, if the data for the year doesn't begin in March.

Similarly, if you do need month and year, you could use something like:

monthmarch=intnx('year.3',intnx('month',date,-2),0,'s');

Personally, I think the original code is easier for most to understand.

Re: how to add a rolling date?

bluetail — Sun, 21 Aug 2011 10:53:42 GMT

oh art297, thanks again so much..:) i tried these this morning and both have worked. i was going mad about this - you're right im not a specialist, only using SAS as part of a project.

to this 1st command, SAS produced

NOTE: Invalid argument to function MDY at line 31 column 15. which refers to monthmarch=mdy(month(date)-2,1,holdyear);

however, despite of this, it has delivered the result alright.

I just used this simple command, yearmarch=year(intnx('year.3',date,0)) since it allows me to compute variance and sum within each year.

Maria ♥

Re: how to add a rolling date?

Ksharp — Mon, 22 Aug 2011 02:30:41 GMT

It looks like lag2() function can achieve this.

The code is not tested.

data have;

set have;

by id;

monthmarch=lag2(month);

format monthmarch mmddyy10.;

if first.id then count=0;

count+1;

if count in (1 2) then call missing(monthmarch);

run;

Ksharp