topic Re: Splitting of observations based on sum in SAS Programming

Splitting of observations based on sum

rohithverma — Tue, 16 Jun 2020 20:41:19 GMT

I have a data like below :

Loanno dramt refno creditamt

. . 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

SPLLITING OF DATA BASED ON SUM

rohithverma — Tue, 16 Jun 2020 20:39:49 GMT

I have a data like below :

Loanno dramt refno creditamt

. . 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

Thanks in advance

Re: Splitting of observations based on sum

smantha — Tue, 16 Jun 2020 21:01:09 GMT

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.

Re: SPLLITING OF DATA BASED ON SUM

PaigeMiller — Tue, 16 Jun 2020 21:01:31 GMT

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

Re: Splitting of observations based on sum

Reeza — Tue, 16 Jun 2020 21:17:57 GMT

@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.

Re: Splitting of observations based on sum

rohithverma — Tue, 16 Jun 2020 21:33:22 GMT

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.

Re: Splitting of observations based on sum

smantha — Tue, 16 Jun 2020 22:01:34 GMT

Loanno dramt refno creditamt

. . 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;

Re: Splitting of observations based on sum

smantha — Wed, 17 Jun 2020 17:11:39 GMT

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;