Need Solution

[Guptashwe]

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

[DJongman]

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;

[mkeintz]

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;