topic Need Solution in SAS Programming

Need Solution

Guptashwe — Mon, 07 Jan 2019 07:56:57 GMT

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

Re: Need Solution

DJongman — Mon, 07 Jan 2019 11:35:55 GMT

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;

Re: Need Solution

mkeintz — Mon, 07 Jan 2019 14:36:47 GMT

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;