topic Re: Proc Report : Removing one column for the rbreak summarize in ODS and Base Reporting

Proc Report : Removing one column for the rbreak summarize

morglum — Wed, 01 Jun 2016 17:20:45 GMT

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!

Re: Proc Report : Removing one column for the rbreak summarize

ballardw — Wed, 01 Jun 2016 17:31:11 GMT

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.

Re: Proc Report : Removing one column for the rbreak summarize

morglum — Wed, 01 Jun 2016 17:56:21 GMT

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;

Re: Proc Report : Removing one column for the rbreak summarize

Cynthia_sas — Wed, 01 Jun 2016 21:27:47 GMT

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;

Re: Proc Report : Removing one column for the rbreak summarize

morglum — Thu, 02 Jun 2016 14:22:39 GMT

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

The first one is exactly what I wanted.