Aggregating information across fields by date

[Jackie Kleynhans]

I’m using Stata 14.0 and I have a longitudinal data set.

Individuals are marked by ID numbers (00-001-9), and each individual is one record. There is a ‘centre’ field to identify which centre was visited (apple/pear/peach) and then there are 5 date fields (screening date 1-5) and a binomial field associated with each screening date stating the vaccination status. The vaccine can only be received once. Once the vaccine is received, the vaccination status always stays 1, and the vaccination status is also reflected in an aggregated field rec_vac_2017 that is changed to 1 if vaccinated during any of the screening dates.
An example:

-----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 id str5 centre str10 first_scrn_dt byte first_rec_vac str10 sec_scrn_dt byte sec_rec_vac str10 thi_scrn_dt byte thi_rec_vac str10 forth_scrn_dt byte forth_rec_vac str10 fifth_scrn_dt byte(fifth_rec_vac rec_vac_2017)
"00-001" "apple" "03/03/2017" 0 "05/03/2017" 1 ""           . ""           . ""           . 1
"00-002" "pear"  "18/03/2017" 0 "20/03/2017" 0 "25/03/2017" 1 ""           . ""           . 1
"00-003" "apple" "05/03/2017" 0 "07/03/2017" 0 ""           . ""           . ""           . 0
"00-004" "peach" "26/03/2017" 0 ""           . ""           . ""           . ""           . 0
"00-005" "peach" "03/03/2017" 0 "06/03/2017" 0 "09/03/2017" 1 "12/03/2017" 1 "25/03/2017" . 1
"00-006" "apple" "10/03/2017" 1 ""           . ""           . ""           . ""           . 1
"00-007" "pear"  "11/03/2017" 1 ""           . ""           . ""           . ""           . 1
"00-008" "pear"  "03/03/2017" 0 "05/03/2017" 1 "07/03/2017" 1 "20/03/2017" . ""           . 1
"00-009" "pear"  "12/03/2017" 0 ""           . ""           . ""           . ""           . 0
end

------------------



I would like to create a summary table in Stata that will reflect the total amount of individuals seen per day and their vaccination status, by centre. Something like this:

	Apple			Pear			Peach
Date	Vac	Not vac	Total	Vac	Not vac	Total	Vac	Not vac	Total
3/3/2017	0	1	1	1	5	6	2	0	2
4/3/2017	0	1	1	2	6	8	2	2	4
5/3/2017	0	1	1	3	9	12	2	3	5
6/3/2017	1	2	3	10	10	20	2	5	7
7/3/2017	0	1	1	0	12	12	2	6	8
8/3/2017	0	1	1	2	30	32	2	8	10
9/3/2017	0	0	0	0	1	1	2	2	4

So I need to be able to see how many individuals were screened at each centre per day, although the same individual may be counted on different days.
Is there a code in Stata that will allow this?

And can this table be exported to excel?

[Nick Cox]

Please go back and read and act on

http://www.statalist.org/forums/help#stata

and incidentally also http://www.statalist.org/forums/help#spelling

That is, your data example is not at all easy to read. It is impractical for people to want to try to use it in any reply.

That's why we ask people to read the Advice given before posting: to stop them spending valuable time posting questions that are difficult to understand.

[Jackie Kleynhans]

Dear Nick Cox

Thank you for the response. I do apologize, it is not my intention to waste anyone's time. I did not know using dataex was a requirement.

My post has been revised and is hopefully easier to understand.

[Nick Cox]

It's not a requirement or rule; it's just advice we believe to be good advice.

