Hi all,
I have a big data set with almost 100 milion observation.
ID b c
1 4 1
1 5 1
1 3 1
1 5 2
1 5 2
2 1 2
2 4 2
2 3 1
2 4 1
for each Id I wanted to sum up the values of variable b. I did it using proc sql by group. for exampole id=1 sum= 22
my question is that i want to sum up the values of variable b for each id based on variable c. A table like below
ID b c total_sum wanted result
1 4 1 22 12
1 5 1 22 12
1 3 1 22 12
1 5 2 22 10
1 5 2 22 10
2 1 2 12 5
2 4 2 12 5
2 3 1 12 7
2 4 1 12 7
your help wil be apprecited
Ali
Like this?
data have;
input ID$ b c$;
datalines;
1 4 1
1 5 1
1 3 1
1 5 2
1 5 2
2 1 2
2 4 2
2 3 1
2 4 1
;
proc sql;
create table want as
select *
,sum(b) as wanted_sum
from have
group by ID, c
order by ID, c;
quit;
Like this?
data have;
input ID$ b c$;
datalines;
1 4 1
1 5 1
1 3 1
1 5 2
1 5 2
2 1 2
2 4 2
2 3 1
2 4 1
;
proc sql;
create table want as
select *
,sum(b) as wanted_sum
from have
group by ID, c
order by ID, c;
quit;
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.
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.