BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bluetail
Calcite | Level 5

                    

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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;

View solution in original post

7 REPLIES 7
art297
Opal | Level 21

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
Calcite | Level 5

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
Opal | Level 21

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
Calcite | Level 5

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
Opal | Level 21

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
Calcite | Level 5

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
Super User

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1350 views
  • 3 likes
  • 3 in conversation