Hello everybody;
I want to calculate a division which the nominator and denominator aren't in same rows. Please consider the table below:
table 1:
name | price | date | Rank | DP_nu | DP_nu_sum | DP_de_sum | RESULT |
A | 1 | 4/2/2008 | 1 | ||||
A | 2 | 4/2/2008 | 1 | 1 | |||
A | 2 | 4/7/2008 | 1 | 0 | |||
A | 2 | 4/17/2008 | 1 | 0 | |||
A | 3 | 4/2/2008 | 1 | 1 | |||
A | 3 | 4/7/2008 | 1 | 0 | 2 | 0.4 | |
A | 4 | 4/2/2008 | 2 | ||||
A | 4 | 4/2/2008 | 2 | 0 | |||
A | 4 | 4/7/2008 | 2 | 0 | |||
A | 5 | 4/2/2008 | 2 | 1 | |||
A | 6 | 4/2/2008 | 2 | 1 | 2 | 0.4 | |
A | 7 | 4/2/2008 | 3 | ||||
A | 7 | 4/7/2008 | 3 | 0 | |||
A | 8 | 4/7/2008 | 3 | 1 | |||
A | 8 | 4/17/2008 | 3 | 0 | 1 | 5 | 0.2 |
B | 1 | 7/22/2008 | 1 | ||||
B | 1 | 10/14/2008 | 1 | 0 | |||
B | 2 | 7/22/2008 | 1 | 1 | |||
B | 2 | 10/14/2008 | 1 | 0 | |||
B | 3 | 10/14/2008 | 1 | 1 | 2 | 0.4 | |
B | 4 | 7/22/2008 | 2 | ||||
B | 4 | 7/22/2008 | 2 | 0 | |||
B | 5 | 7/22/2008 | 2 | 1 | |||
B | 5 | 10/14/2008 | 2 | 0 | |||
B | 6 | 10/14/2008 | 2 | 1 | 2 | 0.4 | |
B | 7 | 7/22/2008 | 3 | ||||
B | 7 | 10/14/2008 | 3 | 0 | |||
B | 7 | 10/14/2008 | 3 | 0 | |||
B | 7 | 10/14/2008 | 3 | 0 | |||
B | 8 | 7/22/2008 | 3 | 1 | |||
B | 8 | 10/14/2008 | 3 | 0 | 1 | 5 | 0.2 |
I want to create the RESULT variable which is shown in the table 1 as the last column. RESULT variable is the (DP_nu_sum / DP_den_sum) for each name.
How can I do that?
Thanks in advance.
Good morning, Try this:
data have;
infile datalines truncover;
input name $ price date :mmddyy10. Rank DP_nu DP_nu_sum DP_de_sum ;
format date mmddyy10.;
datalines;
A 1 4/2/2008 1
A 2 4/2/2008 1 1
A 2 4/7/2008 1 0
A 2 4/17/2008 1 0
A 3 4/2/2008 1 1
A 3 4/7/2008 1 0 2
A 4 4/2/2008 2
A 4 4/2/2008 2 0
A 4 4/7/2008 2 0
A 5 4/2/2008 2 1
A 6 4/2/2008 2 1 2
A 7 4/2/2008 3
A 7 4/7/2008 3 0
A 8 4/7/2008 3 1
A 8 4/17/2008 3 0 1 5
B 1 7/22/2008 1
B 1 10/14/2008 1 0
B 2 7/22/2008 1 1
B 2 10/14/2008 1 0
B 3 10/14/2008 1 1 2
B 4 7/22/2008 2
B 4 7/22/2008 2 0
B 5 7/22/2008 2 1
B 5 10/14/2008 2 0
B 6 10/14/2008 2 1 2
B 7 7/22/2008 3
B 7 10/14/2008 3 0
B 7 10/14/2008 3 0
B 7 10/14/2008 3 0
B 8 7/22/2008 3 1
B 8 10/14/2008 3 0 1 5
;
proc sql;
create table want as
select *, (DP_nu_sum / max(DP_de_sum)) as result
from have
group by name
order by name, price;
quit;
Good morning, Try this:
data have;
infile datalines truncover;
input name $ price date :mmddyy10. Rank DP_nu DP_nu_sum DP_de_sum ;
format date mmddyy10.;
datalines;
A 1 4/2/2008 1
A 2 4/2/2008 1 1
A 2 4/7/2008 1 0
A 2 4/17/2008 1 0
A 3 4/2/2008 1 1
A 3 4/7/2008 1 0 2
A 4 4/2/2008 2
A 4 4/2/2008 2 0
A 4 4/7/2008 2 0
A 5 4/2/2008 2 1
A 6 4/2/2008 2 1 2
A 7 4/2/2008 3
A 7 4/7/2008 3 0
A 8 4/7/2008 3 1
A 8 4/17/2008 3 0 1 5
B 1 7/22/2008 1
B 1 10/14/2008 1 0
B 2 7/22/2008 1 1
B 2 10/14/2008 1 0
B 3 10/14/2008 1 1 2
B 4 7/22/2008 2
B 4 7/22/2008 2 0
B 5 7/22/2008 2 1
B 5 10/14/2008 2 0
B 6 10/14/2008 2 1 2
B 7 7/22/2008 3
B 7 10/14/2008 3 0
B 7 10/14/2008 3 0
B 7 10/14/2008 3 0
B 8 7/22/2008 3 1
B 8 10/14/2008 3 0 1 5
;
proc sql;
create table want as
select *, (DP_nu_sum / max(DP_de_sum)) as result
from have
group by name
order by name, price;
quit;
How did you get such a messed up file?
Looks like the denominator is unique for each NAME, so just remerge it back onto the data.
data want ;
merge have (drop=dp_de_sum)
have (keep=name dp_de_sum where=(not missing(dp_de_sum)))
;
by name ;
result=divide(dp_nu_sum,dp_de_sum);
run;
data text;
input @1 name $1. @5 price 1. @8 date @21 Rank 1. @25 DP_nu 1. @29 DP_nu_sum 1. @33 DP_de_sum 1.;
informat date mmddyy.;
format date mmddyys10.;
result=DP_nu_sum /DP_de_sum;
datalines;
A 1 4/2/2008 1
A 2 4/2/2008 1 1
A 2 4/7/2008 1 0
A 2 4/17/2008 1 0
A 3 4/2/2008 1 1
A 3 4/7/2008 1 0 2
A 4 4/2/2008 2
A 4 4/2/2008 2 0
A 4 4/7/2008 2 0
A 5 4/2/2008 2 1
A 6 4/2/2008 2 1 2
A 7 4/2/2008 3
A 7 4/7/2008 3 0
A 8 4/7/2008 3 1
A 8 4/17/2008 3 0 1 5
B 1 7/22/2008 1
B 1 10/14/2008 1 0
B 2 7/22/2008 1 1
B 2 10/14/2008 1 0
B 3 10/14/2008 1 1 2
B 4 7/22/2008 2
B 4 7/22/2008 2 0
B 5 7/22/2008 2 1
B 5 10/14/2008 2 0
B 6 10/14/2008 2 1 2
B 7 7/22/2008 3
B 7 10/14/2008 3 0
B 7 10/14/2008 3 0
B 7 10/14/2008 3 0
B 8 7/22/2008 3 1
B 8 10/14/2008 3 0 1 5
;
run;
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.