I have a dataset with below data:
i need sum of deposit & sum of withdrawals of each employee
name | money |
Andrea | -10000 |
Mark | 149513 |
Kassidy | -5016 |
Andrea | 17500 |
Andrea | 2500 |
Jim | 100000 |
Jim | -50 |
Jim | -50 |
Jim | -50 |
Kassidy | -2013 |
Output should be like this:
name | sum of deposit | sum of withdraws |
Andrea | 20000 | 10000 |
Mark | 100000 | 150 |
Kassidy | 0 | 7029 |
Jim | 149513 | 0 |
proc sort data=have;
by name;
run;
data want;
set have;
by name;
if first.name then do;
sumofdeposit=0;
sumofwithdraw=0;
end;
sumofdeposit+(money>=0)*money;
sumofwithdraw+(money<0)*money;
if last.name;
sumofwithdraw=abs(sumofwithdraw);
keep name sum:;
run;
data have;
input name $ money;
cards;
Andrea -10000
Mark 149513
Kassidy -5016
Andrea 17500
Andrea 2500
Jim 100000
Jim -50
Jim -50
Jim -50
Kassidy -2013
;
proc sql;
create table want as
select name, sum((money>=0)*money) as sumofdeposit,abs((sum((money<0)*money))) as sumofwithdraw
from have
group by name;
quit;
proc sort data=have;
by name;
run;
data want;
set have;
by name;
if first.name then do;
sumofdeposit=0;
sumofwithdraw=0;
end;
sumofdeposit+(money>=0)*money;
sumofwithdraw+(money<0)*money;
if last.name;
sumofwithdraw=abs(sumofwithdraw);
keep name sum:;
run;
why below variables are multiplied?? can you show or explain with the help of any of the observation from data only?
(money>=0)*money;
(money<0)*money;
Just converting the values to boolean 1's and 0's based on the need .
So when
money>=0)
is true, the value would be 1 and when false the value is 0
And the same for withdrawal expression
Got it thanks a lot 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.