Taking statistics from cells and putting into Excel

[Matthew Boswell]

I have several hundred correlations that need to be put into a "digestable"
format.  Rather than fat-fingering all the values into Excel myself, I was
trying to see if SPSS can read the Pearson's value from the table into an
Excel file.  My data in SPSS  looks like this:

CASE    YEAR    WW  XX  YY  ZZ
A       1980    10  11   9   5
A       1981     8   5  10   7
A       1982     9   7  12   6
B       1980    12   5   6   5
B       1981     8   4   4   4
B       1982    19   3   2   7

My correlations look like this, with file split by CASE:

SORT CASES BY CASE .
SPLIT FILE
  SEPARATE BY CASE .
CORRELATIONS
  /VARIABLES=WW XX
  /PRINT=TWOTAIL NOSIG
  /MISSING=PAIRWISE .

CORRELATIONS
  /VARIABLES=YY ZZ
  /PRINT=TWOTAIL NOSIG
  /MISSING=PAIRWISE .

(basically, i am checking the validity of one set of data- vars WW and YY
with another set of data, XX and ZZ, across 50 cases for about 20 variables)

I took a look in Raynald's book, and it seemed like there might be something
to do with the OMS commands, but my attempts never worked out.  Any help
would be appreciated!!

Thanks!
Matt B

[Maguin, Eugene]

Matthew,

'Fat-fingering' is an interesting characterization of data re-entery by
hand! Haven't heard that before.

Anyway, it sounds like you have tried OMS, which was one of my first
thoughts, but had trouble with it. Why not post the code you used to the
list and see if somebody can help you with that. By the way, make your job
easier and use this syntax and not what you have.

CORRELATIONS
  /VARIABLES=WW with XX
  /PRINT=TWOTAIL NOSIG
  /MISSING=PAIRWISE .

What you propose gives you a two by two table. You only care about one cell
in the table, either the upper right or the lower left. The rest are junk.


My second thought is this. Why not compute the correlation directly via a
series of compute statements and the Aggregate command. Based on what you
posted you have four variables, WW XX YY ZZ, and you want to know the
correlations of WW with XX and YY with ZZ by year. One formula to use, from
Cohen et al. (1983), p. 37, 2nd edition, equation 2.3.6, is

R = Sum(x*y)/Square root(Sum(x*x)*Sum(y*y))

(There is a judgement to make here about how large the squared terms will
get because overflow can be a problem. If it is judged to be a problem then
another formula to use is

R = Sum(x*y)/(sd(x)*sd(y)*n) (equation 2.3.5, same page).

Which exploits the fact that Aggregate will compute standard deviations. The
main question I have out that computations (and which can be answered by
looking at the algorithms is whether the sd function is computed with n or
(n-1) in the denominator. Cohen shows that n should be used.)

So, if using the first formula, you compute x*y, x*x, and y*y) and aggregate
by year and divide the aggregated quantities as shown. This gives you the
listwise missing values. I notice you specifiy pairwise missing. Doing that
is somewhat more complicated because you need to keep track of the number of
cases going into each quantitity (e.g., x*y).

Gene Maguin

[Peck, Jon]

You can export a table directly into Excel from the Viewer via File/Export.

Alternatively, you can use OMS to save the table as an html file that can be read by Excel.

Depending on how you want this organized, various output options from CORRELATIONS or pivoting the table may make this easier.

Of course you could also read this table with Python programmability and make any comparisons you want programmatically there.

Regards,
Jon Peck


-----Original Message-----
From: SPSSX(r) Discussion on behalf of Matthew Boswell
Sent: Wed 12/20/2006 12:00 PM
To: [hidden email]
Subject:      [SPSSX-L] Taking statistics from cells and putting into Excel
 
I have several hundred correlations that need to be put into a "digestable"
format.  Rather than fat-fingering all the values into Excel myself, I was
trying to see if SPSS can read the Pearson's value from the table into an
Excel file.  My data in SPSS  looks like this:

CASE    YEAR    WW  XX  YY  ZZ
A       1980    10  11   9   5
A       1981     8   5  10   7
A       1982     9   7  12   6
B       1980    12   5   6   5
B       1981     8   4   4   4
B       1982    19   3   2   7

My correlations look like this, with file split by CASE:

SORT CASES BY CASE .
SPLIT FILE
  SEPARATE BY CASE .
CORRELATIONS
  /VARIABLES=WW XX
  /PRINT=TWOTAIL NOSIG
  /MISSING=PAIRWISE .

CORRELATIONS
  /VARIABLES=YY ZZ
  /PRINT=TWOTAIL NOSIG
  /MISSING=PAIRWISE .

(basically, i am checking the validity of one set of data- vars WW and YY
with another set of data, XX and ZZ, across 50 cases for about 20 variables)

I took a look in Raynald's book, and it seemed like there might be something
to do with the OMS commands, but my attempts never worked out.  Any help
would be appreciated!!

Thanks!
Matt B

[Simon Phillip Freidin]

OMS
  /SELECT TABLES
  /IF COMMANDS = ["Correlations"]
      SUBTYPES = ["Correlations"]
  /DESTINATION FORMAT = SAV NUMBERED = TableNo
   OUTFILE = "corr.sav"
   VIEWER = NO.
SORT CASES BY CASE .
SPLIT FILE
   SEPARATE BY CASE .
CORRELATIONS
   /VARIABLES=WW XX
   /PRINT=TWOTAIL NOSIG
   /MISSING=PAIRWISE .

CORRELATIONS
   /VARIABLES=YY ZZ
   /PRINT=TWOTAIL NOSIG
   /MISSING=PAIRWISE .
omsend.

get file = "corr.sav" /drop=Subtype_ Label_ .
exe.

If want the the output tables in excel, you can use FORMAT = TABTEXT
and OUTFILE = "CORR.XLS" (& remove the numbered and drop commands) -
but you'll be cutting and pasting a lot of cells to organize the
data. SAV file format is preferable. I'd rearrange the SAV file to
extract the data for the spreadsheet using select if, casestovars etc

cheers
Simon



On 21/12/2006, at 5:00 AM, Matthew Boswell wrote:

> I have several hundred correlations that need to be put into a
> "digestable"
> format.  Rather than fat-fingering all the values into Excel
> myself, I was
> trying to see if SPSS can read the Pearson's value from the table
> into an
> Excel file.  My data in SPSS  looks like this:
>
> CASE    YEAR    WW  XX  YY  ZZ
> A       1980    10  11   9   5
> A       1981     8   5  10   7
> A       1982     9   7  12   6
> B       1980    12   5   6   5
> B       1981     8   4   4   4
> B       1982    19   3   2   7
>
> My correlations look like this, with file split by CASE:
>
> SORT CASES BY CASE .
> SPLIT FILE
>   SEPARATE BY CASE .
> CORRELATIONS
>   /VARIABLES=WW XX
>   /PRINT=TWOTAIL NOSIG
>   /MISSING=PAIRWISE .
>
> CORRELATIONS
>   /VARIABLES=YY ZZ
>   /PRINT=TWOTAIL NOSIG
>   /MISSING=PAIRWISE .
>
> (basically, i am checking the validity of one set of data- vars WW
> and YY
> with another set of data, XX and ZZ, across 50 cases for about 20
> variables)
>
> I took a look in Raynald's book, and it seemed like there might be
> something
> to do with the OMS commands, but my attempts never worked out.  Any
> help
> would be appreciated!!
>
> Thanks!
> Matt B