topic Re: sum excluding observations in SAS Programming

sum excluding observations

gzr2mz39 — Thu, 22 Apr 2010 03:50:11 GMT

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.

Re: sum excluding observations

LinusH — Thu, 22 Apr 2010 07:51:30 GMT

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

Re: sum excluding observations

gzr2mz39 — Thu, 22 Apr 2010 16:39:30 GMT

Looks good. Thank you.

Re: sum excluding observations

Peter_C — Fri, 23 Apr 2010 09:32:15 GMT

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