How to calculate a division which nominator and denominator aren't in the same row?

Accepted Solution Solved
Reply
Regular Contributor
Posts: 165
Accepted Solution

How to calculate a division which nominator and denominator aren't in the same row?

Hello everybody;

I want to calculate a division which the nominator and denominator are in different rows. Please consider the table below:

table 1:

namedatetimeIntradaydailysumadjusted
A12/1/20139:30:001   
A12/1/201310:00:0023  
A12/2/201310:30:003   
A12/2/201311:00:0025  
A12/3/201310:00:002   
A12/3/201310:30:001   
A12/3/201311:00:0014  
A12/4/201311:30:001   
A12/4/201312:00:003   
A12/4/201312:30:0037  
A12/7/20139:30:001   
A12/7/201310:00:001221 
A12/8/201310:30:002  0.095238
A12/8/201311:00:0035230.142857
A12/17/201311:30:001  0.043478
A12/17/201312:00:0012230.043478
A12/18/201312:30:0022200.086957
A12/28/20139:30:001  0.05
A12/28/201310:00:0012180.05
B12/8/201310:30:002   
B12/8/201311:30:002   
B12/8/201312:30:0015  
B12/14/20139:30:001   
B12/14/201310:30:0012  
B12/15/201312:00:002   
B12/15/201312:30:0013  
B12/24/20139:30:0011  
B12/25/201310:00:002   
B12/25/201310:30:002415 
B12/26/201312:00:0022120.133333
B12/30/20139:30:003  0.25
B12/30/201310:00:002  0.166667
B12/30/201310:30:0016160.083333
B1/3/201411:00:001  0.0625
B1/3/201411:30:001  0.0625
B1/13/201412:00:0013160.0625

 

I want to create the adjusted variable which is shown in the table 1. This variable is calculated using the formula which is described below:

The intraday variable is divided by the sum variable of previous date. For instance, values '0.095238' and '0.142857'
at '12/8/2013' are 2/21 and 3/21 respectively.

 

How can I do that?

Thanks in advance.


Accepted Solutions
Solution
‎12-10-2017 12:49 PM
PROC Star
Posts: 7,787

Re: How to calculate a division which nominator and denominator aren't in the same row?

Posted in reply to aminkarimid

Here is one way:

data want (drop=last_sum have last_not_missing_sum);
  set table1;
  retain have last_not_missing_sum;
  by name;
  last_sum=lag(sum);
  if first.name then do;
    have=0;
    call missing(last_sum);
    call missing(last_not_missing_sum);
  end;
  if not missing(last_sum) then do;
    have=1;
    last_not_missing_sum=last_sum;
  end;
  if have then adjusted=Intraday/last_not_missing_sum;
run;

Art, CEO, AnalystFinder.com

 

View solution in original post


All Replies
Solution
‎12-10-2017 12:49 PM
PROC Star
Posts: 7,787

Re: How to calculate a division which nominator and denominator aren't in the same row?

Posted in reply to aminkarimid

Here is one way:

data want (drop=last_sum have last_not_missing_sum);
  set table1;
  retain have last_not_missing_sum;
  by name;
  last_sum=lag(sum);
  if first.name then do;
    have=0;
    call missing(last_sum);
    call missing(last_not_missing_sum);
  end;
  if not missing(last_sum) then do;
    have=1;
    last_not_missing_sum=last_sum;
  end;
  if have then adjusted=Intraday/last_not_missing_sum;
run;

Art, CEO, AnalystFinder.com

 

☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 123 views
  • 0 likes
  • 2 in conversation