Merge two rows into one if the count of one row is 1.

[Kearthana1]

If the loan count is 1 then merge to the next row. If last observation then merge with the previous row. Greatly appreciate your help in resolving this.

Here is my data:

Original Balance	Loan Count	Curr Balance	Contrib IO Balance	Contrib%	WAC	WAM
(25,000 - 50,000]	1	116,294	7,412	0.0	4.47	317
(50,000 - 75,000]	12	711,046	43,812	0.2	4.52	337
(75,000 - 100,000]	33	2,906,308	186,190	0.8	4.49	331
(100,000 - 125,000]	24	2,651,113	144,296	0.6	4.48	332
(125,000 - 150,000]	1	5,807,462	383,168	1.7	4.51	331
(150,000 - 175,000]	59	9,304,718	643,080	2.9	4.52	329
(175,000 - 200,000]	83	15,377,836	984,286	4.4	4.50	342
(200,000 - 225,000]	69	14,498,236	889,785	4.0	4.50	340
(225,000 - 250,000]	84	19,678,015	1,278,485	5.7	4.50	340
(250,000 - 275,000]	76	19,644,538	1,156,395	5.2	4.50	339
(275,000 - 300,000]	1	20,128,657	1,117,135	5.0	4.50	343

Want:

Original Balance	Loan Count	Curr Balance	Contrib IO Balance	Contrib%	WAC	WAM
(25,000 -75,000]	13	827,340	51,224	0.2	4.50	327
(75,000 - 100,000]	33	2,906,308	186,190	0.8	4.49	331
(100,000 - 125,000]	24	2,651,113	144,296	0.6	4.48	332
(125,000 - 175,000]	60	15,112,180	1,026,248	2.3	4.52	330
(175,000 - 200,000]	83	15,377,836	984,286	4.4	4.50	342
(200,000 - 225,000]	69	14,498,236	889,785	4.0	4.50	340
(225,000 - 250,000]	84	19,678,015	1,278,485	5.7	4.50	340
(250,000 - 300,000]	77	39,773,194	2,273,530	5.1	4.50	341

[PaigeMiller]

More information is requested. Please explain how the numbers are calculated when combining rows. Loan count, Curr balance and Contrib IO balance are obvious, the remaining columns it is not clear how you are getting the result.

Please provide data as working SAS data step code (instructions and examples). We do not like to work with Excel files or copy/paste from Excel or screen captures.

[yabwon]

Try this, conditional DOW-loop should do the job:

data have;
infile cards dlm="|";
input OriginalBalance :$32. LoanCount CurrBalance :comma12. ContribIOBalance :comma12.;

OB2 = scan(OriginalBalance,2,'[( -)]');
format CurrBalance ContribIOBalance dollar12.2;
cards;
(25,000 - 50,000]|1|116,294|7,412|0.0|4.47|317
(50,000 - 75,000]|12|711,046|43,812|0.2|4.52|337
(75,000 - 100,000]|33|2,906,308|186,190|0.8|4.49|331
(100,000 - 125,000]|24|2,651,113|144,296|0.6|4.48|332
(125,000 - 150,000]|1|5,807,462|383,168|1.7|4.51|331
(150,000 - 175,000]|59|9,304,718|643,080|2.9|4.52|329
(175,000 - 200,000]|83|15,377,836|984,286|4.4|4.50|342
(200,000 - 225,000]|69|14,498,236|889,785|4.0|4.50|340
(225,000 - 250,000]|84|19,678,015|1,278,485|5.7|4.50|340
(250,000 - 275,000]|76|19,644,538|1,156,395|5.2|4.50|339
(275,000 - 300,000]|1|20,128,657|1,117,135|5.0|4.50|343
;
run;
proc print;
run;

data want;

  length OriginalBalance2 $ 32;
  call missing(OriginalBalance2, n, cumB, cumIOB, cumLC);
  do n=1 by 1 until(LoanCount>1);
    set have;
    if 1=n then OB1 = scan(OriginalBalance,1,'[( -)]');;
    cumB+CurrBalance;
    cumIOB+ContribIOBalance;
    cumLC+LoanCount;
  end;

  OB2 = scan(OriginalBalance,2,'[( -)]');
  OriginalBalance2 = cats("(",OB1,"-",OB2,"]");

  drop CurrBalance ContribIOBalance LoanCount OriginalBalance OB1 OB2 n;
  rename
    cumB=CurrBalance
    cumIOB=ContribIOBalance
    cumLC=LoanCount
    OriginalBalance2=OriginalBalance
  ;
  format cumB cumIOB dollar12.2;
run;
proc print;
run;

But calculation of: Contrib%, WAC, and WAM you have to do yourself. 

Bart

[Kearthana1]

Thank you so much! The last row with loan count 1 is not getting added to the last but one row and is getting dropped. Can you please advise how to handle it?

[yabwon · Accepted solution]

Try this:

data have;
infile cards dlm="|";
input OriginalBalance :$32. LoanCount CurrBalance :comma12. ContribIOBalance :comma12.;

