need to count experiences of employee with respect to year

[tauseef ali]

Hi experts
I want to count the experiences of employees (i.e., personalID), with respect to years such as if they worked in 2001 with company 4 as well as with company 8 so the wanted should be 1 no 2. Sample data is as follows
Thanks and Regards

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id year personalID wanted)
4 2001 1001 1
8 2001 1001 1
4 2003 1001 2
8 2003 1001 2
8 2004 1001 3
4 2004 1001 3
4 2001 1002 1
8 2002 1002 2
4 2002 1002 2
8 2003 1002 3
4 2004 1002 4
8 2001 1003 1
4 2002 1003 2
4 2003 1003 3
8 2004 1003 4
8 2002 1004 1
end

[Carlo Lazzaro]

Tauseef:
I find your example a bit inconsistent.
Sometimes the reference year seems to be 2000, sometimes the year[_n-1].
Be as it may, a temptative answer might be:

. bysort personalID (year id): g wanted2= year-2000
. list

     +-----------------------------------------+
     | id   year   person~D   wanted   wanted2 |
     |-----------------------------------------|
  1. |  4   2001       1001        1         1 |
  2. |  8   2001       1001        1         1 |
  3. |  4   2003       1001        2         3 |
  4. |  8   2003       1001        2         3 |
  5. |  4   2004       1001        3         4 |
     |-----------------------------------------|
  6. |  8   2004       1001        3         4 |
  7. |  4   2001       1002        1         1 |
  8. |  4   2002       1002        2         2 |
  9. |  8   2002       1002        2         2 |
 10. |  8   2003       1002        3         3 |
     |-----------------------------------------|
 11. |  4   2004       1002        4         4 |
 12. |  8   2001       1003        1         1 |
 13. |  4   2002       1003        2         2 |
 14. |  4   2003       1003        3         3 |
 15. |  8   2004       1003        4         4 |
     |-----------------------------------------|
 16. |  8   2002       1004        1         2 |
     +-----------------------------------------+

.

[Maarten Buis]

It is hard to understand your question. Number of years experience needs at least two years: the year that you start working and the year that you stop working. I see only 1 year. Or is this one observation for every year that a person is observed to be employed by a firm? Can you take one person and show us the computations you want Stata to do?

[tauseef ali]

Tauseef:
I find your example a bit inconsistent.
Sometimes the reference year seems to be 2000, sometimes the year[_n-1].
Be as it may, a temptative answer might be:

. bysort personalID (year id): g wanted2= year-2000
. list

+-----------------------------------------+
| id year person~D wanted wanted2 |
|-----------------------------------------|
1. | 4 2001 1001 1 1 |
2. | 8 2001 1001 1 1 |
3. | 4 2003 1001 2 3 |
4. | 8 2003 1001 2 3 |
5. | 4 2004 1001 3 4 |
|-----------------------------------------|
6. | 8 2004 1001 3 4 |
7. | 4 2001 1002 1 1 |
8. | 4 2002 1002 2 2 |
9. | 8 2002 1002 2 2 |
10. | 8 2003 1002 3 3 |
|-----------------------------------------|
11. | 4 2004 1002 4 4 |
12. | 8 2001 1003 1 1 |
13. | 4 2002 1003 2 2 |
14. | 4 2003 1003 3 3 |
15. | 8 2004 1003 4 4 |
|-----------------------------------------|
16. | 8 2002 1004 1 2 |
+-----------------------------------------+

.

Sir the provided code does not produce the intended results given in the wanted column

[tauseef ali]

Tauseef:
I find your example a bit inconsistent.
Sometimes the reference year seems to be 2000, sometimes the year[_n-1].
Be as it may, a temptative answer might be:

. bysort personalID (year id): g wanted2= year-2000
. list

