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!
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;
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.
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;
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;
I didnt know you could computer the "after" row. thanks!
The first one is exactly what I wanted.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: