How about this?
data have;
input Date :ddmmyy10. Units_Sold Brand $ Year;
format Date ddmmyy10.;
cards;
18/03/2010 5 A 2010
12/04/2010 2 A 2010
22/05/2010 1 A 2010
25/05/2010 7 A 2010
11/08/2011 5 A 2011
12/07/2010 2 B 2010
22/10/2010 1 B 2010
05/05/2011 7 B 2011
;
proc sql;
create table startdates as
select brand, min(date) as startd
from have
group by brand;
create table want as
select a.brand, intck('year', b.startd, a.date, 'c')+1 as YearNumber,
catx(' ', put(intnx('year', b.startd, calculated YearNumber-1, 's'), ddmmyy10.), '-',
put(intnx('year', b.startd, calculated YearNumber, 's')-1, ddmmyy10.)) as Period length=23,
sum(units_sold) as Units_per_Year
from have a, startdates b
where a.brand=b.brand
group by a.brand, YearNumber, Period;
quit;
proc print data=want;
run;
Edit: Of course you can replace table STARTDATES by an inline view:
proc sql;
create table want as
select a.brand, intck('year', b.startd, a.date, 'c')+1 as YearNumber,
catx(' ', put(intnx('year', b.startd, calculated YearNumber-1, 's'), ddmmyy10.), '-',
put(intnx('year', b.startd, calculated YearNumber, 's')-1, ddmmyy10.)) as Period length=23,
sum(units_sold) as Units_per_Year
from have a, (select brand, min(date) as startd from have group by brand) b
where a.brand=b.brand
group by a.brand, YearNumber, Period;
quit;
... View more
