addition and subtraction

[Sanchit_goyal]

I have a data set test as shown below:

Data test;
input brand $ loan;
datalines;
mini    100
mini     20
mini     30
toyota  120
toyota  10
toyota  20
toyota  12
toyota  34
audi   320
audi   200
audi   80
;run;

I want output to be shown as 

mini  70 (100 - 20 - 30 )
mini 20
mini 30
toyota  44 (120 - 10 - 20 - 12- 34)
toyota 10
toyota 20
toyota 12
toyota 34
audi  40  (320 - 200 - 80)
audi 200
audi 80

I only wanted to know how to come at those bold values.

Kindly help.

Thanks! in advance.

[andreas_lds]

---

@Sanchit_goyal wrote:

[...]

I want output to be shown as 

mini  70 (100 - 20 - 30 )
mini 20
mini 30

---

70? Is this a typo?

[Sanchit_goyal]

no, 70 is not a typo.

70 shows that the output should be 100 - 20 - 30. Like if you see in the Brand "mini" , its first.brand loan value is 100 . so for every first.brand loan value , output should be first value minus the rest of the value within that brand group.

similary for toyota , its first loan value should come out to be 44.

and for audi , its first loan value should come out to be 40.

Hope you understood!

[andreas_lds]

100 - 20 = 80

80 - 30 = 50

[Kurt_Bremser]

As long as we exist in this universe and use a decimal system, the result of 100 - 20 - 30 will be 50.

[Sanchit_goyal]

Yeah yeah... that was my bad, I was multitasking so thats puts in
confusion..
👍

[andreas_lds]

Try:

data want;
   length _loan 8;
   drop _loan;
   
   do _n_ = 1 by 1 until(last.brand);
      set test;
      by brand notsorted;
      
      if first.brand then _loan = loan;
      else _loan = _loan - loan;      
   end;
   
   loan = _loan;
   output;
   
   do _n_ = 1 by 1 until(last.brand);
      set test;
      by brand notsorted;
      
      if not first.brand then output;      
   end;   
run;

[s_lassen]

Here is a solution that works with the data you have shown, except that I think the "70" actually is a typo (100-20-30=50)

data want;
  set test nobs=nobs;
  by brand notsorted;
  if first.brand then do _N_=_N_+1 to nobs;
    set test(rename=(brand=_brand loan=_loan)) point=_N_;
    if _brand ne brand then leave;
    loan=loan-_loan;
    end;
  drop _:;
run;

I used NOTSORTED because your example data is grouped by BRAND but not in sort order. You may want to use a PROC SORT first instead.