Excel <-> SPSS

[Allan Reese (Cefas)]

Date:         Fri, 17 Apr 2009 10:29:44 -1000
Bob Schacht <[hidden email]> asked:
> Is there a place where known problems in exporting/importing between
SPSS
> and Excel have been summarized? We do a lot of that, and I want to
know
> where the problems occur so that I can deal with them.

A summary would be useful, but is a lot of work and would go out of
date.  The EUSPRIG group may have someone more inclined to do such work.


There are some generic issues due to the different philosophies behind a
spreadsheet and a stats package, or caused by the way spreadsheets are
typically used (Excel defenders always call these "user error"), or
caused by specific features of Excel.

These include:
1) spreadsheets treat columns and rows as equivalent
2) column headings are in the same space as the data
3) spreadsheet users commonly use more than one row for labels, or merge
cells to bridge several columns; they also seem to have an aversion to
starting in A1 - hang over from school days when you "leave a margin"? -
and separate blocks of data with blank lines
4) spreadsheet users commonly insert sub-totals within the data rows, or
hide columns and rows (so WYSIWYG isn't)

5) Excel "Guidelines on lists" help advises against all those practices.
6) Important to note distinction of visible and stored value.  Excel
formats single cells; packages format the column.  Some packages then
read whole column and assign a format; others rely on first row of data
to establish pattern.
7) Even when used properly, Excel can distort values as entered.  Known
examples include numbers stored as strings, and strings coerced to
dates.  A recent problem for me was having people enter "month/year"
data and stopping Excel storing this as "1/month/year" in date format.
8) *Biggest issue" for me is that Excel doesn't have concept of missing
value, and odd things happen with unused/empty/" "/ cells.  (worst case
blank->0 when used in formula)

You can always search the list archive for Excel problems (but it's a
long job!) and there are some excellent papers to google on "problems
with Excel" in general.

Allan

R Allan Reese
Senior statistician, Cefas
The Nothe, Weymouth DT4 8UB

Tel: [phone] -direct
Fax: [phone]

www.cefas.co.uk



***********************************************************************************
This email and any attachments are intended for the named recipient only.  Its unauthorised use, distribution, disclosure, storage or copying is not permitted.  If you have received it in error, please destroy all copies and notify the sender.  In messages of a non-business nature, the views and opinions expressed are the author's own and do not necessarily reflect those of the organisation from which it is sent.  All emails may be subject to monitoring.
***********************************************************************************

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Marta Garcia-Granero]

Hi:

I finally finished my macro for Passing-Bablok regression (available at
the usual place, click the link at the end of this message, below my
signature), and right now I'm working on another for Deming regression
(to complete my set of macros for agreement). I got a nice reference
(Linnet K, Clin Chem 1993; 39-3: 424-32) and I think I have the correct
scenario for unweighted Deming regression. Anyway, the paper I have used
says that for weighted Deming regression, an iterative  method described
in: "Linnet K, Stat Med 1990; 9: 1463-73" has to be used. Also, a full
description of the jackknife method for estimating the slope&intercept
is described there too (Ive had to "guess" it, by comparison to other
jackknife SE I have seen in other papers), and I'd want to check my SE
are correct (I get some very small differences for the 95%CI for the
intercept when I compare my results with the ones obtained by Method
Validator, and it upsets me). Unfortunately, my University does not have
access to that journal, and I'll have to wait for a month of so until my
request for a copy is attended (sight). This is the link to the paper I
need.

http://www3.interscience.wiley.com/search/allsearch?mode=viewselected&product=journal&ID=113396813&view_selected.x=76&view_selected.y=6&view_selected=view_selected

Could anybody help?

Thanks in advance.

Marta García-Granero (AKA: the Compulsive Macro Programmer)

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Marta Garcia-Granero]

Thanks a lot, Dave

(for everybody else, this message is to inform you that I have already
the paper, thanks to David Nichols).

I'll inform you all when I finally upload the macro.

Marta

(funny, I tried to translate the Spanish saying "feliz como una
lombriz", to describe how I feel right now, but it does not look cute in
English: "as happy as an earth-worm" would be the exact translation, and
it evokes a strange image... rhymes are untranslatable).


Nichols, David P. wrote:

> Funny, the link identifies this as from 2006, but it's actually from 1990. Anyway, here's a copy.
>
> Best,
>
> Dave
>
> David Nichols - Master Statistician
> SPSS Inc.
> Email: [hidden email]
> Tel:   [phone] / Fax: [phone]
>
> -----Original Message-----
> From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Marta García-Granero
> Sent: Monday, April 20, 2009 11:18 AM
> To: [hidden email]
> Subject: [SPSSX-L] Anyone with online access to "Statistics in Medicine" Journal?
>
> Hi:
>
> I finally finished my macro for Passing-Bablok regression (available at the usual place, click the link at the end of this message, below my signature), and right now I'm working on another for Deming regression (to complete my set of macros for agreement). I got a nice reference (Linnet K, Clin Chem 1993; 39-3: 424-32) and I think I have the correct scenario for unweighted Deming regression. Anyway, the paper I have used says that for weighted Deming regression, an iterative  method described
> in: "Linnet K, Stat Med 1990; 9: 1463-73" has to be used. Also, a full description of the jackknife method for estimating the slope&intercept is described there too (Ive had to "guess" it, by comparison to other jackknife SE I have seen in other papers), and I'd want to check my SE are correct (I get some very small differences for the 95%CI for the intercept when I compare my results with the ones obtained by Method Validator, and it upsets me). Unfortunately, my University does not have access to that journal, and I'll have to wait for a month of so until my request for a copy is attended (sight). This is the link to the paper I need.
>
> http://www3.interscience.wiley.com/search/allsearch?mode=viewselected&product=journal&ID=113396813&view_selected.x=76&view_selected.y=6&view_selected=view_selected
>
> Could anybody help?
>
> Thanks in advance.
>
> Marta García-Granero (AKA: the Compulsive Macro Programmer)
>
>

--
For miscellaneous SPSS related statistical stuff, visit:
http://gjyp.nl/marta/

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD