In the below picture, TABLE A has brand and then first.brand loan row contains total.
What I am want to do is create a desired table where the (first.brand row value = first row current value – following rows value ) and if its not the first.brand then value as the current value as show in the table 2.
Regards
BMW
Hi Guptashwe,
You could do the following:
data have;
infile datalines;
input id brand $ loan;
return;
datalines;
1 mini 70
2 mini 30
3 mini 20
4 mini 10
5 toyota 80
6 toyota 40
;
run;
proc sql;
create table have2 as
select id, brand, loan, sum(loan) as loan_calc
from have
group by brand;
quit;
proc sort
data= have2;
by id;
run;
data want;
set have2;
by brand;
if first.brand then
loan_calc = loan-(loan_calc-loan);
else loan_calc = loan;
run;
Given your data are grouped by brand, this is a classic double DOW task in the data step. Read a group once to calculate the needed value. Re-read the group using that value an outputting the data:
data have;
infile datalines;
input id brand $ loan;
return;
datalines;
1 mini 70
2 mini 30
3 mini 20
4 mini 10
5 toyota 80
6 toyota 40
;
run;
data want (drop=_:);
do until (last.brand);
set have;
by brand;
if _loan=. then _loan=loan;
else _loan=_loan-loan;
end;
do until (last.brand);
set have;
by brand;
if _loan^=. then loan=_loan;
output;
_loan=.;
end;
run;
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.