how to add a rolling date?

[bluetail]

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

[art297]

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;

[bluetail]

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;

[art297 · Accepted solution]

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;

[bluetail]

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...

[art297]

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.

[bluetail]

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 ♥

[Ksharp]

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