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
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;
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;
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;
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;
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...
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.
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 ♥
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: