total sales in each month for a year

[Aexor]

Hi All,

Need help in below query.

I need to get the total sum by each product id  for a particular month in year.

input 

product date     sales
a      1Jan2012   200
b      4Jan2012   300
c      2Feb2012   200
d      3Mar2012   200
d      5Mar2012  100

output is having detail of all month along with product and total sale in each month. if there is no sales then set value as missing.

Output  should look like this.
product  jan  feb mar apr may jun jul aug sep oct nov dec
a           200   .      .     .      .      .     .    .      .      .     .      . 
b           300   .      .     .      .      .     .    .      .      .     .      . 
c             .     200  .      .     .      .      .     .    .      .      .     .      
d             .       .    300

[PeterClemmensen]

data have;
input product $ date :date9. sales;
format date date9.;
datalines;
a 1Jan2012 200
b 4Jan2012 300
c 2Feb2012 200
d 3Mar2012 200
d 5Mar2012 100
;

data temp;
   set have;
   m = month(date);
run;

data want;
   s = 0;
   do until (last.m);
      set temp;
      by product m notsorted;
      array d{*} jan feb mar apr may jun jul aug sep oct nov dec;
      s + sales;
   end;

   d[m] = s;
   keep product jan -- dec;
run;

[Ksharp]

data have;
input product $ date :date9. sales;
format date date9.;
datalines;
a 1Jan2012 200
b 4Jan2012 300
c 2Feb2012 200
d 3Mar2012 200
d 5Mar2012 100
;
proc summary data=have nway;
class product date;
format date monname3.;
var sales;
output out=temp(drop=_:) sum=;
run;
proc transpose data=temp out=want(drop=_:);
by product;
id date;
var sales;
run;

[ballardw]

Just in case the actual data has more than one year in the data set I might suggest use of the YYMON7. format .

That will show column years such as 2012JAN -2012DEC, 2013JAN -2013DEC. That way you don't have to do any code for multiple year datasets.

Unless you need that output as a data set for further manipulation/analysis I would use a report proc such as Tabulate or report

data have;
input product $ date :date9. sales;
format date date9.;
datalines;
a 1Jan2012 200
b 4Jan2012 300
c 2Feb2012 200
d 3Mar2012 200
d 5Mar2012 100
;

proc tabulate data=have;
   class product date;
   format date yymon7.;
   var sales;
   tables product,
          date=' '*sales*sum=' '*f=best10.
   ;
run;


proc report data=have;
   columns product date, sales;
   define product/group;
   define date/across format=yymon7. order=data;
run;