BookmarkSubscribeRSS Feed
gzr2mz39
Quartz | Level 8
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.
3 REPLIES 3
LinusH
Tourmaline | Level 20
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
Data never sleeps
gzr2mz39
Quartz | Level 8
Looks good. Thank you.
Peter_C
Rhodochrosite | Level 12
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 3 replies
  • 712 views
  • 0 likes
  • 3 in conversation