Split to multiple files, merge and append

[Edward Wu]

I have two files, each with 3 variables.

The first one has date, city, and score. The second one has date, city and ret. Each has 49 cities.

I want to split each files to 49 stata files, by city.

Then merge the 49 score files with the 49 ret files by city and date.

Then merge each of the 49 files with a fulldate file (only one variable date with all the 365/366 dates per year).

merge 1:1 date using fulldate



Then for the 49 files, each

replace city="cityname" if missing(city)


Then append all the 49 files to one file.

Anyone can help?

Great thanks!

[Clyde Schechter]

This makes no sense to me. It seems you would get the same result just directly merging the first two data sets on city and date, and then doing a -merge m:1 date using fulldate_file- without having to break things up and put them back together. What am I missing?

[Edward Wu]

If so, I won't be able to fill out the missing dates by city.

replace city="cityname" if missing(city)

I need each city with all 365/366 dates every year.

If only simply merged those 3, there will be missing dates for all the cities, and I don't know how to identify and replace the blank to each cityname.

Thanks! Is there a simple way?

[William Lisowski]

I think that the following code may indicate a useful direction to efficiently accomplish what you hope without all the effort you describe.

cls

// make up test data in temporary datasets

clear
input str5 city float(date score)
"city1" 1960 10
"city1" 1961 11
"city2" 1960 20
"city3" 1960 30
end
tempfile ds1
save `ds1'

clear
input str5 city float(date ret)
"city1" 1960 110
"city1" 1961 111
"city2" 1960 120
"city3" 1961 131
end
tempfile ds2
save `ds2'

clear
input float date
1960
1961
1963
end
tempfile fulldate
save `fulldate'

// the real work begins here, substitute actual dataset names

use `ds1', clear
merge 1:1 city date using `ds2'
drop _merge
merge m:1 date using `fulldate'
drop _merge
sort city date
list, noobs sepby(city)

fillin city date
drop _fillin
drop if missing(city)

sort city date
list, noobs sepby(city)

. list, noobs sepby(city)

  +----------------------------+
  |  city   date   score   ret |
  |----------------------------|
  |         1963       .     . |
  |----------------------------|
  | city1   1960      10   110 |
  | city1   1961      11   111 |
  |----------------------------|
  | city2   1960      20   120 |
  |----------------------------|
  | city3   1960      30     . |
  | city3   1961       .   131 |
  +----------------------------+

. list, noobs sepby(city)

  +----------------------------+
  |  city   date   score   ret |
  |----------------------------|
  | city1   1960      10   110 |
  | city1   1961      11   111 |
  | city1   1963       .     . |
  |----------------------------|
  | city2   1960      20   120 |
  | city2   1961       .     . |
  | city2   1963       .     . |
  |----------------------------|
  | city3   1960      30     . |
  | city3   1961       .   131 |
  | city3   1963       .     . |
  +----------------------------+

[Edward Wu]

Fantastic! I didn't know the fillin...

Great Thanks!