topic Re: Merge two rows into one if the count of one row is 1. in SAS Programming

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

Kearthana1 — Tue, 17 Dec 2024 14:41:16 GMT

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

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

PaigeMiller — Tue, 17 Dec 2024 17:13:40 GMT

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.

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

yabwon — Tue, 17 Dec 2024 17:43:05 GMT

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

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

Kearthana1 — Tue, 17 Dec 2024 18:03:34 GMT

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?

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

ballardw — Tue, 17 Dec 2024 18:24:04 GMT

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

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

yabwon — Wed, 18 Dec 2024 15:12:08 GMT

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;

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

Kearthana1 — Wed, 18 Dec 2024 15:27:31 GMT

Thank you so much! This works perfectly.

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

Kearthana1 — Tue, 04 Mar 2025 15:21:58 GMT

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?

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

Kearthana1 — Wed, 05 Mar 2025 18:27:59 GMT

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?