topic Re: Why my row totals doesn't sum up? in SAS Programming

Why my row totals doesn't sum up?

Malathi13 — Tue, 07 Feb 2017 14:18:59 GMT

Hi,

I have a datset with product, Date and Net sales amount for each quarter. My row totals doesn't sumup while the column totals add up to the total amount.

suppose I have a table like below

Product	Date	NetSales
Apples	20160222	400.00
Grapes	20160512	450.20
Oranges	20160812	250.30
Peaches	20160915	500.00
Plums	20161225	350.80
Bananas	20160119	160.60

I want a table like

Product	Quarter	NetSales
	Q1	Q2	Q3	Q4
Apples	100	100	150	50	400.00
Grapes	100	150	100.20	100	450.20
Oranges	50	50	50	50.30	250.30
Peaches	100	150	150	100	500.00
Plums	50	50	100	100.80	350.80
Bananas	50	25	25	60.60	160.60
TotalSales	450	525	575.20	461.70	2,111.9

I have a proc report like this:

proc report data=new spanrows missing nowd;

column product quarter netsales;

define product/group;

define quarter/across;

define netsales/sum f=comma12.;

rbreak after/summarize ul ol;

run;

My totals for the row netsales are not adding up to the correct amount while the column totals are correct. What I'm getting is something like this:

Product	Quarter	NetSales
	Q1	Q2	Q3	Q4
Apples	100	100	150	50	800.00
Grapes	100	150	100.20	100	1,500
Oranges	50	50	50	50.30	2,150
Peaches	100	150	150	100	1,500
Plums	50	50	100	100.80	3,250
Bananas	50	25	25	60.60	4,615
TotalSales	450	525	575.20	461.70	13,815

Can someone help me in understanding why my row totals doesn't match?

Thank you

Re: Why my row totals doesn't sum up?

RW9 — Tue, 07 Feb 2017 14:35:58 GMT

Well, to start with the data you presented doesn't match your proc report (date versus quarter for instance).

Personally I would always do my calculations in a datastep before, then proc report becomes quite basic.

Re: Why my row totals doesn't sum up?

BrunoMueller — Tue, 07 Feb 2017 14:50:10 GMT

When you use an variable for ACROSS, then by default the number of rows will be printed. If you want to nest an analysis variable within the across use the syntax as follows: acrossVar, analysisVar or analysisVar, acrossVar what ever suits you better.

See code sample below:

data have;
  infile cards dlm=",";
  input
    Product : $32.
    Date : anydtdte.
    NetSales : 8.
  ;
  quarter = cats("Q", put(date, qtr.));
  format date date9.;
  cards;
Apples,20160222,400.00
Grapes,20160512,450.20
Oranges,20160812,250.30
Peaches,20160915,500.00
Plums,20161225,350.80
Bananas,20160119,160.60
;

proc report data=have spanrows missing nowd;
  column product quarter netsales;
  define product/group;
  define quarter/across;
  define netsales/sum f=comma12.;
  rbreak after/summarize ul ol;
run;


proc report data=have spanrows missing nowd;
  column product quarter, netsales netsales=netSales2;
  define product/group;
  define quarter/across;
  define netsales / sum f=comma12.;
  define netsales2 /sum f=comma12.;
  rbreak after/summarize ul ol;
run;

Bruno

Re: Why my row totals doesn't sum up?

Malathi13 — Tue, 07 Feb 2017 16:57:03 GMT

Thank you so much Bruno,

It worked perfectly except the format comma12. which is not getting applied to the amount. I will figure out that but again thank you so much. It was so perfect.

Malathi

Re: Why my row totals doesn't sum up?

Malathi13 — Tue, 07 Feb 2017 18:31:39 GMT

Hi Bruno,

I tried to apply the format comma12. to the netsales and netsales2 column and I didn't get it. For some reason I'm not getting commas to the netsales amount. Can you suggest any other way to apply the format?

Thank you

Re: Why my row totals doesn't sum up?

ballardw — Tue, 07 Feb 2017 18:53:38 GMT

With example data only the column total at the bottom is large engouth to need a comma. If you ran this on other data you may need to provide a more complete example of the data or see if it is the individual values not needed the comma.

And/or show us the exact code you ran from the log. Please past it into a code box using the {i} icon.

Re: Why my row totals doesn't sum up?

Malathi13 — Tue, 07 Feb 2017 21:26:48 GMT

Hi,

from the example above, if the netsales is in 100,000s than in hundreds, for example

Product	Date	Netsales
Apples	20160212	100000
Peaches	20160514	1502500
Plums	20160620	1602430
Bananas	20160930	2502004
Grapes	20161110	450890

How do we add a comma for the the Netsales in proc report?

Thanks

Re: Why my row totals doesn't sum up?

BrunoMueller — Wed, 08 Feb 2017 15:59:15 GMT

How big are your numbers, the length of 12 for the comma format might be too short. Try with 16. The length specified in the format (comma16.) must be wide enough for the number plus the additional formatting chars like thousand seperator, decimal separator, sign, etc.

Bruno