st: RE: Making a matrix out of three variables

["Cohen, Elan" <cohened@upmc.edu>]

I'm going to assume that C is numeric since 1) Stata matrices cannot be strings (outside of -mata-), and 2) you state that your variable is numeric (despite what you show).  I'm also using nested loops, but unless your matrix is really large, this shouldn't run too slowly.

See below for code.

HTH,

- Elan


clear
input str2 A str2 B C
A1 A1 1
A1 A2 2
A1 A4 3
A1 A5 4
A2 A2 5
A2 A1 6
A2 A3 7
end

bys A B: assert _N==1
qui levelsof A, clean
loc rownames `r(levels)'
qui levelsof B, clean
loc colnames `r(levels)'
loc r : word count `rownames'
loc c : word count `colnames'
mat M = J(`r',`c',0)
mat rownames M = `rownames'
mat colnames M = `colnames'

foreach r in `rownames' {
  foreach c in `colnames' {
    su C if A=="`r'" & B=="`c'", meanonly
    if `r(N)'>0 {
      mat M[rownumb(M,"`r'"), colnumb(M,"`c'")] = r(mean)
    }
  }
}


-----Original Message-----
From: owner-statalist@hsphsun2.harvard.edu [mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Eilya Torshizian
Sent: Thursday, January 16, 2014 15:03
To: statalist@hsphsun2.harvard.edu
Subject: st: Making a matrix out of three variables

Dear Statalist members,

I need to convert three variables to a matrix. The values of variable A and B should be used as row and column names of the output matrix.  Let's assume three columns of data, where A, B and C are the name of my numeric variables,

A      B      C
A1 | A1 | C1
A1 | A2 | C2
A1 | A4 | C3
A1 | A5 | C4
A2 | A2 | C5
A2 | A1 | C6
A2 | A3 | C7

I need to convert these to the following matrix,

      A1 A2 A3 A4 A5
A1 C1 C2  0   C3  0
A2 C6 C5  C7  0   0

As I have a wide range of observations, I would like to avoid nested loops.

Thanks,
Eilya.


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


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