firm address to panel data

[Mo Gad]

dear all,

I have data about companies' addresses, zip code, and state as follows. Where begQ and endQ is the period during which the company was located in the referenced address.
There are some gaps in this data and it goes back to 2007 only. I want to create a panel dataset between 2000q1 and 2016q4 by using older quarter data to backfill (e.g. if 2006q4 address is missing, I need to use 2007q1 data) and the newest quarter to forward fill (e.g. if 2016q4 address is missing, I need to use 2016q3 data).

* Example generated by -dataex-. To install: ssc install dataex
clear
input int id long zip str48 address str2 state str6(begq endq) byte n
1034  7024 "1 executive dr"                                   "nj" "2007q2" "2007q2" 3
1034  8807 "grande commons 440 u s highway 22 east 3rd floor" "nj" "2007q4" "2013q3" 3
1034  7940 "5 giralda farms"                                  "nj" "2014q1" "2016q1" 3
1037 18951 "1900 am drive"                                    "pa" "2007q2" "2013q1" 1
1038 64105 "920 main st"                                      "mo" "2007q2" "2016q1" 1
1039 10016 "one park avenue"                                  "ny" "2007q2" "2012q4" 1
1040 10604 "777 westchester avenue"                           "ny" "2007q2" "2011q1" 2
1040 23227 "p o box 15060"                                    "va" "2011q2" "2011q2" 2
1041 80901 "p o box 370"                                      "co" "2007q2" "2010q2" 2
1041 85040 "3414 south 5th street"                            "az" "2011q1" "2012q2" 2
end

Desired dataset should look as follows:

ID Date Address ZIP State 1034 2000q1 1 executive dr 7024 nj 1034 2000q2 1 executive dr 7024 nj 1034 2000q3 1 executive dr 7024 nj 1034 2000q4 1 executive dr 7024 nj 1034 2001q1 1 executive dr 7024 nj 1034 2001q2 1 executive dr 7024 nj 1034 2001q3 1 executive dr 7024 nj 1034 2001q4 1 executive dr 7024 nj 1034 2002q1 1 executive dr 7024 nj 1034 2002q2 1 executive dr 7024 nj . . . . . . . . . 1034 2007q2 1 executive dr 7024 nj 1034 2007q3 grande commons 440 u s highway 22 east 3rd floor 8807 nj 1034 2007q4 grande commons 440 u s highway 22 east 3rd floor 8807 nj . . . . . 1034 2013q3 grande commons 440 u s highway 22 east 3rd floor 8807 nj . . . . . 1034 2013q4 5 giralda farms 7940 nj 1034 2014q1 5 giralda farms 7940 nj . . . . . 1034 2016q4 5 giralda farms 7940 nj

Thank you for help

Gad

[Jesse Wursten]

You could generate a dataset without addresses, but complete in firm and dates (ie the first two columns of your bottom dataset) and then use rangejoin to assign addresses.

[Mo Gad]

Many thanks Jesse Wursten