One line code for performing arithmetic operation (here multiplication) while merging two datasets

[Aishwary Trivedi]

Suppose, I have two datasets given below, now how can I merge the datasets so that resulting values in the merged dataset gets multiplied with each other. How should i go about it? For example: Afg (2004) = 48[1st dataset] * Afg (1989) = 64 [2nd dataset], Algeria (1989)= 8{1st dataset] * Algeria(1989) = 13[2nd dataset] and so on.

year	Afg	Albania	Algeria	Angola	Anguilla
1989	39		8	0	3
1990	39		9	0	3
1991	39		18	0	3
1992	39	75	22	0	3
1993	39	102	23	0	3
1994	39	95	35	0	3
1995	37	93	48	0	3
1996	48	104	55	0	3
1997	48	149	58	0	3
1998	48	151	59	0	3
1999	47	138	67	3	3
2000	47	144	75	10	3
2001	48	143	77	22	3
2002	47	140	80	44	3
2003	49	122	77	75	3
2004	48	103	72	84	3

	Afg	Albania	Algeria	Angola	Anguilla
1989			13
1990			16	0	52
1991		6	20	0	54
1992		18	26	0	55
1993		33	31	0	58
1994		41	40	0	60
1995		44	52	0	60
1996		50	62	0	63
1997		66	65	0	63
1998		80	68	0	64
1999		80	70	1	65
2000		80	70	3	68
2001		83	73	7	72
2002		89	74	15	72
2003		90	78	30	74
2004	64	92	81	44	77

[Clyde Schechter]

I cannot think of any way to do this as a one-liner. The following code will get the result you are looking for:

use dataset1, clear
ds year, not
local countries `r(varlist)'
rename (`countries') =1
merge 1:1 year using dataset2
foreach c of local countries {
    replace `c' = `c'*`c'1
    drop `c'1
}

That said, it is likely that whatever you plan to do with this data will be better accomplished in Stata by first reshaping both of these data sets to a long layout. That would eliminate the need to loop over countries to calculate the product. Tha looks like this:

use dataset1, clear
ds year, not
rename (`r(varlist)') value=
reshape long value, i(year) j(country) string

frame create dataset2
frame dataset2 {
    use dataset2
    ds year, not
    rename (`r(varlist)') value=
    reshape long value, i(year) j(country) string
}

frlink 1:1 country year, frame(dataset2)
replace value = value*frval(dataset2, value)
frame drop dataset2
drop dataset2

While this is longer and more complicated than the first approach, it has the advantage of leaving you with a data set that will be much easier to work with in Stata than the wide layout you are starting with--there is only a handful of commands in Stata that work well with wide layout data.

By the way, your explanation of what you want is confusing. Where you say "Afg (2004) = 48[1st dataset] * Afg (1989) = 64," I think you mean "Afg (1989) = 48[1st dataset] * Afg (1989) = 64"

[Fei Wang]

Aishwary, if the row and column structures are identical in the two datasets, you may transform both into matrices, multiply them element by element, and then transform the multiplication matrix back into a dataset.

use data1, clear
mkmat varlist, mat(data1)

use data2, clear
mkmat varlist, mat(data2)

mat M = hadamard(data1, data2)

svmat M, n(matcol)

varlist is the list of all country names in your columns (e.g., Afg-Zimbabwe). The multiplication result will appear in data2.

But ultimately, you'd better transform your dataset as Clyde suggests, as the current form is inconvenient for many operations in Stata.