+-----------------------------------------+
| id year person~D wanted wanted2 |
|-----------------------------------------|
1. | 4 2001 1001 1 1 |
2. | 8 2001 1001 1 1 |
3. | 4 2003 1001 2 3 |
4. | 8 2003 1001 2 3 |
5. | 4 2004 1001 3 4 |
|-----------------------------------------|
6. | 8 2004 1001 3 4 |
7. | 4 2001 1002 1 1 |
8. | 4 2002 1002 2 2 |
9. | 8 2002 1002 2 2 |
10. | 8 2003 1002 3 3 |
|-----------------------------------------|
11. | 4 2004 1002 4 4 |
12. | 8 2001 1003 1 1 |
13. | 4 2002 1003 2 2 |
14. | 4 2003 1003 3 3 |
15. | 8 2004 1003 4 4 |
|-----------------------------------------|
16. | 8 2002 1004 1 2 |
+-----------------------------------------+

.

Sir the provided code does not produce the intended results given in the wanted column

[Carlo Lazzaro]

Tauseef:
how can it be that:
1) personalID 1001 totalized 1 year of experience in 2001, that become 3 in 2004 (according to wanted;
whereas:
2) personalID 1003 totalized 1 year of experience in 2001, that become 4 in 2004 (according to wanted)?

[tauseef ali]

Tauseef:
how can it be that:
1) personalID 1001 totalized 1 year of experience in 2001, that become 3 in 2004 (according to wanted;
whereas:
2) personalID 1003 totalized 1 year of experience in 2001, that become 4 in 2004 (according to wanted)?

professor, i hope it would help you in understanding, I used a filter in Excel

id	year	personalID	wanted
4	2001	1001	1
8	2001	1001	1
4	2003	1001	2
8	2003	1001	2
8	2004	1001	3
4	2004	1001	3

[tauseef ali]

Tauseef:
how can it be that:
1) personalID 1001 totalized 1 year of experience in 2001, that become 3 in 2004 (according to wanted;
whereas:
2) personalID 1003 totalized 1 year of experience in 2001, that become 4 in 2004 (according to wanted)?

yes sir please have look at the immage

id	year	personalID	wanted
4	2001	1001	1
8	2001	1001	1
4	2003	1001	2
8	2003	1001	2
8	2004	1001	3
4	2004	1001	3

[tauseef ali]

It is hard to understand your question. Number of years experience needs at least two years: the year that you start working and the year that you stop working. I see only 1 year. Or is this one observation for every year that a person is observed to be employed by a firm? Can you take one person and show us the computations you want Stata to do?

id	year	personalID	wanted
4	2001	1001	1
8	2001	1001	1
4	2003	1001	2
8	2003	1001	2
8	2004	1001	3
4	2004	1001	3

[Maarten Buis]

That is just a bunch of numbers. Without context numbers mean exactly nothing. What operations lead to wanted? How do you know at what year to start? What is the problem you want us to solve for you? Is this panel data? You need to tell us much more before we help you.

[tauseef ali]

That is just a bunch of numbers. Without context numbers mean exactly nothing. What operations lead to wanted? How do you know at what year to start? What is the problem you want us to solve for you? Is this panel data? You need to tell us much more before we help you.

Thank you professor, suppose you are an employee in the year of 2001,2002, and 2004 in two companies (i.e., 4 and 8). So if i ask you how many total years of experience you have ? you answer will be 3 years. So i will say write in the 2001 infront of each company value 1,1 and 2002 the values will 2,2 and in the year 2004 the values will be 3,3. So if we have hundrends of employees how will you write these values , 1,1. 2,2. 3,3 ......? This is my question. Hope you got the problem

[tauseef ali]

That is just a bunch of numbers. Without context numbers mean exactly nothing. What operations lead to wanted? How do you know at what year to start? What is the problem you want us to solve for you? Is this panel data? You need to tell us much more before we help you.

yes sir its panel data

[Maarten Buis]

suppose you are an employee in the year of 2001,2002, and 2004 in two companies (i.e., 4 and 8). So if i ask you how many total years of experience you have ? you answer will be 3 years.

