Lapis Lazuli | Level 10

## How to calculate a division with missing data?

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

## Re: How to calculate a division with missing data?

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;

3 REPLIES 3
Tourmaline | Level 20

## Re: How to calculate a division with missing data?

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;

Super User

## Re: How to calculate a division with missing data?

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;``````
Quartz | Level 8

## Re: How to calculate a division with missing data?

``````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;``````
Discussion stats
• 3 replies
• 956 views
• 1 like
• 4 in conversation