BookmarkSubscribeRSS Feed
Malathi13
Obsidian | Level 7

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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

BrunoMueller
SAS Super FREQ

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

Malathi13
Obsidian | Level 7

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

Malathi13
Obsidian | Level 7

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

ballardw
Super User

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.

Malathi13
Obsidian | Level 7

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

BrunoMueller
SAS Super FREQ

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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