BookmarkSubscribeRSS Feed
Davanden
Obsidian | Level 7

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.  

 

Capture.PNG

4 REPLIES 4
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller
Davanden
Obsidian | Level 7
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
Super User

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
Obsidian | Level 7
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!

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2167 views
  • 0 likes
  • 3 in conversation