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.
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
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.