Thanks for the data example. I wouldn't hold data like this in such a structure. This may help:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 id str5 centre str10 first_scrn_dt byte first_rec_vac str10 sec_scrn_dt byte sec_rec_vac str10 thi_scrn_dt byte thi_rec_vac str10 forth_scrn_dt byte forth_rec_vac str10 fifth_scrn_dt byte(fifth_rec_vac rec_vac_2017)
"00-001" "apple" "03/03/2017" 0 "05/03/2017" 1 ""           . ""           . ""           . 1
"00-002" "pear"  "18/03/2017" 0 "20/03/2017" 0 "25/03/2017" 1 ""           . ""           . 1
"00-003" "apple" "05/03/2017" 0 "07/03/2017" 0 ""           . ""           . ""           . 0
"00-004" "peach" "26/03/2017" 0 ""           . ""           . ""           . ""           . 0
"00-005" "peach" "03/03/2017" 0 "06/03/2017" 0 "09/03/2017" 1 "12/03/2017" 1 "25/03/2017" . 1
"00-006" "apple" "10/03/2017" 1 ""           . ""           . ""           . ""           . 1
"00-007" "pear"  "11/03/2017" 1 ""           . ""           . ""           . ""           . 1
"00-008" "pear"  "03/03/2017" 0 "05/03/2017" 1 "07/03/2017" 1 "20/03/2017" . ""           . 1
"00-009" "pear"  "12/03/2017" 0 ""           . ""           . ""           . ""           . 0
end

rename (*scrn_dt) (scrn*)
rename (*_rec_vac) (rec_vac*)
rename (*first*) (*1)
rename (*sec*) (*2)
rename (*thi*) (*3)
rename (*forth*) (*4)
rename (*fifth*) (*5)

reshape long scrn rec_vac, i(id centre) j(date)
drop if missing(scrn)
bysort id centre (date): gen wanted = sum(rec_vac) >= 1

list, sepby(id centre)



     +-------------------------------------------------------------------+
     |     id   centre   date         scrn   rec_vac   rec~2017   wanted |
     |-------------------------------------------------------------------|
  1. | 00-001    apple      1   03/03/2017         0          1        0 |
  2. | 00-001    apple      2   05/03/2017         1          1        1 |
     |-------------------------------------------------------------------|
  3. | 00-002     pear      1   18/03/2017         0          1        0 |
  4. | 00-002     pear      2   20/03/2017         0          1        0 |
  5. | 00-002     pear      3   25/03/2017         1          1        1 |
     |-------------------------------------------------------------------|
  6. | 00-003    apple      1   05/03/2017         0          0        0 |
  7. | 00-003    apple      2   07/03/2017         0          0        0 |
     |-------------------------------------------------------------------|
  8. | 00-004    peach      1   26/03/2017         0          0        0 |
     |-------------------------------------------------------------------|
  9. | 00-005    peach      1   03/03/2017         0          1        0 |
 10. | 00-005    peach      2   06/03/2017         0          1        0 |
 11. | 00-005    peach      3   09/03/2017         1          1        1 |
 12. | 00-005    peach      4   12/03/2017         1          1        1 |
 13. | 00-005    peach      5   25/03/2017         .          1        1 |
     |-------------------------------------------------------------------|
 14. | 00-006    apple      1   10/03/2017         1          1        1 |
     |-------------------------------------------------------------------|
 15. | 00-007     pear      1   11/03/2017         1          1        1 |
     |-------------------------------------------------------------------|
 16. | 00-008     pear      1   03/03/2017         0          1        0 |
 17. | 00-008     pear      2   05/03/2017         1          1        1 |
 18. | 00-008     pear      3   07/03/2017         1          1        1 |
 19. | 00-008     pear      4   20/03/2017         .          1        1 |
     |-------------------------------------------------------------------|
 20. | 00-009     pear      1   12/03/2017         0          0        0 |
     +-------------------------------------------------------------------+

[Jackie Kleynhans]

Thank you. I do see how having the data in this format will resolve the issue. As the data is captured in real-time and stored in a database in the format I showed, I would have to write a do-file to reshape the data every day when I do a draw-down to get the daily stats.

Is there no solution to present the data in a table when the data is in the format I provided?

[Nick Cox]

I would not say "No solution". I would say no solution that I want to write. Sorry.

[Jackie Kleynhans]

Thank you for the help, I appreciate the solution.

My last question is if it is possible to export a summary table created from this information to excel? It is a sizable table, covering several screens in Stata and will be visually easier to work with if I have it in excel. Selecting the table and copying as table is pasting the table in blocks as shown in Stata, I would prefer if it can be pasted with all my columns horizontally instead of split up.