Splitting of observations based on sum

[rohithverma]

I have a data like below : 

 Loanno        dramt       refno     creditamt

     .                  .              1234      1

     .                .                1234      1

    .                  .               1234     100

1234              101               .          .  

I need output like below 

dataset1:-

Loanno         dramt         refno     creditamt

     .                   .               1234       100

     .                  .                1234         1                       

1234               101              .             .           

dataset2:-

Loanno          dramt       refno     creditamt

 .                       .             1234        1

Here we can find that sum of dramt 101 is matching by the summation of two CREDITAMT observations .So the observations that makes  matching count will be seperated in one dataset and the other in another dataset should be saved.  Please help me .

Thanks in advance

[rohithverma]

I have a data like below : 

 Loanno        dramt       refno     creditamt

     .                  .              1234      1

     .                .                1234      1

    .                  .               1234     100

1234              101               .          .  

I need output like below 

dataset1:-

Loanno         dramt         refno     creditamt

     .                   .               1234       100

     .                  .                1234         1                       

1234               101              .             .           

dataset2:-

Loanno          dramt       refno     creditamt

 .                       .             1234        1

Here we can find that sum of dramt 101 is matching by the summation of two CREDITAMT observations .So the observations that makes  matching count will be seperated in one dataset and the other in another dataset should be saved.  Please help me .

Thanks in advance

[PaigeMiller]

We need to have your input data provided as working SAS data step code.

[smantha]

proc sort data=have; by  Loanno        dramt       refno     creditamt;
run;
data dataset1 dataset2;
set have;
by  Loanno        dramt       refno     creditamt;
if first.creditamt then output dataset1;
else output dataset2;
run;

I hope the above code is what you want.

[Reeza]

@rohithverma please do not post the same question multiple times. 

You need to expand your sample beyond a single situation. @smantha code works in this exact example but I suspect it won't generalize to your actual data and situation.

[rohithverma]

Yes, that's what when the sum of dramt equals to credit amount then those observations should be extracted to one dataset and the rest in other.

[smantha]

Loanno        dramt       refno     creditamt

     .                  .              1234      1

     .                .                1234      1

    .                  .               1234     100

1234              101               .          .  

I need output like below 

dataset1:-

Loanno         dramt         refno     creditamt

     .                   .               1234       100

     .                  .                1234         1                       

1234               101              .             .           

dataset2:-

Loanno          dramt       refno     creditamt

 .                       .             1234        1

Rohith, I am making an assumption that  row 3 is unique and does not have repeated values.

data have1(rename=(loanno=refno)) have2(keep=refno creditamt);

set have;

if missing (loanno) then output have1;

else output have2;

run;

proc sort data=have1 out=have1(keep=refno dramt); by refno;

proc sort data=have2 out=have1(keep=refno creditamt); by refno descending creditamt;



data want1 want2;

merge have1 have2;

by refno;

retain running_total 0;

if first.refno then running_total=creditamt;

else running_total=creditamt + running_total;

if running_total <= then output want1;

else output want2;

run; 

[smantha]

data have1(rename=(loanno=refno)) have2(keep=refno creditamt);

set have;

if missing (loanno) then output have1;

else output have2;

run;

proc sort data=have1 out=have1(keep=refno dramt); by refno;

proc sort data=have2 out=have1(keep=refno creditamt) nodupkey; by refno descending creditamt;



data want1 want2;

merge have1 have2;

by refno;

retain running_total 0;

if first.refno then running_total=creditamt;

else running_total=creditamt + running_total;

if running_total <= then output want1;

else output want2;

run;