Hello Statlist-Forum,

I am in need for the technical commands for two datasets.

The first problem: I have a dataset that gives me the amount of e.g. hours of sun per day in governorate X at day X. My time periode spreads over several years, some days are missing.
What I would like to do, is to create a variable that sums up the hours of sun per month per governorate. Concrete, so I can tell in Feburary in 2006 in California, there were 50 hours of sun.
What would be the code to sum up the hours of sun per month, by governorate. My problem really is the month. I could ofc manually make a code for all month individually, but since my dataset covers over 4 years, this would be at least 48 time definitions. Is there any way that Stata automatically devides into the month?


Date Hours of sun Governorate
01.01.2005 4 A
03.01.2005 5 B
21.05.2006 8 A
31.07.2006 0 A

Secondly, I would like to merge this monthly score with a dataset that contains, let say, health information for individuals. I have information about the health status of the individual at time t (this is the same for all individuals, they have all been measuerd at the end of 2006), the governorate they live in and their date of birth.
I now want to regress the hours of sun on the health status. However, I only want to use the hours of sun AFTER the birth (obviously).
So lets say my Individuals is born April 2005, then the hours of sun in January 2005 should not be included in the "sun score". Meaning I somehow need to sum up all months that happened after the date of birth until the end of the survey.
Very similar, I would like to create another score that counts the number of hours of sun 9-month before the indiviudual was born. The same code like above should be applicable here I think.
Individual Date of Birth Governorate Hours of Sun after Birth Hours of Sun before Birth
1 04.05.2004 A 100 40
2 02.02.2005 C 110 20
3 17.10.2005 B 50 25
4 19.09.2006 A 200 9


So first, I need to aggregated my data on the monthly level per governorate. Secondly, I want to create a score that is unique for each individual, that corresponds to its individual hours of sun.

I hope my execution is clear and somebody is able to help me!