SPSS export to excel
Locked
 
|
Dear list,
Is there a simpler way to work out the average correlation out of a over a hundred correlations, than exporting the Pearsons correlation output to excel then spending ages cutting and pasting all of the correlations into one column? I'd welcome any suggestions using either SPSS (I'm OK using basic syntax) or excel? Thanks Kathryn _________________________________________________________________ Try Live.com: where your online world comes together - with news, sports, weather, and much more. http://www.live.com/getstarted |
|
At 02:27 PM 4/2/2007, Kathryn Gardner wrote:
>Is there a simpler way to work out the average correlation out of a >over a hundred correlations, than exporting the Pearsons correlation >output to excel then spending ages cutting and pasting all of the >correlations into one column? Here's an SPSS solution, though I'm afraid the syntax isn't altogether simple. Usually you use OMS when you need to read procedure output back in as data. CORRELATIONS lets you write them directly, with the MATRIX OUT subcommand. See "Format of the Matrix Data File" in the *Command Syntax Reference* article on CORRELATIONS, and the listing, below, of the matrix file. (This has nothing to do with MATRIX/END MATRIX, by the way.) The VARSTOCASES was mostly written; it has features that can't be generated from the menus. This is SPSS 15 draft output, for correlations of five variables: |-----------------------------|---------------------------| |Output Created |03-APR-2007 02:31:30 | |-----------------------------|---------------------------| [TestData] CASENUM DATUM1 DATUM2 DATUM3 CODE1 CODE2 1 1.59 4.55 4.25 .03 .82 2 2.64 1.57 2.15 .27 1.25 3 5.84 5.95 5.36 -.81 .73 4 .31 -.11 3.60 1.18 2.44 5 1.03 .42 4.40 .84 1.97 6 10.06 8.38 11.88 1.29 1.83 7 1.41 1.93 1.31 .50 2.28 8 3.68 6.11 10.27 1.13 1.60 9 1.90 2.80 7.64 2.08 1.47 10 3.23 3.00 5.96 .91 .83 Number of cases read: 10 Number of cases listed: 10 *..FILE HANDLE CorrMtrx NAME='c:\MyData\SPSS\CorrMtrx.SAV'. . DATASET DECLARE CorrMtrx. * .. Compute correlations, write to an auxiliary file .. . CORRELATIONS /VARIABLES=DATUM1 DATUM2 DATUM3 CODE1 CODE2 /PRINT=TWOTAIL NOSIG /STATISTICS DESCRIPTIVES /MISSING=PAIRWISE /MATRIX OUT(CorrMtrx). Correlations |-----------------------------|---------------------------| |Output Created |03-APR-2007 02:31:31 | |-----------------------------|---------------------------| [TestData] Descriptive Statistics |------|------|--------------|--| | |Mean |Std. Deviation|N | |------|------|--------------|--| |DATUM1|3.1701|2.88604 |10| |DATUM2|3.4592|2.73226 |10| |DATUM3|5.6836|3.38498 |10| |CODE1 |.7419 |.79450 |10| |CODE2 |1.5221|.61327 |10| |------|------|--------------|--| Correlations |------|---------------|--------|--------|-------|-----|-----| | | |DATUM1 |DATUM2 |DATUM3 |CODE1|CODE2| |------|---------------|--------|--------|-------|-----|-----| |DATUM1|Pearson Corr. |1 |.864(**)|.707(*)|-.073|-.226| | |Sig. (2-tailed)| |.001 |.022 |.841 |.531 | | |N |10 |10 |10 |10 |10 | |------|---------------|--------|--------|-------|-----|-----| |DATUM2|Pearson Corr. |.864(**)|1 |.764(*)|-.126|-.394| | |Sig. (2-tailed)|.001 | |.010 |.729 |.259 | | |N |10 |10 |10 |10 |10 | |------|---------------|--------|--------|-------|-----|-----| |DATUM3|Pearson Corr. |.707(*) |.764(*) |1 |.458 |-.085| | |Sig. (2-tailed)|.022 |.010 | |.184 |.815 | | |N |10 |10 |10 |10 |10 | |------|---------------|--------|--------|-------|-----|-----| |CODE1 |Pearson Corr. |-.073 |-.126 |.458 |1 |.487 | | |Sig. (2-tailed)|.841 |.729 |.184 | |.154 | | |N |10 |10 |10 |10 |10 | |------|---------------|--------|--------|-------|-----|-----| |CODE2 |Pearson Corr. |-.226 |-.394 |-.085 |.487 |1 | | |Sig. (2-tailed)|.531 |.259 |.815 |.154 | | | |N |10 |10 |10 |10 |10 | |------|---------------|--------|--------|-------|-----|-----| ** Correlation is significant at the 0.01 level (2-tailed). * Correlation is significant at the 0.05 level (2-tailed). * .. Load from the auxiliary file, and select correlation rows .. . *--GET FILE=CorrMtrx. . DATASET ACTIVATE CorrMtrx. . DATASET COPY CorrCoef. . DATASET ACTIVATE CorrCoef. LIST. List |-----------------------------|---------------------------| |Output Created |03-APR-2007 02:31:32 | |-----------------------------|---------------------------| [CorrCoef] ROWTYPE_ VARNAME_ DATUM1 DATUM2 DATUM3 CODE1 CODE2 MEAN 3.1701112 3.4591579 5.6835616 .7418650 1.5220857 STDDEV 2.8860372 2.7322591 3.3849810 .7945001 .6132688 N DATUM1 10.0000000 10.0000000 10.0000000 10.0000000 10.0000000 N DATUM2 10.0000000 10.0000000 10.0000000 10.0000000 10.0000000 N DATUM3 10.0000000 10.0000000 10.0000000 10.0000000 10.0000000 N CODE1 10.0000000 10.0000000 10.0000000 10.0000000 10.0000000 N CODE2 10.0000000 10.0000000 10.0000000 10.0000000 10.0000000 CORR DATUM1 1.0000000 .8644103 .7074852 -.0728627 -.2256263 CORR DATUM2 .8644103 1.0000000 .7636960 -.1259758 -.3943646 CORR DATUM3 .7074852 .7636960 1.0000000 .4576258 -.0851158 CORR CODE1 -.0728627 -.1259758 .4576258 1.0000000 .4868924 CORR CODE2 -.2256263 -.3943646 -.0851158 .4868924 1.0000000 Number of cases read: 12 Number of cases listed: 12 SELECT IF ROWTYPE_ = 'CORR'. * .. Write each correlation to its own record. .. . * Identify each by names of both variables AND their . * positions in the list of variables correlated. . RENAME VARIABLES (VARNAME_=VblName1). Numeric VblNmbr1 (F4). COMPUTE VblNmbr1 = $CASENUM. . /*-- LIST /*-*/. VARSTOCASES /MAKE CORR FROM DATUM1 DATUM2 DATUM3 CODE1 CODE2 /INDEX = VblName2(CORR) /KEEP = VblName1 VblNmbr1 /NULL = KEEP. Variables to Cases |----------------------------|---------------------------| |Output Created |03-APR-2007 02:31:33 | |----------------------------|---------------------------| [CorrCoef] Generated Variables |--------|------| |Name |Label | |--------|------| |VblName2|<none>| |CORR |<none>| |--------|------| Processing Statistics |-------------|-| |Variables In |8| |Variables Out|4| |-------------|-| FORMATS CORR(F5.2). NUMERIC VblNmbr2 (F4). LEAVE VblNmbr2. IF MISSING(LAG(VblNmbr1)) OR VblNmbr1 GT LAG(VblNmbr1) VblNmbr2 = 0. COMPUTE VblNmbr2 = VblNmbr2 + 1. * .. The following LIST command is functional; it's needed, to .. . * .. make the code to calculate 'VblNmbr2' work with the .. . * .. following SELECT IF. If the listing is not desired, use .. . * .. EXECUTE instead of LIST. .. . * . * It's possible to work around the constraint, but it's difficult . * logic, not clear either to read or to write. . LIST. List |-----------------------------|---------------------------| |Output Created |03-APR-2007 02:31:33 | |-----------------------------|---------------------------| [CorrCoef] VblName1 VblNmbr1 VblName2 CORR VblNmbr2 DATUM1 1 DATUM1 1.00 1 DATUM1 1 DATUM2 .86 2 DATUM1 1 DATUM3 .71 3 DATUM1 1 CODE1 -.07 4 DATUM1 1 CODE2 -.23 5 DATUM2 2 DATUM1 .86 1 DATUM2 2 DATUM2 1.00 2 DATUM2 2 DATUM3 .76 3 DATUM2 2 CODE1 -.13 4 DATUM2 2 CODE2 -.39 5 DATUM3 3 DATUM1 .71 1 DATUM3 3 DATUM2 .76 2 DATUM3 3 DATUM3 1.00 3 DATUM3 3 CODE1 .46 4 DATUM3 3 CODE2 -.09 5 CODE1 4 DATUM1 -.07 1 CODE1 4 DATUM2 -.13 2 CODE1 4 DATUM3 .46 3 CODE1 4 CODE1 1.00 4 CODE1 4 CODE2 .49 5 CODE2 5 DATUM1 -.23 1 CODE2 5 DATUM2 -.39 2 CODE2 5 DATUM3 -.09 3 CODE2 5 CODE1 .49 4 CODE2 5 CODE2 1.00 5 Number of cases read: 25 Number of cases listed: 25 * .. Drop second copies of correlations, and correlations of .. . * variables with themselves. .. . SELECT IF VblNmbr2 GT VblNmbr1. * .. Finally, descriptive statistics on the correlations .. . TEMPORARY. COMPUTE NOBREAK = 1. MEANS TABLES=CORR BY NOBREAK /CELLS=COUNT MEAN STDDEV MEDIAN MIN MAX. Means |-----------------------------|---------------------------| |Output Created |03-APR-2007 02:31:34 | |-----------------------------|---------------------------| [CorrCoef] Case Processing Summary [suppressed - no missing data] Report CORR |-------|--|-----|--------------|------|-------|-------| |NOBREAK|N |Mean |Std. Deviation|Median|Minimum|Maximum| |-------|--|-----|--------------|------|-------|-------| |1.00 |10|.2376|.46520 |.1924 |-.39 |.86 | |-------|--|-----|--------------|------|-------|-------| |Total |10|.2376|.46520 |.1924 |-.39 |.86 | |-------|--|-----|--------------|------|-------|-------| =================== APPENDIX: Test data =================== * Test data: Five correlated variables ..................... . SET RNG=MT /* Advanced, "Mersenne twister", random-no. generator */. SET MTINDEX = 2755 /* Seed, from the phone book */. INPUT PROGRAM. . NUMERIC CASENUM (F4). . NUMERIC DATUM1 TO DATUM3 CODE1 CODE2 (F6.2). . LOOP CASENUM = 1 TO 10. . COMPUTE #COMMON1 = RV.NORMAL(0,2). . COMPUTE #COMMON2 = RV.NORMAL(0,1). . COMPUTE DATUM1 = 3 + #COMMON1 + RV.NORMAL(0,1) . . COMPUTE DATUM2 = 4 + #COMMON1 + #COMMON2 . . COMPUTE DATUM3 = 5 + #COMMON1 + #COMMON2 + RV.NORMAL(0,2). . COMPUTE #COMMON3 = RV.NORMAL(0,0.5). . COMPUTE CODE1 = 1 + #COMMON3 + RV.NORMAL(0,0.5). . COMPUTE CODE2 = 2 + #COMMON3 + RV.NORMAL(0,0.5). . END CASE. . END LOOP. END FILE. END INPUT PROGRAM. DATASET NAME TestData. |
|
|
In reply to this post by Kathryn Gardner
I'm fairly certain, that with the python this would be a fairly simple
solution, using the viewer module to mean correlation, and then the spssaux module to export the solution. Do you have V14 or V15 with python installed? Mike -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Kathryn Gardner Sent: 02 April 2007 19:27 To: [hidden email] Subject: SPSS export to excel Dear list, Is there a simpler way to work out the average correlation out of a over a hundred correlations, than exporting the Pearsons correlation output to excel then spending ages cutting and pasting all of the correlations into one column? I'd welcome any suggestions using either SPSS (I'm OK using basic syntax) or excel? Thanks Kathryn _________________________________________________________________ Try Live.com: where your online world comes together - with news, sports, weather, and much more. http://www.live.com/getstarted ________________________________________________________________________ This e-mail has been scanned for all viruses by Star. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________ ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________ |
|
|
In reply to this post by Kathryn Gardner
Hi Mike,
Thanks for your e-mail. I have SPSS 14 but have no idea what python is or whether I have it installed. Can you advise please? Thanks a lot Kathryn > Date: Tue, 3 Apr 2007 09:26:57 +0100> From: [hidden email]> Subject: Re: SPSS export to excel> To: [hidden email]> > I'm fairly certain, that with the python this would be a fairly simple> solution, using the viewer module to mean correlation, and then the> spssaux module to export the solution.> > Do you have V14 or V15 with python installed?> > Mike> > -----Original Message-----> From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of> Kathryn Gardner> Sent: 02 April 2007 19:27> To: [hidden email]> Subject: SPSS export to excel> > Dear list,> > Is there a simpler way to work out the average correlation out of a over> a hundred correlations, than exporting the Pearsons correlation output> to excel then spending ages cutting and pasting all of the correlations> into one column? I'd welcome any suggestions using either SPSS (I'm OK> using basic syntax) or excel?> Thanks> Kathryn> _________________________________________________________________> Try Live.com: where your online world comes together - with news,> sports, weather, and much more.> http://www.live.com/getstarted> > ________________________________________________________________________> This e-mail has been scanned for all viruses by Star. The> service is powered by MessageLabs. For more information on a proactive> anti-virus service working around the clock, around the globe, visit:> http://www.star.net.uk> ________________________________________________________________________> > ______________________________________________________________________> This email has been scanned by the MessageLabs Email Security System.> For more information please visit http://www.messagelabs.com/email> ______________________________________________________________________ _________________________________________________________________ Express yourself: design your homepage the way you want it with Live.com. http://www.live.com/getstarted |
|
|
In reply to this post by Kathryn Gardner
There is example syntax below the sig block. It is adapted from a demo
I use that I use to show the kinds of correlations from that can occur with random data Save all your current work, then open a new instance of SPSS. Make sure that you put warnings, etc. into the output file. <edit> <options> <viewer>. Cut-and-paste then run the syntax. Does this do what you want? If not please restate your question in more detail. Hope this helps. Art Kendall Social Research Consultants * make up some data. new file. set seed = 20070403. input program. vector item (50,f10.8). loop id = 1 to 300. loop #k = 1 to 50. compute item(#k) = rv.normal(0,1). end loop. end case. end loop. end file. end input program. formats id (f3). * change the two variable lists and scale name to match your needs. reliability variables= item1 to item50 /scale(All50) = item1 to item50 /summary=corr. Kathryn Gardner wrote: > Dear list, > > Is there a simpler way to work out the average correlation out of a over a hundred correlations, than exporting the Pearsons correlation output to excel then spending ages cutting and pasting all of the correlations into one column? I'd welcome any suggestions using either SPSS (I'm OK using basic syntax) or excel? > Thanks > Kathryn > _________________________________________________________________ > Try Live.com: where your online world comes together - with news, sports, weather, and much more. > http://www.live.com/getstarted > > |
|
|
In reply to this post by Kathryn Gardner
Dear Art,
Many thanks for your e-mail. I've played around with your syntax and couldn't get it to work as I'm only follow parts of it. However, all is not lost. The last part of your syntax produces the mean inter-item correlation (by using the reliability analysis option) which is what I wanted. I'd completely forgotten about this option. Problem solved. Thanks! Kathryn > Date: Tue, 3 Apr 2007 08:15:10 -0400> From: [hidden email]> Subject: Re: SPSS export to excel> To: [hidden email]> CC: [hidden email]> > There is example syntax below the sig block. It is adapted from a demo > I use that I use to show the kinds of correlations from that can occur > with random data> > Save all your current work, then open a new instance of SPSS. Make sure > that you put warnings, etc. into the output file. <edit> <options> > <viewer>. Cut-and-paste then run the syntax.> Does this do what you want? If not please restate your question in more > detail.> > > Hope this helps.> > Art Kendall> Social Research Consultants> > > * make up some data.> new file.> set seed = 20070403.> input program.> vector item (50,f10.8).> loop id = 1 to 300.> loop #k = 1 to 50.> compute item(#k) = rv.normal(0,1).> end loop.> end case.> end loop.> end file.> end input program.> formats id (f3).> * change the two variable lists and scale name to match your needs.> reliability variables= item1 to item50> /scale(All50) = item1 to item50> /summary=corr.> > > Kathryn Gardner wrote:> > Dear list,> > > > Is there a simpler way to work out the average correlation out of a over a hundred correlations, than exporting the Pearsons correlation output to excel then spending ages cutting and pasting all of the correlations into one column? I'd welcome any suggestions using either SPSS (I'm OK using basic syntax) or excel? > > Thanks> > Kathryn> > _________________________________________________________________> > Try Live.com: where your online world comes together - with news, sports, weather, and much more.> > http://www.live.com/getstarted> >> > > _________________________________________________________________ Express yourself: design your homepage the way you want it with Live.com. http://www.live.com/getstarted |
|
|
In reply to this post by Art Kendall-2
At 08:15 AM 4/3/2007, Art Kendall wrote:
>* change the two variable lists and scale name to match your needs. >reliability variables= item1 to item50 > /scale(All50) = item1 to item50 > /summary=corr. CLEARLY the way to go. Thanks, Art. Richard |
|
You're welcome.
I'll save your approach though. The next time I'm trying to demo the range of correlations from completely random data, I may adapt it to do a frequencies on rounded correlations. Art Richard Ristow wrote: > At 08:15 AM 4/3/2007, Art Kendall wrote: > >> * change the two variable lists and scale name to match your needs. >> reliability variables= item1 to item50 >> /scale(All50) = item1 to item50 >> /summary=corr. > > CLEARLY the way to go. Thanks, Art. > Richard > >
Art Kendall
Social Research Consultants |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |