Why my row totals doesn't sum up?

Reply
Contributor
Posts: 46

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

Super User
Super User
Posts: 7,720

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
Posts: 706

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

Contributor
Posts: 46

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

Contributor
Posts: 46

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
Posts: 11,134

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.

Contributor
Posts: 46

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

ProductDateNetsales
Apples20160212100000
Peaches201605141502500
Plums201606201602430
Bananas201609302502004
Grapes20161110450890

 

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

 

Thanks

M

SAS Super FREQ
Posts: 706

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

 

Ask a Question
Discussion stats
  • 7 replies
  • 143 views
  • 1 like
  • 4 in conversation