Expand data based on a single variable

[Rochelle Zhang]

Dear Statalisters,

Below I present my pretend data, desired outcome, and my code. My code runs, but not the desired outcome. Could you help ?

my pretend data - gvkey is firm unique id.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(gvkey sic) byte sale
9375 100 10 
8596 115 22 
8596 6552 31 
8596 7374 36 
end

what I want

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(gvkey sic) str2 sale
9375 100 "10" 
9375 115 "." 
9375 6552 "." 
9375 7374 "." 
8596 100 "." 
8596 115 "22" 
8596 6552 "31" 
8596 7374 "36" 
end

basically I expand each gvkey to incorporate all distinct sic for the entire sample.

my code

clear
input int(gvkey sic) byte sale
9375 100 10 
8596 115 22 
8596 6552 31 
8596 7374 36 
end

tempfile main
save `main'



keep sic 
duplicates drop 

tempfile siclist
save `siclist'

use  `main'
cross using `siclist'

Thank you,
Rochelle

[Clyde Schechter]

I think this does it:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(gvkey sic) byte sale
9375 100 10 
8596 115 22 
8596 6552 31 
8596 7374 36 
end

fillin gvkey sic
drop _fillin
gsort -gvkey sic

[Rochelle Zhang]

This is the most efficient way of doing it.

Thanks Clyde