dataset combo question

[Thomas Anderson]

Salutations,

I have a merger/cross issue that I haven't been able to figure out. I have an enterprise dataset that has companies and states, organized like this

Company ID State_ID Facilities
A 1 12
A 6 39
A 37 2
B 2 10
B 18 4
B 24 1
B 51 11
etc..

I also have a list of all the states

State_ID Abbr
1 AL
2 AK
4 AZ
etc...

I want to combine them so that every company ID has all the states

Company ID State_ID Facilities Abbr
A 1 12 AL
A 2 . AK
A 4 . AZ
A 6 39 CA
A 8 . CO
...
A 37 2 NC
...
B 1 . AL
B 2 10 AK
B 18 4 AZ
etc...

I think this should be possible with merge or cross, but I haven't been able to figure it out. Can anyone help ?

Thanks

[William Lisowski]

Does something like this

use companies.dta
merge 1:m State_ID using states.dta

not work?

Added in edit: now that I've managed to get your example data into Stata, the answer is no, it does not work.

While you wait for a better answer, please take a few moments to review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question. It is particularly helpful to copy commands and output from your Stata Results window and paste them into your Statalist post using code delimiters [CODE] and [/CODE], and to use the dataex command to provide sample data, as described in section 12 of the FAQ.

The more you help others understand your problem, the more likely others are to be able to help you solve your problem.

[William Lisowski]

// read example data

* Example generated by -dataex-. For more info, type help dataex
clear
input str3 company_id byte(state_id facilities)
"A"    1 12
"A"    6 39
"B"    2 10
"B"   18  4
end
save ~/Downloads/companies, replace

* Example generated by -dataex-. For more info, type help dataex
clear
input byte state_id str2 abbr
1 "AL"
2 "AK"
4 "AZ"
6 "CA"
18 "IN"
end
save ~/Downloads/states, replace

// generate a fake company in every state
use ~/Downloads/states, clear
drop abbr
generate company_id = "DUMMY"

// add it to the company data
append using ~/Downloads/companies.dta

// expand the company data to every combination of company and state
fillin company_id state_id
drop if company_id=="DUMMY"

// now merge the state abbreviations
merge m:1 state_id using ~/Downloads/states

sort company_id state_id
list, noobs sepby(company_id) abbreviate(16)

. list, noobs sepby(company_id) abbreviate(16)

  +-------------------------------------------------------------------+
  | state_id   company_id   facilities   _fillin   abbr        _merge |
  |-------------------------------------------------------------------|
  |        1            A           12         0     AL   Matched (3) |
  |        2            A            .         1     AK   Matched (3) |
  |        4            A            .         1     AZ   Matched (3) |
  |        6            A           39         0     CA   Matched (3) |
  |       18            A            .         1     IN   Matched (3) |
  |-------------------------------------------------------------------|
  |        1            B            .         1     AL   Matched (3) |
  |        2            B           10         0     AK   Matched (3) |
  |        4            B            .         1     AZ   Matched (3) |
  |        6            B            .         1     CA   Matched (3) |
  |       18            B            4         0     IN   Matched (3) |
  +-------------------------------------------------------------------+

[Thomas Anderson]

WIlliam,

Thank you very much. The code works perfectly.

Thomas