Counting person who meet the criteria at the end of the month

[Papungkorn Kitcharoenkarnkul]

Hi Statalist,

I hope your day is going well. I need some help with counting individuals in the dataset who meet certain criteria (marked as flag in the dataset). I created a hypothetical example to demonstrate what I'm trying to do.

Basically, I want to count the number of flags a person has on a monthly basis. The problem arises because some individuals may have the flag in some instances but not in others within the same month. Below are the data and code I've attempted so far:

*please install dataex
clear all
input float person float time byte flag
1 1 1
1 2 1
1 3 1
1 4 1
1 5 1
1 6 1
2 1 0
2 1 1
2 2 1
2 3 0
2 4 0
3 1 0
3 2 0
3 3 0
3 4 1
4 1 0
4 1 1
4 2 0
4 3 0
4 4 0
4 5 0
4 6 0
5 1 0
5 2 0
5 3 0
5 4 0
5 5 0
5 6 0
5 6 1
end

by person (flag time), sort: egen test = sum(flag & (time != time[_n - 1]))

After running this code, I successfully counted the flag for almost every individual except the 5th person. I have been trying to figure out how to include this last observation, but I'm out of ideas. I would appreciate any suggestions or ideas on how to modify the code to include the 5th person in this scenario. Please note that the 5th person is not the last person, and there is no consistent pattern for time and flag across individuals.

Thank you!

[George Ford]

Not sure what the "time !=" is doing.

You have 2 values of time==6 for the last observation. Why?

[Papungkorn Kitcharoenkarnkul]

Thanks, George, for your observation. I added 'time != ' to count the number of flags in different months. I have two values for time because, within the same month, it is possible for an individual to both have and not have the flag. Why is this? That's how the data is recorded in this dataset. That's why I tried to write the code to accommodate this situation.

To provide a bit more context, I can't modify the dataframe because other columns are necessary for calculating additional indicators that I didn't include in this example. Ideally, I would like to adjust my code to account for this scenario.

Let me know if I didn't answer your question.

[Carlo Lazzaro]

Papungkorn:
what about:

. bysort person time: gen wanted=sum(flag)

[Papungkorn Kitcharoenkarnkul]

Hi Carlo Lazzaro , thanks for sharing your thought. I tried this command. It works only the last individual. However, the count for the first four individual are not accurate. I appreciate your thought tho and I will try to incorporate the idea into my code.

Best,
Kob

[Andrew Musau]

by person (flag time), sort: egen test = sum(flag & (time != time[_n - 1]))
[/CODE]

After running this code, I successfully counted the flag for almost every individual except the 5th person. I have been trying to figure out how to include this last observation, but I'm out of ideas. I would appreciate any suggestions or ideas on how to modify the code to include the 5th person in this scenario. Please note that the 5th person is not the last person, and there is no consistent pattern for time and flag across individuals.

First of all, the egen documentation warns against subscripting.

Explicit subscripting
(using _N and _n), which is commonly used with generate, should not be used with egen; see subscripting.

So you really need 2 lines to do what you want.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(person time) byte flag float test
1 1 1 6
1 2 1 6
1 3 1 6
1 4 1 6
1 5 1 6
1 6 1 6
2 1 0 2
2 1 1 2
2 2 1 2
2 3 0 2
2 4 0 2
3 1 0 1
3 2 0 1
3 3 0 1
3 4 1 1
4 1 0 1
4 1 1 1
4 2 0 1
4 3 0 1
4 4 0 1
4 5 0 1
4 6 0 1
5 1 0 0
5 2 0 0
5 3 0 0
5 4 0 0
5 5 0 0
5 6 0 0
5 6 1 0
end

*EITHER
bysort person (time flag): gen wanted1= sum(time!=time[_n+1] & flag)
by person: replace wanted1= wanted1[_N]

*OR
bysort person time (flag): gen tag= _n==_N
by person: egen wanted2= total(tag & flag)

Res.:

. l, sepby(person)

     +-------------------------------------------------------+
     | person   time   flag   test   wanted1   tag   wanted2 |
     |-------------------------------------------------------|
  1. |      1      1      1      6         6     1         6 |
  2. |      1      2      1      6         6     1         6 |
  3. |      1      3      1      6         6     1         6 |
  4. |      1      4      1      6         6     1         6 |
  5. |      1      5      1      6         6     1         6 |
  6. |      1      6      1      6         6     1         6 |
     |-------------------------------------------------------|
  7. |      2      1      0      2         2     0         2 |
  8. |      2      1      1      2         2     1         2 |
  9. |      2      2      1      2         2     1         2 |
 10. |      2      3      0      2         2     1         2 |
 11. |      2      4      0      2         2     1         2 |
     |-------------------------------------------------------|
 12. |      3      1      0      1         1     1         1 |
 13. |      3      2      0      1         1     1         1 |
 14. |      3      3      0      1         1     1         1 |
 15. |      3      4      1      1         1     1         1 |
     |-------------------------------------------------------|
 16. |      4      1      0      1         1     0         1 |
 17. |      4      1      1      1         1     1         1 |
 18. |      4      2      0      1         1     1         1 |
 19. |      4      3      0      1         1     1         1 |
 20. |      4      4      0      1         1     1         1 |
 21. |      4      5      0      1         1     1         1 |
 22. |      4      6      0      1         1     1         1 |
     |-------------------------------------------------------|
 23. |      5      1      0      0         1     1         1 |
 24. |      5      2      0      0         1     1         1 |
 25. |      5      3      0      0         1     1         1 |
 26. |      5      4      0      0         1     1         1 |
 27. |      5      5      0      0         1     1         1 |
 28. |      5      6      0      0         1     0         1 |
 29. |      5      6      1      0         1     1         1 |
     +-------------------------------------------------------+

.

[Papungkorn Kitcharoenkarnkul]

Hi Andrew Musau, thank you so much for your solution! It works perfectly, and I can further edit it to address my other cases. Your help is greatly appreciated!