First. and Last . specific

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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?

Thanks in advance


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

Re: First. and Last . specific

Posted in reply to AbhinavBhatnagar

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,046

Re: First. and Last . specific

Posted in reply to AbhinavBhatnagar

What did you try so far?

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

Re: First. and Last . specific

Posted in reply to AbhinavBhatnagar

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
Super User
Posts: 7,955

Re: First. and Last . specific

Posted in reply to AbhinavBhatnagar

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 229 views
  • 3 likes
  • 4 in conversation