topic Re: How do I sum values from result to next value in next row in SAS Enterprise Guide

How do I sum values from result to next value in next row

rodrichiez — Tue, 28 Nov 2017 19:23:35 GMT

Hi, I've got this table in SAS Enterprise Guide 6.1:

Date	DPD	Percent	n
20/11/2017	0	42.3032614
20/11/2017	1	17.86107321
20/11/2017	2	17.94882252
20/11/2017	3	8.067420863
20/11/2017	4	7.57191038
20/11/2017	7	6.247511636
21/11/2017	0	38.68761746
21/11/2017	1	21.37805108
21/11/2017	2	16.47630256
21/11/2017	3	10.9339566
21/11/2017	6	12.52407231
22/11/2017	0	46.60777228
22/11/2017	1	12.8460822
22/11/2017	2	23.98441227
22/11/2017	5	16.56173324
23/11/2017	0	54.97545337
23/11/2017	1	24.71672851
23/11/2017	4	20.30781812
24/11/2017	0	33.48435617
24/11/2017	3	66.51564383
27/11/2017	0	100

And I need to sum the value from DPD 0 with DPD 1 and the result with the value in DPD 2 and so goes on, then display the result in n column. There's one more thing:the values must be from the same date.
Should look like this:

Date	DPD	Percent	n
20/11/2017	0	42.3032614	42.30
20/11/2017	1	17.86107321	60.16
20/11/2017	2	17.94882252	78.1
20/11/2017	3	8.067420863	86.16
20/11/2017	4	7.57191038	93.73
20/11/2017	7	6.247511636	99.97
21/11/2017	0	38.68761746	38.68
21/11/2017	1	21.37805108	60.05
21/11/2017	2	16.47630256	76.52
21/11/2017	3	10.9339566	87.45
21/11/2017	6	12.52407231	99.97
22/11/2017	0	46.60777228	46.60
22/11/2017	1	12.8460822	59.44
22/11/2017	2	23.98441227	83.42
22/11/2017	5	16.56173324	99.98
23/11/2017	0	54.97545337	54.97
23/11/2017	1	24.71672851	79.68
23/11/2017	4	20.30781812	99.98
24/11/2017	0	33.48435617	33.48
24/11/2017	3	66.51564383	99.99
27/11/2017	0	100	100

Can someone help me, please.

Thanks!

Re: How do I sum values from result to next value in next row

PaigeMiller — Tue, 28 Nov 2017 19:24:27 GMT

UNTESTED CODE

Assumes data is properly sorted by date and dpd.

data want;
    set have;
    by date;
    if first.date then n=0;
    n+percent;
run;

Re: How do I sum values from result to next value in next row

Reeza — Tue, 28 Nov 2017 19:33:48 GMT

Do you have a license for SAS ETS? PROC EXPAND is a very easy solution, if you do.

Or you can use RETAIN.

data want;
set have;

by id date;
RETAIN RUNNING_TOTAL;

if first.date then RUNNING_TOTAL=percent;
else RUNNING_TOTAL=sum(RUNNING_TOTAL, percent);

run;

If you're doing this via the GUI then try the PREPARE TIME SERIES data task.

Re: How do I sum values from result to next value in next row

rodrichiez — Tue, 28 Nov 2017 19:49:21 GMT

Thank you very much!