Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

[ Edited ]

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

DateDPDPercentn
20/11/2017042.3032614 
20/11/2017117.86107321 
20/11/2017217.94882252 
20/11/201738.067420863 
20/11/201747.57191038 
20/11/201776.247511636 
21/11/2017038.68761746 
21/11/2017121.37805108 
21/11/2017216.47630256 
21/11/2017310.9339566 
21/11/2017612.52407231 
22/11/2017046.60777228 
22/11/2017112.8460822 
22/11/2017223.98441227 
22/11/2017516.56173324 
23/11/2017054.97545337 
23/11/2017124.71672851 
23/11/2017420.30781812 
24/11/2017033.48435617 
24/11/2017366.51564383 
27/11/20170100 

 

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:

 

DateDPDPercentn
20/11/2017042.3032614 42.30
20/11/2017117.86107321 60.16
20/11/2017217.94882252 78.1
20/11/201738.067420863 86.16
20/11/201747.57191038 93.73
20/11/201776.247511636 99.97
21/11/2017038.68761746 38.68
21/11/2017121.37805108 60.05
21/11/2017216.47630256 76.52
21/11/2017310.9339566 87.45
21/11/2017612.52407231 99.97
22/11/2017046.60777228 46.60
22/11/2017112.8460822 59.44
22/11/2017223.98441227 83.42
22/11/2017516.56173324 99.98
23/11/2017054.97545337 54.97
23/11/2017124.71672851 79.68
23/11/2017420.30781812 99.98
24/11/2017033.48435617 33.48
24/11/2017366.51564383 99.99
27/11/20170100  100

 

Can someone help me, please.

 

Thanks!

 

 


Accepted Solutions
Solution
3 weeks ago
Trusted Advisor
Posts: 1,989

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

Posted in reply to rodrichiez

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;
--
Paige Miller

View solution in original post


All Replies
Solution
3 weeks ago
Trusted Advisor
Posts: 1,989

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

Posted in reply to rodrichiez

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;
--
Paige Miller
New Contributor
Posts: 3

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

Posted in reply to PaigeMiller

Thank you very much!Smiley Very Happy

Super User
Posts: 20,252

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

Posted in reply to rodrichiez

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. 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 96 views
  • 2 likes
  • 3 in conversation