SPSSX Discussion

converting data from excel to spss

classic Classic list List threaded Threaded
♦
♦
 3 messages Options
Liat Lev-Shalem
Reply | Threaded
Open this post in threaded view
♦
♦
|

converting data from excel to spss

Liat Lev-Shalem

Dear List members,

 

I want to convert a data file from excel to spss. I have a large number of cases (>1000) with 120 variables for each case. The data is arranged in the excel file with 1 row for every variable, so that every subject has 120 rows (row1 to row 120 for subject 1, rows 121-240 for subject2, etc.). I want to create a data file that would look like a regular data file- i.e.: all 120 variables presented in the same row. How do I do it?

 

Many thanks

 

Liat Lev-Shalem

 



--
ליאת לב-שלם
עיבודים סטטיסטיים וסיוע מחקרי
טלפון: 054-5452848
פקס: 03-9229807
Liat Lev-Shalem
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: converting data from excel to spss

Liat Lev-Shalem
Many thanks Jason
 
haven't tried it yet but it looks like a good solution for my problem.
 
All the best,
 
Liat

On Tue, Jun 16, 2009 at 11:25 AM, Jason Ferris <[hidden email]> wrote:

Hi Liat,

If I understand your description of your excel datasheet you have

Var1 11

Var2 12

Var3 13

Var1 21

Var2 22

Var3 23

Var1 31

Var2 32

Var3 33

 

Where in column 1 var1-var3 represent your 120 variables in each row and column 2 represent responses (in this case the first three responses are for subject 1 (11 12 13) and the second three are for subject 2 (21 22 23)).

 

