conditional sum in proc report

[Ronein]

Hello

Is it possible to calculate conditional sum in proc report?

It means that I want to calculate sum for different populations.

Please see the example by proc sql.

The target is to create the same output via proc report.

Data tbl;
input ID  Ind1 $ Ind2 $   y;
cards;
1 a T1 10
2 a T1 20
3 a T1 30
4 a T2 40
5 a T2 50
6 a T3 60
7 a T4 70
8 b T1 80
9 b T1 90
10 b T2 100
11 b T3 110
;
Run;

PROC SQL;
	create table Result  as
	select sum(case when Ind1='a'  and Ind2='T1'  then y else 0 end) as Sum_Y_a_T1,
		   sum(case when Ind1='a'  and Ind2='T2'  then y else 0 end) as Sum_Y_a_T2,
		   sum(case when Ind1='a'  and Ind2 not in('T1' ,'T2') then y else 0 end) as Sum_Y_a_No_T1_No_T2,
		   sum(case when Ind1='b'  and Ind2='T1'  then y else 0 end) as Sum_Y_b_T1,
		   sum(case when Ind1='b'  and Ind2='T2'  then y else 0 end) as Sum_Y_b_T2,
		   sum(case when Ind1='b'  and Ind2 not in('T1' ,'T2') then y else 0 end) as Sum_Y_b_No_T1_No_T2
	from tbl
;
QUIT;

[RW9]

Per your other question on this, use a procedure or datastep deisgned to manupulate data.  A reporting procedure is not a replace for such things.

[Ronein]

I want to be sure that Proc Report doesn't have ability to calculate conditional sum.

[PaigeMiller]

First, I strongly recommend the approach of getting your data into its final form and doing necessary calculations (PROC SUMMARY, PROC SQL, DATA step) before running PROC REPORT.

In PROC REPORT, you could use a WEIGHT option in the DEFINE statement to achieve your conditional sum, but this requires you performing a data step to create the weights and assign them properly, and as long as you are going to do that, you might as well go back to my first paragraph above and use the a tool that is appropriate for your conditional sums.

[ballardw]

---

@Ronein wrote:

I want to be sure that Proc Report doesn't have ability to calculate conditional sum.

---

Yes it does. Look up the definition of Compute blocks in Proc Report syntax.

However does it do what you want easily and robustly, maybe, maybe not.

I have used SAS for pushing 30 years and seldom use Proc Report to do calculations across columns because as soon as my user sees the initial report they start asking about "can you add ...". Which often ends up adding a great deal of work or has issues with the actual data such as missing values for groups and such that get to be a pain for dealing with calculated columns. Most of time I actually use Proc Report with calculated columns is actually more to make a demonstration table as my users may not quite know what they want for a final result and prototyping to a small extent with dummy data lets them explore layout options. Then when we agree on appearance I dig into the nuts and bolts of doing so with the actual data.

So I usually pre-summarize data where I have better controls than Proc reports (when you get to referencing 15 or 20 calculated columns or dealing with differing numbers of across values you'll know what I mean). Then when I have the values I can use Proc Report or Proc Tabulate to display the results in a "pretty" fashion.