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.
@Sanchit_goyal wrote:
[...]
I want output to be shown as
mini 70 (100 - 20 - 30 )
mini 20
mini 30
70? Is this a typo?
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!
100 - 20 = 80
80 - 30 = 50
As long as we exist in this universe and use a decimal system, the result of 100 - 20 - 30 will be 50.
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.