What I would suggest is arranging your data in excel (I would have used Stata to do this) I have attached an excel spread sheet which has two macro (this are listed below in case attachments are dropped off.

 

In your first spread sheet (orig) has data similar to above in the second spread sheet (final) has data the way you want it (if I have it correct).

 

The first macro – arrange_data basically transposes each grouping of your 120 variables with the subjects response (ie., puts them across columns – so that each row is now a subject)

 

The second macro delete_rows deletes all the variable name rows (excluding the first one).  In this macro you will have to indicate the n-1 row (which I current have set to 5).  I.e., if you have 2002 rows of data replace 5 to 2001 and then run the macro.

 

Once you have the data in excel in the appropriate form you can readily import in through SPSS’s menus

 

HTH

Jason

 

Sub arrange_data()

' arrange_data Macro

'

    Sheets("final").Select

    Range("A1").Select

    Sheets("orig").Select

    Range("A1").Select

   

Do While IsEmpty(ActiveCell.Offset(0, 1)) = False

    ActiveCell.Range("A1:B3").Select

    Selection.Copy

    Sheets("final").Select

    ActiveCell.Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=True

    ActiveCell.Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(1, 0).Range("A1").Select

    Sheets("orig").Select

    Application.CutCopyMode = False

    Selection.Delete Shift:=xlUp

    ActiveCell.Select

    Loop

 

 

End Sub

 

Sub delete_rows()

' delete every other row with variable names

 

 For i = 5 To 2 Step -2

   Rows(i).Delete

 Next i

 

End Sub

 

 

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Liat Lev-Shalem
Sent: Tuesday, 16 June 2009 4:45 PM
To: [hidden email]
Subject: converting data from excel to spss

 

Dear List members,

 

I want to convert a data file from excel to spss. I have a large number of cases (>1000) with 120 variables for each case. The data is arranged in the excel file with 1 row for every variable, so that every subject has 120 rows (row1 to row 120 for subject 1, rows 121-240 for subject2, etc.). I want to create a data file that would look like a regular data file- i.e.: all 120 variables presented in the same row. How do I do it?

 

Many thanks

 

Liat Lev-Shalem

 



--
ליאת לב-שלם
עיבודים סטטיסטיים וסיוע מחקרי
טלפון: 054-5452848
פקס: 03-9229807

------------------------------------------
DISCLAIMER: This message (including any attachments) is intended solely for the addressee(s) named and may contain confidential or privileged information. 
If you are not the intended recipient, please delete it and notify the sender. 
Views expressed in this message are those of the individual sender,and are not necessarily the views of the Turning Point Alcohol and Drug Centre Inc (ABN: 17 302 055 629).  

Turning Point Alcohol and Drug Centre Inc

Although this message and any attachments have been scanned for viruses by 'Trend Micro InterScan' at the time of sending, 
you are advised to rescan on receipt.

The whole or parts of this email may be subject to copyright of Turning Point Alcohol and Drug Centre Inc (ABN: 17 302 055 629), and/or third parties. 
You can only re-transmit, distribute or use the material if you are authorised to do so.

Please consider the environment before printing this email or attachments.



--
ליאת לב-שלם
עיבודים סטטיסטיים וסיוע מחקרי
טלפון: 054-5452848
פקס: 03-9229807
Garry Gelade
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: converting data from excel to spss

Garry Gelade
Dear Liat
 
There is another way. First read your Excel data into SPSS just as it is.  Then in SPSS calculate a unique id for each subject (if you haven't already got one).
Something like COMPUTE id = mod($casenum, 120) should do that if you need to.
 
Then use the Data Restructure Wizard in SPSS (available by clicking Data then Restructure) to transpose the rows to columns - you'll want to select the dialogue "Restructure selected cases into variables" on the front page of the Wizard, and follow the screens from there.
 
Garry Gelade
Business Analytic Ltd

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Liat Lev-Shalem
Sent: 16 June 2009 12:28
To: [hidden email]
Subject: Re: converting data from excel to spss

Many thanks Jason
 
haven't tried it yet but it looks like a good solution for my problem.
 
All the best,
 
Liat

On Tue, Jun 16, 2009 at 11:25 AM, Jason Ferris <[hidden email]> wrote:

Hi Liat,

If I understand your description of your excel datasheet you have

Var1 11

Var2 12

Var3 13

Var1 21

Var2 22

Var3 23

Var1 31

Var2 32

Var3 33

 

Where in column 1 var1-var3 represent your 120 variables in each row and column 2 represent responses (in this case the first three responses are for subject 1 (11 12 13) and the second three are for subject 2 (21 22 23)).

 

What I would suggest is arranging your data in excel (I would have used Stata to do this) I have attached an excel spread sheet which has two macro (this are listed below in case attachments are dropped off.

 

In your first spread sheet (orig) has data similar to above in the second spread sheet (final) has data the way you want it (if I have it correct).

 

The first macro – arrange_data basically transposes each grouping of your 120 variables with the subjects response (ie., puts them across columns – so that each row is now a subject)

 

The second macro delete_rows deletes all the variable name rows (excluding the first one).  In this macro you will have to indicate the n-1 row (which I current have set to 5).  I.e., if you have 2002 rows of data replace 5 to 2001 and then run the macro.

 

Once you have the data in excel in the appropriate form you can readily import in through SPSS’s menus

 

HTH

Jason

 

Sub arrange_data()

' arrange_data Macro

'

    Sheets("final").Select

    Range("A1").Select

    Sheets("orig").Select

    Range("A1").Select

   

Do While IsEmpty(ActiveCell.Offset(0, 1)) = False

    ActiveCell.Range("A1:B3").Select

    Selection.Copy

    Sheets("final").Select

    ActiveCell.Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=True

    ActiveCell.Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(1, 0).Range("A1").Select

    Sheets("orig").Select

    Application.CutCopyMode = False

    Selection.Delete Shift:=xlUp

    ActiveCell.Select

    Loop

 

 

End Sub

 

Sub delete_rows()

' delete every other row with variable names

 

 For i = 5 To 2 Step -2

   Rows(i).Delete

 Next i

 

End Sub

 

 

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Liat Lev-Shalem
Sent: Tuesday, 16 June 2009 4:45 PM
To: [hidden email]
Subject: converting data from excel to spss

 

Dear List members,

 

I want to convert a data file from excel to spss. I have a large number of cases (>1000) with 120 variables for each case. The data is arranged in the excel file with 1 row for every variable, so that every subject has 120 rows (row1 to row 120 for subject 1, rows 121-240 for subject2, etc.). I want to create a data file that would look like a regular data file- i.e.: all 120 variables presented in the same row. How do I do it?

 

Many thanks

 

Liat Lev-Shalem

 



--
ליאת לב-שלם
עיבודים סטטיסטיים וסיוע מחקרי
טלפון: 054-5452848
פקס: 03-9229807

------------------------------------------
DISCLAIMER: This message (including any attachments) is intended solely for the addressee(s) named and may contain confidential or privileged information. 
If you are not the intended recipient, please delete it and notify the sender. 
Views expressed in this message are those of the individual sender,and are not necessarily the views of the Turning Point Alcohol and Drug Centre Inc (ABN: 17 302 055 629).  

Turning Point Alcohol and Drug Centre Inc

Although this message and any attachments have been scanned for viruses by 'Trend Micro InterScan' at the time of sending, 
you are advised to rescan on receipt.

The whole or parts of this email may be subject to copyright of Turning Point Alcohol and Drug Centre Inc (ABN: 17 302 055 629), and/or third parties. 
You can only re-transmit, distribute or use the material if you are authorised to do so.

Please consider the environment before printing this email or attachments.



--
ליאת לב-שלם
עיבודים סטטיסטיים וסיוע מחקרי
טלפון: 054-5452848
פקס: 03-9229807


__________ Information from ESET NOD32 Antivirus, version of virus signature database 4158 (20090616) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
Free forum by Nabble Edit this page