Solved
New Contributor
Posts: 4

# First. and Last . specific

Hi Experts,

My data looks like:

Name Amount Status

a 2000 credit

a 5000 debit

a 1000 debit

b 1000 credit

b 2000 debit

and I want the solution to look like:

Name Status Net_amount

a debit 4000

b debit 1000

Can it be achieved using first. and last., or sql,please explain the logic as well?

Accepted Solutions
Solution
‎06-01-2015 02:23 AM
Contributor
Posts: 44

## Re: First. and Last . specific

proc sql;

create table want as

select distinct

h.Name,

case when debit.debit_sum-credit.credit_sum ge 0 then 'debit'

else 'credit'

end  as Status,

debit.debit_sum-credit.credit_sum as Net_amount

from have h,(select distinct d.name, sum(d.amount) as debit_sum from have d where d.Status='debit' group by d.name ) debit,

(select distinct c.name, sum(c.amount) as credit_sum from have c where c.Status='credit' group by c.name ) credit

where h.name = debit.name

and   h.name = credit.name

;

quit;

All Replies
Super User
Posts: 8,069

## Re: First. and Last . specific

What did you try so far?

Solution
‎06-01-2015 02:23 AM
Contributor
Posts: 44

## Re: First. and Last . specific

proc sql;

create table want as

select distinct

h.Name,

case when debit.debit_sum-credit.credit_sum ge 0 then 'debit'

else 'credit'

end  as Status,

debit.debit_sum-credit.credit_sum as Net_amount

from have h,(select distinct d.name, sum(d.amount) as debit_sum from have d where d.Status='debit' group by d.name ) debit,

(select distinct c.name, sum(c.amount) as credit_sum from have c where c.Status='credit' group by c.name ) credit

where h.name = debit.name

and   h.name = credit.name

;

quit;

Super User
Posts: 9,599

## Re: First. and Last . specific

Hi,

Datastep approach.

data want;

set have;

by name;

retain net_amount;

if first.name then net_amount=0;

if status="debit" then net_amount=net_amount-amount;

if status="credit" then net_amount=net_amount+amount;

if last.name then do;

if net_amount>=0 then status="credit";

else status="debit";

output;

end;

run;

New Contributor
Posts: 4

## Re: First. and Last . specific

Thanks so much guys, it really helpful.

🔒 This topic is solved and locked.