st: Estimating new variable from multiple datasets

["Pavlos C. Symeou" <p.symeou@lmu.de>]

Dear Statalisters,

Some time ago I wrote to Statalist about the problem below but I 
have been unsuccessful in receiving any suggestions. I am trying 
herein to simplify my problem in the hope that you can help. I have 
250 company files with their patents of total size of 12Gbytes (see 
below a sample). I want to use information from each company's 
patents and their citations and create a new dataset which will 
consist of all companies in a panel format with a new variable 
whose estimation I describe below.
Let me give you an example. Company "Acer" which operates in 
industrial sector 3456 (mother_SIC) has 100,000 patents published 
between 1960-2009 (year). Certain years may have multiple patents. 
Every patent is assigned multiple patent numbers (patent_number) 
which uniquely identify it. Each of that patent can be used in at 
least one industrial sector (patent_Sic). Every patent may cite 
multiple patents (citation).
The data below tell that, ACER in year 1994 published two patents 
which were assigned 20 numbers. Each patent is used in 20 
industries (patent_Sic). In each of the two patents, ACER is citing 
20 other patents, which may belong to ACER or other companies, 
which themselves appear in a similar fashion as observations (in 
ACER's or) another company's file.
name mother_sic Year patent_Sic_1 patent_Sic_20 patent_number_1 
patent_number_20 citation_1 citation_20
ACER   3456          1994   
3661                                     TW231391-A         
TW231391-B            US231391-A  CY231391-A
ACER   3456          1994   3417 5472                           
DR231342-A          TA231342-C             FR231342-A   CY2634542-B
ACER   3456          1995   3577 3572                           
BR231342-B           PAT231342-A          TW231342-A  SE231342-A
I want to estimate a new numerical variable ("convergence") for 
ACER which will measure how much its patents' SIC sectors and the 
related cited patents' SIC sectors deviate from ACER's industrial 
sector (mother_SIC) based on the following formula. Take for 
example the two patents in 1994 above. The value of "convergence" 
for the year 1994 should be:
{[0.90 * (a1 +b1 +c1) + 0.10 * (d1 + e1 + f1)] + [0.90 * (a2 +b2 
+c2) + 0.10 * (d2 + e2 + f2)] } / n
where 1,2,...,n is the number of patents that ACER published in 
1994 and a, b, c, d, e, f are:
For every ACER patent published in 1994:
a) the proportion of all patent_SICs whose 1st digit is different 
than the 1st digit of mother_SIC, multiplied by 3;
b) the proportion of all patent_SICs whose 1st digit is the same as 
the 1st digit of mother_SIC but the 2nd digit is different than the 
2nd digit of mother_SIC, multiplied by 2;
c) the proportion of all patent_SICs whose first 2 digits are the 
same as the first two digits of mother_SIC but they have a 
different 3rd digit, multiplied it 1;
d) the proportion of all cited patents' patent_SICs whose 1st digit 
is different than the 1st digit of ACER's mother_SIC, multiplied by 3;
e) the proportion of all cited patents' patent_SICs whose 1st digit 
is the same as the 1st digit of ACER's mother_SIC but the 2nd digit 
is different than the 2nd digit of mother_SIC, multiplied by 2;
f) the proportion of all cited patents' patent_SICs whose first 2 
digits are the same as the first 2 digits of ACER's mother_SIC but 
they have a different 3rd digit, multiplied by 1;
Notes:
A) For a, b, c the search will be done inside ACER's file. For d, 
e, f the search will be done inside all available companies' files, 
including ACER and only for the years 1994 and earlier. This is 
because cited patents are already published.
B) A citation number in ACER's file will appear as a patent number 
in another company's file (or in ACER's file if the company is 
citing another patent it owns).
C) Since a patent can be assigned multiple numbers, the search 
intended to match the citation with the patent number must go 
through all columns that have patent numbers.
D) It is possible that a cited patent does not belong to our sample 
companies. This should not terminate the loop but go on with the 
next citation.
The output should look like this:

Company    Year        convergence
ACER            1994      2.3
ACER            1995      2.1
ACER            1996      2.5
......
......

Any help will be very appreciated.

Best,

Pavlos
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/