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

aminkarimid — Sun, 10 Dec 2017 12:00:45 GMT

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?

Thanks in advance.

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

art297 — Sun, 10 Dec 2017 17:34:51 GMT

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

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

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

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