Using the egen command

[Ye Jin Bang]

Hello, Statalist members,

I am a new Stata user. I would like to count the total number from the first year to the last year given a value, including missing values.

That's how the table should look like:

id	qt1_2000	qt2_2000	qt3_2000	qt4_2000	qt1_2001	qt2_2001	qt3_2001	qt4_2001	count
1	1.1	1.2	.	.	1.1	1.2	.	1.4	8
2	2.1	2.2	2.3	2.4	2.1	2.2	2.3	2.4	8
3	.	.	3.3	3.4	3.1	.	3.3	.	5
4	.	4.2	4.3	.	4.1	4.2	.	.	5
5	.	5.2	.	5.4	.	5.2	.	5.4	7

I have a variable called qt1_2000~qt4_2001 and one called id, which is an identifier for each population.

Is anybody here who can help me with this issue?

Thank you for your time and insights.

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input byte id float(qt1_2000 qt2_2000 qt3_2000 qt4_2000 qt1_2001 qt2_2001 qt3_2001 qt4_2001)
1 1.1 1.2   .   . 1.1 1.2   . 1.4
2 2.1 2.2 2.3 2.4 2.1 2.2 2.3 2.4
3   .   . 3.3 3.4 3.1   . 3.3   .
4   . 4.2 4.3   . 4.1 4.2   .   .
5   . 5.2   . 5.4   . 5.2   . 5.4
end

rename qt*_* qt*[2]_*[1]
reshape long qt, i(id) j(which) string
bys id (which): drop if !sum(!missing(qt))
gsort id -which
by id: drop if !sum(!missing(qt))
bys id: gen wanted=_N
reshape wide qt, i(id) j(which) string
rename qt*_* qt*[2]_*[1]

Res.:

. l

     +-----------------------------------------------------------------------------------------------------+
     | id   qt1_2000   qt2_2000   qt3_2000   qt4_2000   qt1_2001   qt2_2001   qt3_2001   qt4_2001   wanted |
     |-----------------------------------------------------------------------------------------------------|
  1. |  1        1.1        1.2          .          .        1.1        1.2          .        1.4        8 |
  2. |  2        2.1        2.2        2.3        2.4        2.1        2.2        2.3        2.4        8 |
  3. |  3          .          .        3.3        3.4        3.1          .        3.3          .        5 |
  4. |  4          .        4.2        4.3          .        4.1        4.2          .          .        5 |
  5. |  5          .        5.2          .        5.4          .        5.2          .        5.4        7 |
     +-----------------------------------------------------------------------------------------------------+

[Andrew Musau]

A looping solution with the data in wide layout is also possible here, but it is no more efficient than reshaping to long layout.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte id float(qt1_2000 qt2_2000 qt3_2000 qt4_2000 qt1_2001 qt2_2001 qt3_2001 qt4_2001)
1 1.1 1.2   .   . 1.1 1.2   . 1.4
2 2.1 2.2 2.3 2.4 2.1 2.2 2.3 2.4
3   .   . 3.3 3.4 3.1   . 3.3   .
4   . 4.2 4.3   . 4.1 4.2   .   .
5   . 5.2   . 5.4   . 5.2   . 5.4
end

gen first=0
gen last=0
gen wanted=0
local count 1
foreach var of varlist qt1_2000-qt4_2001{
    replace last=`count' if !missing(`var')
    replace wanted=wanted+ !missing(`var')
    replace first = `count' if !wanted
    local ++count
}
replace wanted= last-first

Res.:

. l

     +--------------------------------------------------------------------------------------------------------------------+
     | id   qt1_2000   qt2_2000   qt3_2000   qt4_2000   qt1_2001   qt2_2001   qt3_2001   qt4_2001   first   last   wanted |
     |--------------------------------------------------------------------------------------------------------------------|
  1. |  1        1.1        1.2          .          .        1.1        1.2          .        1.4       0      8        8 |
  2. |  2        2.1        2.2        2.3        2.4        2.1        2.2        2.3        2.4       0      8        8 |
  3. |  3          .          .        3.3        3.4        3.1          .        3.3          .       2      7        5 |
  4. |  4          .        4.2        4.3          .        4.1        4.2          .          .       1      6        5 |
  5. |  5          .        5.2          .        5.4          .        5.2          .        5.4       1      8        7 |
     +--------------------------------------------------------------------------------------------------------------------+

[Ye Jin Bang]

Thank you so much Andrew Musau. I have tried this and It works perfectly.

Thank you for your help.
Ye Jin Bang