topic Re: How to calculate total deposit using base SAS & SQL? in New SAS User

How to calculate total deposit using base SAS & SQL?

aman23 — Fri, 22 Feb 2019 18:10:43 GMT

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

Re: How to calculate total deposit using base SAS & SQL?

novinosrin — Fri, 22 Feb 2019 18:19:25 GMT


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;

Re: How to calculate total deposit using base SAS & SQL?

novinosrin — Fri, 22 Feb 2019 18:39:56 GMT

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;

Re: How to calculate total deposit using base SAS & SQL?

aman23 — Fri, 22 Feb 2019 18:54:14 GMT

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;

Re: How to calculate total deposit using base SAS & SQL?

novinosrin — Fri, 22 Feb 2019 19:05:44 GMT

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

Re: How to calculate total deposit using base SAS & SQL?

aman23 — Fri, 22 Feb 2019 19:14:44 GMT

Got it thanks a lot 🙂