Converting Excel to SPSS and recoding

Ask here about academic and research issues, like designing studies, recruiting participants, choosing statistics, submitting for publication, etc.
Post Reply
anniecat
Posts: 37
Joined: Mon May 26, 2014 6:45 pm

Converting Excel to SPSS and recoding

Post by anniecat » Sun Jun 15, 2014 8:39 am

Hi everyone

I have a question about converting data from an Excel file to SPSS. I know that it is simple to convert numeric data but I have most string variables in my file (a combination of Likert-type questions; a fair amount of yes/no answers plus a few open ended text questions). I have tried to re-code string to numeric variables in SPSS but it just leaves me with decimal points in the variable columns. I am guessing that maybe the data needs to be cleaned and put into numeric form in Excel first? I have looked as much as I can at SPSS forums online but most of the responses are talking about things that are currently way beyond my understanding of SPSS and mostly written by non psychologists. This is not helped by the fact that for some reason the Help tab on my SPSS wont' work!

My brain feels so scrambled right now that I have considered just manually inputting into SPSS but I have a lot of data (over 200 variables and participants).

I have no supervisor at the moment so can not ask them (I hopefully will get a replacement by the end of this week. Ideally I would really like to have my data cleaned and good to go in SPSS so I can maximise the time I have with them for analysis).

Thank you all for all you share here - it is a wonderful resource.

User avatar
Loula
Posts: 1096
Joined: Mon Jun 15, 2009 3:28 pm

Re: Converting Excel to SPSS and recoding

Post by Loula » Sun Jun 15, 2014 6:04 pm

Sounds like you need to recode your yes/ no items in excel to 1/0 responses (that you can then paste into SPSS). The easiest way I know to do it is to make a new column next to your yes/no in excel and paste in the formula =COUNTIF(A1,"yes") (substitute the A1 for wherever your yes/ no is). You can then highlight the whole column and press CTRL+D and it will convert the whole set for you. You can then paste your list of 1s and 0s into SPSS where you can recode the variable as yes/no

User avatar
HedleyLamarr
Posts: 202
Joined: Tue Jul 24, 2012 9:46 pm

Re: Converting Excel to SPSS and recoding

Post by HedleyLamarr » Sun Jun 15, 2014 6:07 pm

Hi,

Yes, its probably best to recode the yes/no answers in excel as 1/0 or something before copying over. Generally I've found it easier to do my editing in Excel - as much as anything, its just a bit more user friendly than SPSS for those things. I'm not 100% sure what you mean by the decimal places but it just sounds like the data hasn't copied over or has gone missing in SPSS - you get a '.' when nothing has been entered for a particular variable. I'm guessing that SPSS isn't clear if its numeric or alphabetic and has missed it out.

After its in SPSS, you'll still need to go in to variable view and make sure all the variables are labelled, tell it what to do for missing values etc. For your yes/no questions, you'll need to make sure the 'values' bit in 'variable view' reflects what your '1/0' codes mean. Also, variables have a 'width' which limits the amount of data that can go in there, so it can restrict the amount of text that is copied in. So you may need to edit this to make sure all the text for the open ended answers copies over (but I may be wrong, SPSS may do this automatically when you copy over from Excel).

Also, shouldn't your likert scale variables be numeric and not string?

I'm no expert at this and I'm not sure how much help the above will be, but the links at the bottom have some useful guides to converting from Excel to SPSS. I've also put in a link to a blog that gives some useful tips on managing databases.

http://www.sussex.ac.uk/its/help/guide?id=90
http://www.statstutor.ac.uk/resources/u ... mexcel.pdf
http://deevybee.blogspot.co.uk/2014/04/ ... known.html

I hope this helps and good luck with it all!
Last edited by HedleyLamarr on Sun Jun 15, 2014 6:12 pm, edited 1 time in total.
My mind is aglow with whirling, transient nodes of thought careening through a cosmic vapor of invention.

User avatar
HedleyLamarr
Posts: 202
Joined: Tue Jul 24, 2012 9:46 pm

Re: Converting Excel to SPSS and recoding

Post by HedleyLamarr » Sun Jun 15, 2014 6:10 pm

Loula wrote:Sounds like you need to recode your yes/ no items in excel to 1/0 responses (that you can then paste into SPSS). The easiest way I know to do it is to make a new column next to your yes/no in excel and paste in the formula =COUNTIF(A1,"yes") (substitute the A1 for wherever your yes/ no is). You can then highlight the whole column and press CTRL+D and it will convert the whole set for you. You can then paste your list of 1s and 0s into SPSS where you can recode the variable as yes/no
Beaten to it! Another way of doing this would be to also click on the letter at the top of the column and use find/replace .
My mind is aglow with whirling, transient nodes of thought careening through a cosmic vapor of invention.

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest