topic total sales in each month for a year in SAS Programming

total sales in each month for a year

Aexor — Thu, 15 Sep 2022 07:44:49 GMT

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

Re: total sales in each month for a year

PeterClemmensen — Thu, 15 Sep 2022 07:54:21 GMT

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;

Re: total sales in each month for a year

Ksharp — Thu, 15 Sep 2022 12:47:58 GMT

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;

Re: total sales in each month for a year

ballardw — Thu, 15 Sep 2022 16:34:40 GMT

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;