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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.