Weighted and Unweighted Sums in Same Table

[Davanden]

I have a need to produce some tables that show the weighted and unweighted sums of a number of numerical variables, crossed by a categorical variable, with the output going to Excel.  See the table shell below.  I know that I can produce these sums separately, save them to one or more datasets, and then reshape the data before sending it to Excel.  I was wondering if there was any capability in PROC TABULATE or PROC REPORT that would allow me to do this directly.  

[PaigeMiller]

I would do this in PROC SUMMARY, which will create the weighted sums and unweighted sums, it will also create the columns that you have labelled "ALL", and then use the output data set from PROC SUMMARY as the input to PROC REPORT.

Here's an example using the data set SASHELP.CLASS

proc summary data=sashelp.class;
    class sex age;
	var weight/weight=height;
	output out=results1 sum=weighted_sum;
run;

proc summary data=sashelp.class;
    class sex age;
	var weight;
	output out=results2 sum=unweighted_sum;
run;
proc format;
    value $sf 'Z'='All';
    value agef 999='All';
run;
data results;
	merge results1 results2;
	if missing(sex) then sex='Z';
	if missing(age) then age=999;
        format sex $sf. age agef.;
run;


proc report data=results;
	columns sex age,weighted_sum age,unweighted_sum;
	define sex/group;
	define age/across;
	define weighted_sum/analysis sum;
	define unweighted_sum/analysis sum;
run;

[Davanden]

Thank you for your reply. I am already doing something similar to what you suggest, except that I am using TABULATE to produce the two sets of sums, sending the output to datasets.

[Reeza]

Create a view with a duplicate copy of the variable.

Bad example but this illustrates the concept:

data have / view=have;
set sashelp.class;
Height2 = Height;
run;

proc tabulate data=have;
class sex  name;
var height;
var height2 / weight = age;

table sex , name* (height*sum height2*sum);
run;

[Davanden]

Thank you for this. That works in principle. I have a large number of such tables to construct, with many combinations of variables. I'll have to think about whether this approach really reduces the complexity.

Incidentally, in my years of writing SAS code, I never noticed that the VAR statement had the /WEIGHT option. So I learned something useful!