OB2 = scan(OriginalBalance,2,'[( -)]');
format CurrBalance ContribIOBalance dollar12.2;
cards;
(25,000 - 50,000]|1|116,294|7,412|0.0|4.47|317
(50,000 - 75,000]|12|711,046|43,812|0.2|4.52|337
(75,000 - 100,000]|33|2,906,308|186,190|0.8|4.49|331
(100,000 - 125,000]|24|2,651,113|144,296|0.6|4.48|332
(125,000 - 150,000]|1|5,807,462|383,168|1.7|4.51|331
(150,000 - 175,000]|59|9,304,718|643,080|2.9|4.52|329
(175,000 - 200,000]|83|15,377,836|984,286|4.4|4.50|342
(200,000 - 225,000]|69|14,498,236|889,785|4.0|4.50|340
(225,000 - 250,000]|84|19,678,015|1,278,485|5.7|4.50|340
(250,000 - 275,000]|76|19,644,538|1,156,395|5.2|4.50|339
(275,000 - 300,000]|1|20,128,657|1,117,135|5.0|4.50|343
;
run;
proc print;
run;


data have2;
do n=1 by 1 until(LoanCount>1 or _E_);
  set have end=_E_;
  grp++(1=n);
  grp+-(_E_=LoanCount);
  output;
end;
run;
proc print;
run;

data want;

  length OriginalBalance2 $ 32;
  call missing(OriginalBalance2, n, cumB, cumIOB, cumLC);
  do until(last.grp);
    set have2 end=_E_;
    by grp;
    if first.grp then OB1 = scan(OriginalBalance,1,'[( -)]');;
    cumB+CurrBalance;
    cumIOB+ContribIOBalance;
    cumLC+LoanCount;
  end;

  OB2 = scan(OriginalBalance,2,'[( -)]');
  OriginalBalance2 = cats("(",OB1,"-",OB2,"]");

  drop CurrBalance ContribIOBalance LoanCount OriginalBalance OB1 OB2 n grp;
  rename
    cumB=CurrBalance
    cumIOB=ContribIOBalance
    cumLC=LoanCount
    OriginalBalance2=OriginalBalance
  ;
  format cumB cumIOB dollar12.2;
run;
proc print;
run;

[Kearthana1]

Thank you so much! This works perfectly.

[Kearthana1]

If the last two rows are with loan count 1 then I see the error message:
ERROR: BY variables are not properly sorted on data set WORK.HAVE2.
Can you please help?

[ballardw]

This looks a lot like a report. If this is a report generated by SAS code you post the code?

It might be that the fix might be to update or create a custom format for the values of the original balance column.

---

@Kearthana1 wrote:

If the loan count is 1 then merge to the next row. If last observation then merge with the previous row. Greatly appreciate your help in resolving this.

Here is my data:

Original Balance	Loan Count	Curr Balance	Contrib IO Balance	Contrib%	WAC	WAM
(25,000 - 50,000]	1	116,294	7,412	0.0	4.47	317
(50,000 - 75,000]	12	711,046	43,812	0.2	4.52	337
(75,000 - 100,000]	33	2,906,308	186,190	0.8	4.49	331
(100,000 - 125,000]	24	2,651,113	144,296	0.6	4.48	332
(125,000 - 150,000]	1	5,807,462	383,168	1.7	4.51	331
(150,000 - 175,000]	59	9,304,718	643,080	2.9	4.52	329
(175,000 - 200,000]	83	15,377,836	984,286	4.4	4.50	342
(200,000 - 225,000]	69	14,498,236	889,785	4.0	4.50	340
(225,000 - 250,000]	84	19,678,015	1,278,485	5.7	4.50	340
(250,000 - 275,000]	76	19,644,538	1,156,395	5.2	4.50	339
(275,000 - 300,000]	1	20,128,657	1,117,135	5.0	4.50	343

Want:

Original Balance	Loan Count	Curr Balance	Contrib IO Balance	Contrib%	WAC	WAM
(25,000 -75,000]	13	827,340	51,224	0.2	4.50	327
(75,000 - 100,000]	33	2,906,308	186,190	0.8	4.49	331
(100,000 - 125,000]	24	2,651,113	144,296	0.6	4.48	332
(125,000 - 175,000]	60	15,112,180	1,026,248	2.3	4.52	330
(175,000 - 200,000]	83	15,377,836	984,286	4.4	4.50	342
(200,000 - 225,000]	69	14,498,236	889,785	4.0	4.50	340
(225,000 - 250,000]	84	19,678,015	1,278,485	5.7	4.50	340
(250,000 - 300,000]	77	39,773,194	2,273,530	5.1	4.50	341

---

[Kearthana1]

Original Balance	Loan Count	Curr Balance	Contrib IO Balance	Contrib%	WAC	WAM
(25,000 - 50,000]	1	116,294	7,412	0.0	4.47	317
(50,000 - 75,000]	12	711,046	43,812	0.2	4.52	337
(75,000 - 100,000]	33	2,906,308	186,190	0.8	4.49	331
(100,000 - 125,000]	24	2,651,113	144,296	0.6	4.48	332
(125,000 - 150,000]	1	5,807,462	383,168	1.7	4.51	331
(150,000 - 175,000]	59	9,304,718	643,080	2.9	4.52	329
(175,000 - 200,000]	83	15,377,836	984,286	4.4	4.50	342
(200,000 - 225,000]	69	14,498,236	889,785	4.0	4.50	340
(225,000 - 250,000]	84	19,678,015	1,278,485	5.7	4.50	340
(250,000 - 275,000]	1	19,644,538	1,156,395	5.2	4.50	339
(275,000 - 300,000]	1	20,128,657	1,117,135	5.0	4.50	343

If the last two rows are 1 then I see grp not sorted properly error. Also is it possible to merge the last two rows with the 225,000 - 250,000 bucket?