topic How to do row by row comparison and sum if row>=row in SAS Programming

How to do row by row comparison and sum if row>=row

chithra — Thu, 29 Nov 2018 06:17:51 GMT

Dear All,

I have a dataset like below with 300000 observations;

The calculation is ;

if A>=A then sum(B) [if observation in A column is greater than equal to A column Sum the values in B column]

Finally, TOTAL=sum(all B)

Input		output
A	B	A	B
1	100	1	1209
1	101	2	1008
2	102	3	706
2	200	4	501
3	205	TOTAL	3424
4	501

Could anyone please help me here.?

Thanks,

Chithra

Re: How to do row by row comparison and sum if row>=row

DanielLangley — Thu, 29 Nov 2018 07:54:10 GMT

This code is not the full solution as it misses out the final Total. There is probably also a more efficient way of doing this. However I hope the below code helps.

data have;
input a b;
cards;
1 100	 
1 101
2 102
2 200
3 205
4 501
;
run;

proc sort data=have
out=haveSrt;
by descending a;
run;

data want(drop=originalB);
set haveSrt(rename=(b=originalB));
by descending a;
retain b 0;
b + originalB;
if last.a;
run;

proc sort data=want;
by a;
run;

Re: How to do row by row comparison and sum if row>=row

learsaas — Thu, 29 Nov 2018 08:20:24 GMT

Re: How to do row by row comparison and sum if row>=row

chithra — Thu, 29 Nov 2018 11:36:04 GMT

Thanks a lot, this helps

Re: How to do row by row comparison and sum if row>=row

chithra — Thu, 29 Nov 2018 11:37:28 GMT

Thanks a lot, this helps.

Re: How to do row by row comparison and sum if row>=row

chithra — Thu, 29 Nov 2018 12:59:16 GMT

one more thing, is it possible to do the same with respect to one more column?

data have;

input a1 $9. a b;

cards;

IME_TOTAL 11 100

IME_PREM 11 101

IME_TOTAL 2 102

IME_ANNUI 2 200

IME_TOTAL 3 205

IME_ANNUI 4 501

;

run;

output looks like :

Input			output
A1	A	B	A1	A	B
IME_TOTAL	11	100	IME_TOTAL	11	100
IME_PREM	11	101	IME_TOTAL	3	407
IME_TOTAL	3	102	IME_PREM	11	101
IME_ANNUI	2	200	IME_ANNUI	2	701
IME_TOTAL	3	205	IME_ANNUI	4	501
IME_ANNUI	4	501		TOTAL	1810

Re: How to do row by row comparison and sum if row>=row

DanielLangley — Thu, 29 Nov 2018 13:12:00 GMT

What are your rules with the additional Column?

I also cannot work out what sorting method you want for the Output, please could you clarify.

Thanks

Re: How to do row by row comparison and sum if row>=row

chithra — Thu, 29 Nov 2018 13:29:49 GMT

if a1=ime_total then do the calculation as done before, the sam if a1=ime_prem and ime_annui.

ie; for a1=ime_total, when a>=a sum(b). like for all a1.

in this example for a1=ime_total we get 2 rows :

11>=11 --> one row

3>=3(2 rows),3>=11 sum together--> one row

hope you will get this.

Re: How to do row by row comparison and sum if row>=row

DanielLangley — Fri, 30 Nov 2018 07:00:37 GMT


data have;
input a1 $9. a b;
cards;
IME_TOTAL 11 100
IME_PREM 11 101
IME_TOTAL 2 102
IME_ANNUI 2 200
IME_TOTAL 3 205
IME_ANNUI 4 501
;
run;

proc sort data=have
out=haveSrt;
by a1 descending a;
run;

data want(drop=originalB);
set haveSrt(rename=(b=originalB));
by a1 descending a;
retain b 0;
if first.a1 then b=0;
b + originalB;
if last.a;
run;

proc sort data=want;
by a;
run;

Hello. This seems to match your rules but not your output. (for the three column method).

Could you have a look at it and if necessary tell me exactly which rows each row of your output should be adding up.

Apologies for this.

Re: How to do row by row comparison and sum if row>=row

chithra — Fri, 30 Nov 2018 08:42:30 GMT

But, this actually helps me.

Thanks