Hi, I am having trouble in finding the missing quarter for every company. Companies have separate IDs. As shown in "Data I Have", I have the data on companies, but there are some years where I do not have the data for each of the 4 quarters of each year. Please see the below example of the data
DATA I HAVE
*The code for the above input can be found at the end of the post.
Now What I want from stata is to generate New_Year and New_Quarter based on the missing quarters from "data I have". For example, the 1st quarter of 2000 is missing, therefore, I want Stata to generate a new observation where the year is 2000 and the Quarter is 1. Since the 1st quarter of 2000 missing.
Furthermore, if there is a full year which is missing, like in the "data I have" you can see the year 2002 is missing, then I want Stata to generate 4 quarters for 2002 as shown in "Data I want".
DATA I WANT
Any help would be greatly appreciated.
Thanks
Ahmed
DATA I HAVE
Old_year | Old_Quarter | Count | ID |
2000 | 2 | 1 | 1 |
2000 | 3 | 2 | 1 |
2000 | 4 | 3 | 1 |
2001 | 1 | 4 | 1 |
2001 | 3 | 5 | 1 |
2001 | 4 | 6 | 1 |
2003 | 1 | 7 | 1 |
2003 | 2 | 8 | 1 |
Now What I want from stata is to generate New_Year and New_Quarter based on the missing quarters from "data I have". For example, the 1st quarter of 2000 is missing, therefore, I want Stata to generate a new observation where the year is 2000 and the Quarter is 1. Since the 1st quarter of 2000 missing.
Furthermore, if there is a full year which is missing, like in the "data I have" you can see the year 2002 is missing, then I want Stata to generate 4 quarters for 2002 as shown in "Data I want".
DATA I WANT
New_Year | New_Quarter | New_Count | New_ID | Old_year | Old_Quarter | Count | ID |
2000 | 1 | 1 | 1 | . | . | . | |
2000 | 2 | 2 | 1 | 2000 | 2 | 1 | 1 |
2000 | 3 | 3 | 1 | 2000 | 3 | 2 | 1 |
2000 | 4 | 4 | 1 | 2000 | 4 | 3 | 1 |
2001 | 1 | 5 | 1 | 2001 | 1 | 4 | 1 |
2001 | 2 | 6 | 1 | . | . | . | . |
2001 | 3 | 7 | 1 | . | . | . | . |
2001 | 4 | 8 | 1 | 2001 | 4 | 5 | 1 |
2002 | 1 | 9 | 1 | . | . | . | . |
2002 | 2 | 10 | 1 | . | . | . | . |
2002 | 3 | 11 | 1 | . | . | . | . |
2002 | 4 | 12 | 1 | . | . | . | . |
2003 | 1 | 11 | 1 | 2003 | 1 | 7 | 1 |
2002 | 2 | 12 | 1 | 2002 | 2 | 8 | 1 |
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input Old_year Old_Quarter count ID 2000 2 1 1 2000 3 2 1 2000 4 3 1 2001 1 4 1 2001 3 5 1 2001 4 6 1 2003 1 7 1 2003 2 8 1 end
Any help would be greatly appreciated.
Thanks
Ahmed
Comment