topic addition and subtraction in SAS Programming

addition and subtraction

Sanchit_goyal — Thu, 24 Sep 2020 08:15:00 GMT

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.

Re: addition and subtraction

andreas_lds — Thu, 24 Sep 2020 08:50:37 GMT

@Sanchit_goyal wrote:

[...]

I want output to be shown as

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

70? Is this a typo?

Re: addition and subtraction

andreas_lds — Thu, 24 Sep 2020 08:57:41 GMT

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;

Re: addition and subtraction

Sanchit_goyal — Thu, 24 Sep 2020 08:58:03 GMT

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!

Re: addition and subtraction

s_lassen — Thu, 24 Sep 2020 09:36:13 GMT

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.

Re: addition and subtraction

andreas_lds — Thu, 24 Sep 2020 09:42:59 GMT

100 - 20 = 80

80 - 30 = 50

Re: addition and subtraction

Kurt_Bremser — Thu, 24 Sep 2020 13:17:48 GMT

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

Re: addition and subtraction

Sanchit_goyal — Thu, 24 Sep 2020 13:38:07 GMT

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