Counting number of non-missing variables by visit id in longitudinal dataset

[Victoria Limon]

Hello,

I have a longitudinal dataset that is shown as follows:

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str8 vis_id double(sfvitaly sfs_func sfrole_e sfmhealt curmetsyn) float totalphqscore double seqno
"VIS00002" 55  100                100  92 1  . 1
"VIS00002" 80  100                100  92 1  . 2
"VIS00002" 70  100                100  80 1  . 3
"VIS00002" 60  100                100  76 1  . 4
"VIS00003" 35   50                100  56 0  . 1
"VIS00003" 35 62.5                100  60 1  . 2
"VIS00003" 55 87.5                100  68 1  . 3
"VIS00003" 55 87.5                100   . 1  4 4
"VIS00003" 25 37.5  66.66666666666667  52 1  3 5
"VIS00004" 75  100                100 100 0  . 1
"VIS00004" 40   50                100  80 0  5 2
"VIS00005" 65  100                100  84 .  . 1
"VIS00005" 50  100                100  76 1  . 2
"VIS00006" 45   75                  0  40 1  . 1
"VIS00007" 45 62.5                  0  80 1  2 1
"VIS00008" 80  100                100   . .  0 1
"VIS00009" 75  100                100  84 0  . 1
"VIS00009" 70  100                100  84 0  . 2
"VIS00009" 60 87.5                100  84 0  1 3
"VIS00010" 55   50 33.333333333333336  68 1  . 1
"VIS00010" 40   50  66.66666666666667  60 1 13 2
"VIS00010" 70  100                100  76 1  6 3
"VIS00011" 70   50                100  88 0  . 1
"VIS00011" 75  100                100  88 0  . 2
"VIS00011" 65  100                100  88 0  0 3
"VIS00011" 70  100                100  80 0  0 4
"VIS00013" 40 87.5                100  68 0  . 1
"VIS00014" 75  100                100  80 .  3 1
"VIS00014" 80  100                100  64 0  4 2
"VIS00015" 75  100                100  72 1  . 1
"VIS00015" 75   50                100  92 0  . 2
"VIS00015" 80   50                100  96 0  0 3
"VIS00017" 70  100                100  92 0  0 1
"VIS00018" 20   50 33.333333333333336  48 0  . 1
"VIS00018" 35 37.5 33.333333333333336  72 .  . 2
"VIS00018" 15 37.5  66.66666666666667  72 1  . 3
"VIS00018" 30   25  66.66666666666667  64 0  . 4
"VIS00018" 25   50  66.66666666666667  72 0  3 5
"VIS00018" 75 37.5  66.66666666666667  72 0  5 6
"VIS00018" 65   50                100  84 1  4 7
"VIS00019" 65  100                100  88 1  . 1
"VIS00019" 70  100                  .  92 1  . 2
"VIS00020" 75  100                100  84 0  . 1
"VIS00020" 65  100                100  80 0  . 2
"VIS00020" 65  100                100  80 0  . 3
"VIS00020" 65  100                100  84 0  . 4
"VIS00020" 60  100                100  88 0  . 5
"VIS00020" 65  100                100  84 0  2 6
"VIS00020" 65  100                100  72 0  1 7
"VIS00021" 75  100                100  88 0  . 1
"VIS00021" 80 87.5                100  84 0  . 2
"VIS00021" 90  100                100  96 .  . 3
"VIS00021" 70  100                100  88 .  1 4
"VIS00023" 75  100                100  96 0  . 1
"VIS00023" 75  100                100  88 0  1 2
"VIS00024" 75  100                100  84 0  . 1
"VIS00024" 65  100                100  84 0  . 2
"VIS00024" 70  100                100  84 0  0 3
"VIS00024" 55  100                100  84 0  1 4
"VIS00024" 60  100                100  80 0  1 5
"VIS00026" 55 87.5                100  64 0  . 1
"VIS00026" 70   25 33.333333333333336  56 0  . 2
"VIS00026" 55 62.5  66.66666666666667  44 0 13 3
"VIS00026" 25 62.5  66.66666666666667  56 0  6 4
"VIS00026" 25   75  66.66666666666667  28 1 18 5
"VIS00026" 50 37.5                  0  44 0 21 6
"VIS00027"  0 62.5  66.66666666666667  72 0  . 1
"VIS00028" 50 87.5                100  80 1  . 1
"VIS00028" 50  100                100  72 1  2 2
"VIS00028" 70  100                100  88 1  2 3
"VIS00029" 80  100                100  88 0  . 1
"VIS00029" 80  100                100  84 0  . 2
"VIS00029" 70  100                100  92 1  . 3
"VIS00029" 80   75                100  92 1  . 4
"VIS00030" 65  100                100  84 1  . 1
"VIS00030" 50 87.5                100  92 1  1 2
"VIS00031" 80  100                100  88 0  . 1
"VIS00031" 75  100                100  72 1  0 2
"VIS00032"  0 37.5 33.333333333333336  32 0  . 1
"VIS00032"  0   25                  0  56 0 12 2
"VIS00033" 45   50                  0  56 1  5 1
"VIS00033" 45   50  33.33333333333333  60 1  6 2
"VIS00034" 80  100                100  76 0  . 1
"VIS00034" 60   75                100  64 0  4 2
"VIS00035" 75  100                100  80 1  . 1
"VIS00035" 75 87.5                100  80 1  . 2
"VIS00035" 80 87.5                100  76 1  . 3
"VIS00035" 65  100  66.66666666666667  84 0  1 4
"VIS00036" 80  100                100  88 0  . 1
"VIS00036" 65   50                100  72 0  . 2
"VIS00036" 70  100                100  68 0  2 3
"VIS00036" 70  100                100  76 0  0 4
"VIS00036" 80   50                100  84 0  0 5
"VIS00036" 70   50                100  68 0  1 6
"VIS00037" 80 87.5                100  88 0  . 1
"VIS00037" 80   75                100  88 0  0 2
"VIS00038" 75  100                100  92 0  . 1
"VIS00039" 40   75                100  64 0  9 1
"VIS00040" 30   25                  0  84 .  1 1
"VIS00041" 65  100                100  84 0  . 1
end

I need to calculate the total number of non-missing values for the variables shown for each vis_id by seqno. The reason being that I will use the seqno that has the highest number of non-missing variables for each vis_id. I have tried to understand the missingstable command, but I'm getting pretty confused by it and I'm not sure it can do exactly what I want it to do. Does anyone have any advice?
Thank you in advance.

[Joseph Coveney]

Something like the following might get you close to what you want.

egen byte not_missing = rownonmiss(sfvitaly-totalphqscore)
bysort vis_id: egen max_nonmiss = max(not_missing)
list if not_missing == max_nonmiss, noobs abbreviate(20) sepby(vis_id)