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

Solved
Regular Contributor
Posts: 166

# 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:

 name date time Intraday daily sum adjusted A 12/1/2013 9:30:00 1 A 12/1/2013 10:00:00 2 3 A 12/2/2013 10:30:00 3 A 12/2/2013 11:00:00 2 5 A 12/3/2013 10:00:00 2 A 12/3/2013 10:30:00 1 A 12/3/2013 11:00:00 1 4 A 12/4/2013 11:30:00 1 A 12/4/2013 12:00:00 3 A 12/4/2013 12:30:00 3 7 A 12/7/2013 9:30:00 1 A 12/7/2013 10:00:00 1 2 21 A 12/8/2013 10:30:00 2 0.095238 A 12/8/2013 11:00:00 3 5 23 0.142857 A 12/17/2013 11:30:00 1 0.043478 A 12/17/2013 12:00:00 1 2 23 0.043478 A 12/18/2013 12:30:00 2 2 20 0.086957 A 12/28/2013 9:30:00 1 0.05 A 12/28/2013 10:00:00 1 2 18 0.05 B 12/8/2013 10:30:00 2 B 12/8/2013 11:30:00 2 B 12/8/2013 12:30:00 1 5 B 12/14/2013 9:30:00 1 B 12/14/2013 10:30:00 1 2 B 12/15/2013 12:00:00 2 B 12/15/2013 12:30:00 1 3 B 12/24/2013 9:30:00 1 1 B 12/25/2013 10:00:00 2 B 12/25/2013 10:30:00 2 4 15 B 12/26/2013 12:00:00 2 2 12 0.133333 B 12/30/2013 9:30:00 3 0.25 B 12/30/2013 10:00:00 2 0.166667 B 12/30/2013 10:30:00 1 6 16 0.083333 B 1/3/2014 11:00:00 1 0.0625 B 1/3/2014 11:30:00 1 0.0625 B 1/13/2014 12:00:00 1 3 16 0.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?

Accepted Solutions
Solution
‎12-10-2017 12:49 PM
Super User
Posts: 8,216

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

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;
run;
```

Art, CEO, AnalystFinder.com

All Replies
Solution
‎12-10-2017 12:49 PM
Super User
Posts: 8,216

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

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;
run;
```

Art, CEO, AnalystFinder.com

☑ This topic is solved.