Help with Using gen -- Stata returns missing instead of 0

[Jake Magel]

Hello, I am calculating a difference in percent change variable (fabq_w_chgpercent) using the following code:

capture drop fabq_w_chgpercent
by record_id: gen fabq_w_chgpercent = (fabq_w_tot_chg/fabq_w_tot[1])*100 ///
if redcap_event_name_rc==2 | redcap_event_name_rc==3

When I do this I get "missing" for cases where 0 is divided by 0 when I actually want a 0 as is the case for record_id 22 below. The help file for gen did not reveal anything. How can I code so that fabq_w_chgpercent returns 0 instead of missing?

record_id	redcap_event_name_rc	fabq_w_base	fabq_w_tot	fabq_w_tot_chg	fabq_w_chgpercent
19	3	19	16	-3	-15.78947
21	0	12	12
21	2	12	0	-12	-100
21	3	12	0	-12	-100
22	0	0	0
22	2	0	0	0
22	3	0	0	0
23	0	12	12
23	2	12	12	0	0
23	3	12	12	0	0
25	0	0	0
25	2	0	9	9
25	3	0	4	4
26	0	6	6
26	2	6	12	6	100
26	3	6	8	2	33.33333

* Example generated by -dataex-. To install: ssc install dataex
clear
input int record_id float(redcap_event_name_rc fabq_w_base fabq_w_tot fabq_w_tot_chg fabq_w_chgpercent)
 2 0 25 25   .          .
 2 1 25  .   .          .
 4 0 21 21   .          .
 5 0 15 15   .          .
 5 1 15  .   .          .
 5 2 15  5 -10 -66.666664
 5 3 15 11  -4 -26.666666
 8 0 23 23   .          .
 8 1 23  .   .          .
 8 2 23 11 -12  -52.17391
 9 0 18 18   .          .
 9 1 18  .   .          .
 9 2 18 13  -5  -27.77778
 9 3 18  7 -11  -61.11111
13 0  .  .   .          .
13 1  .  .   .          .
13 2  . 11   .          .
13 3  . 12   .          .
14 0  8  8   .          .
14 1  8  .   .          .
14 2  8  4  -4        -50
14 3  8 15   7       87.5
16 0 14 14   .          .
16 1 14  .   .          .
16 2 14 19   5  35.714287
17 0 17 17   .          .
19 0 19 19   .          .
19 1 19  .   .          .
19 2 19 19   0          0
19 3 19 16  -3 -15.789474
21 0 12 12   .          .
21 1 12  .   .          .
21 2 12  0 -12       -100
21 3 12  0 -12       -100
22 0  0  0   .          .
22 1  0  .   .          .
22 2  0  0   0          .
22 3  0  0   0          .
23 0 12 12   .          .
23 1 12  .   .          .
23 2 12 12   0          0
23 3 12 12   0          0
25 0  0  0   .          .
25 1  0  .   .          .
25 2  0  9   9          .
25 3  0  4   4          .
26 0  6  6   .          .
26 1  6  .   .          .
26 2  6 12   6        100
26 3  6  8   2  33.333332
27 0  8  8   .          .
27 1  8  .   .          .
27 2  8  6  -2        -25
27 3  8 12   4         50
28 0 24 24   .          .
28 1 24  .   .          .
28 2 24  6 -18        -75
28 3 24  0 -24       -100
29 0  0  0   .          .
29 1  0  .   .          .
29 2  0  3   3          .
29 3  0  3   3          .
32 0 24 24   .          .
32 1 24  .   .          .
32 2 24 13 -11  -45.83333
32 3 24 20  -4 -16.666666
33 0 12 12   .          .
33 1 12  .   .          .
33 3 12  .   .          .
34 0 14 14   .          .
34 1 14  .   .          .
34 2 14  0 -14       -100
34 3 14  0 -14       -100
36 0 13 13   .          .
36 1 13  .   .          .
36 2 13 16   3  23.076923
36 3 13  6  -7  -53.84615
38 0 17 17   .          .
38 1 17  .   .          .
38 2 17  5 -12 -70.588234
38 3 17  6 -11  -64.70588
42 0  3  3   .          .
42 1  3  .   .          .
42 2  3  0  -3       -100
42 3  3  0  -3       -100
43 0 15 15   .          .
43 1 15  .   .          .
43 2 15 18   3         20
43 3 15 15   0          0
45 0  5  5   .          .
45 1  5  .   .          .
45 2  5  5   0          0
45 3  5 13   8        160
47 0  1  1   .          .
47 1  1  .   .          .
47 2  1  0  -1       -100
47 3  1  0  -1       -100
49 0  9  9   .          .
51 0 26 26   .          .
51 1 26  .   .          .
end

[Clyde Schechter]

It is you, not Stata, that has it wrong. 0/0 is not 0. It is undefined and is correctly represented as a missing value. If you want your variable to be a hybrid of actual percent change for most observations, and arbitrarily set to zero when the base value is zero, you can just add another command to -replace- the value with zero in that circumstance. Such a variable might be suitable for your purposes, I can't say. But be aware that such a variable is not actually percent change. In fact, there is no such thing as percent change from a baseline value of zero.

[Jake Magel]

Great points. Understood. Thanks.