Obsidian | Level 7

## Why my row totals doesn't sum up?

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

M

7 REPLIES 7
Diamond | Level 26

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

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.

SAS Super FREQ

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

Hi

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

Obsidian | Level 7

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

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

Obsidian | Level 7

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

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

M

Super User

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

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.

Obsidian | Level 7

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

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

M

SAS Super FREQ

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

Hi

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

Discussion stats
• 7 replies
• 1188 views
• 1 like
• 4 in conversation