sum excluding observations

[gzr2mz39]

I have three variables: job, count, hours.
There are more than 100 observations.
The data looks like this:

job count hours
welder1 10 100
plumber 11 110
lineman 9 120
construction 5 90
welder2 11 200

I would like to create two new variables count1 and hours1.
For each job I would like to sum count (ie count1) and sum hours (ie hours1) without including that particular job.
For example, for welder1 I would have count1=11+9+5+11 and hours1=110+120+90+200.

Thank you for your help.

[LinusH]

I think this suits SQL pretty well.
But maybe in a way it isn't usually used, by re-merging summaries to the original data.
Maybe not to be recommended if you have huge data.

proc sql;
select *, sum(count) - count as count1, sum(hours) - hours as hours1
from MyInputTable;
quit;

/Linus

[gzr2mz39]

Looks good. Thank you.

[Peter_C]

and if your data were too huge for that cartesian join, you could use proc MEANS to get grand, followed by class totals, and digest that summary:[pre]
proc means noprint data= huge_data ;
class job ;
var count hours ;
output sum= ;
run ;
data digested ;
set ;
retain g1-g2 ;
if _type_ = 0 then do;
g1= count ;
g2= hours ;
delete ;
end ;
count1 = g1 - count ;
hours1 = g2 - hours ;
keep job count1 hours1 ;
run ;[/pre]

Of course, huge is not specific, but I think the cartesian join in SQL will run out of resources before proc MEANS.

any bets?

PeterC