That assumes that they only work when you see them in the data. That is almost never the case (unless you have register data, but even than people may work in different countries or otherwise not appear in the register when they should, e.g. in Germany you won't appear in some registers if you are a civil servant). So in the example you gave us, you assumed that person 1001 did not work in 2002, or in 1999, or in 1998, etc. So treating that variable as experience can be very misleading. Say person 1001 started working in 1980 and worked continuously, then her/his actual experience would 21, 22, and 23. If another person has the same pattern in your data, but started working in 2000, than you are both assigning them the values 1,2, and 3, whereas the actual experiences are 21, 22, and 23 for person 1001 and 1, 2, and 3 for the other person. So do you really want to do that, or is that variable going to give you more problems than it solves?

My guess is that you really don't want to do this. But in case you still want to, here is one way to do so:

. clear

. input id        year    personalID      wanted

            id       year  persona~D     wanted
  1. 4       2001    1001    1
  2. 8       2001    1001    1
  3. 4       2003    1001    2
  4. 8       2003    1001    2
  5. 8       2004    1001    3
  6. 4       2004    1001    3
  7. 1       2001    1002    1
  8. 2       2001    1002    1
  9. 1       2003    1002    2
 10. 2       2003    1002    2
 11. 1       2004    1002    3
 12. 4       2004    1002    3
 13. end

.
. bysort personalID year : gen first = (_n == 1)

. by personalID: gen pseudoexperience = sum(first)

. list , sepby(personalID)

     +--------------------------------------------------+
     | id   year   person~D   wanted   first   pseudo~e |
     |--------------------------------------------------|
  1. |  4   2001       1001        1       1          1 |
  2. |  8   2001       1001        1       0          1 |
  3. |  4   2003       1001        2       1          2 |
  4. |  8   2003       1001        2       0          2 |
  5. |  8   2004       1001        3       1          3 |
  6. |  4   2004       1001        3       0          3 |
     |--------------------------------------------------|
  7. |  1   2001       1002        1       1          1 |
  8. |  2   2001       1002        1       0          1 |
  9. |  1   2003       1002        2       1          2 |
 10. |  2   2003       1002        2       0          2 |
 11. |  1   2004       1002        3       1          3 |
 12. |  4   2004       1002        3       0          3 |
     +--------------------------------------------------+

[tauseef ali]

That assumes that they only work when you see them in the data. That is almost never the case (unless you have register data, but even than people may work in different countries or otherwise not appear in the register when they should, e.g. in Germany you won't appear in some registers if you are a civil servant). So in the example you gave us, you assumed that person 1001 did not work in 2002, or in 1999, or in 1998, etc. So treating that variable as experience can be very misleading. Say person 1001 started working in 1980 and worked continuously, then her/his actual experience would 21, 22, and 23. If another person has the same pattern in your data, but started working in 2000, than you are both assigning them the values 1,2, and 3, whereas the actual experiences are 21, 22, and 23 for person 1001 and 1, 2, and 3 for the other person. So do you really want to do that, or is that variable going to give you more problems than it solves?

My guess is that you really don't want to do this. But in case you still want to, here is one way to do so:

. clear

. input id year personalID wanted

id year persona~D wanted
1. 4 2001 1001 1
2. 8 2001 1001 1
3. 4 2003 1001 2
4. 8 2003 1001 2
5. 8 2004 1001 3
6. 4 2004 1001 3
7. 1 2001 1002 1
8. 2 2001 1002 1
9. 1 2003 1002 2
10. 2 2003 1002 2
11. 1 2004 1002 3
12. 4 2004 1002 3
13. end

.
. bysort personalID year : gen first = (_n == 1)

. by personalID: gen pseudoexperience = sum(first)

. list , sepby(personalID)

+--------------------------------------------------+
| id year person~D wanted first pseudo~e |
|--------------------------------------------------|
1. | 4 2001 1001 1 1 1 |
2. | 8 2001 1001 1 0 1 |
3. | 4 2003 1001 2 1 2 |
4. | 8 2003 1001 2 0 2 |
5. | 8 2004 1001 3 1 3 |
6. | 4 2004 1001 3 0 3 |
|--------------------------------------------------|
7. | 1 2001 1002 1 1 1 |
8. | 2 2001 1002 1 0 1 |
9. | 1 2003 1002 2 1 2 |
10. | 2 2003 1002 2 0 2 |
11. | 1 2004 1002 3 1 3 |
12. | 4 2004 1002 3 0 3 |
+--------------------------------------------------+

Thanks you so much professor, your provided code works perfectly. Thanks you so much again for your sincere efforts. Sir your concerns are valid. The provided sample data was just a reflection of actual data while the actual data seems more complex.