Proc Report : Removing one column for the rbreak summarize

[morglum]

Hi everyone,

I am building a Proc Report and I hit a snag.

Let's say I have 12 lines (one per month), and 2 metrics : a flow (sales) and a stock (debt at the end of the month).

I add a total line using rbreak /summarize.

This generates a total line.  Total sales for the year makes sense.  Total monthly debt doesnt.  How do I remove the total monthly debt from the summary?

thanks!

[ballardw]

It helps to show the code you are using as there may be interactions between various DEFINE and COMPUTE blocks and so we have some idea what role each variable is playing. Otherwise we are guessing a whole lot.

[morglum]

For example, below I want to sum the debt at the end of the month for all users, but I dont want to sum the debt across different months;

data test;
do month= 1 to 12;
	do user = 1 to 3;
	sales = round(5*ranuni(1));
	debt_at_end_of_month = round(10*ranuni(1));
	output;
	end;
end;
run;

proc report data=test;
column 
	month 
	sales
	debt_at_end_of_month;
define month / group;
define sales /analysis sum;
define debt_at_end_of_month /analysis sum;
rbreak after /summarize;
run;
quit;

[Cynthia_sas · Accepted solution]

Hi:

  I'm not sure I quite understand what you're asking in your first example, there is only the summary at the end of the report, not after every month. But if you put USER variable on the report, then you could break after every month. But at any rate, you can suppress what you see by using the COMPUTE block.

  I like to see the same numbers every time I run a report if I'm testing so I just changed the fake data step.

cynthia

data test;
  infile datalines dlm=',' dsd;
  input month user sales debt_at_end_of_month;
return;
datalines;
1,1,1,10
1,2,2,3
1,3,5,10
2,1,3,5
2,2,0,1
2,3,4,5
3,1,4,1
3,2,5,3
3,3,1,7
4,1,5,2
4,2,3,4
4,3,3,3
5,1,2,8
5,2,3,6
5,3,3,4
6,1,4,5
6,2,5,9
6,3,3,3
7,1,2,5
7,2,3,2
7,3,1,9
8,1,1,9
8,2,5,6
8,3,0,1
9,1,3,4
9,2,1,7
9,3,2,1
10,1,2,2
10,2,3,7
10,3,2,0
11,1,3,3
11,2,0,7
11,3,5,4
12,1,5,7
12,2,1,2
12,3,1,6
;
run;
 

options missing=' ';
proc report data=test 
  style(summary)=Header;
column month sales debt_at_end_of_month;
define month / group;
define sales /analysis sum;
define debt_at_end_of_month /analysis sum;
compute after;
  debt_at_end_of_month.sum = .;
endcomp;
rbreak after /summarize;
run;

proc report data=test 
  style(summary)=Header;
column month user sales debt_at_end_of_month;
define month / group;
define user /group;
define sales /analysis sum;
define debt_at_end_of_month /analysis sum;
break after month / summarize;
rbreak after /summarize;
compute after;
  debt_at_end_of_month.sum = .;
endcomp;
run;
quit;

[morglum]

I didnt know you could computer the "after" row.  thanks!

The first one is exactly what I wanted.