Reading Data

[Nyougo Omae.]

Hello,

I have read some excel data onto an SPSS file and when I run the frequencies I notice that what should be similar observations show up as different entries. For instance, I end up with 2 entries for B (one of the choices on a multiple choice question). I think in the excel dataset, one may have been entered with a space at the beginning. Is there a way I can correct this in SPSS without having to go back to the original data and change one at a time?

Thanks for any help on this.

Hilda.

[Oliver, Richard]

Compute stringvarname=ltrim(stringvarname).

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Nyougo Omae.
Sent: Wednesday, June 06, 2007 11:50 AM
To: [hidden email]
Subject: Reading Data

Hello,

I have read some excel data onto an SPSS file and when I run the frequencies I notice that what should be similar observations show up as different entries. For instance, I end up with 2 entries for B (one of the choices on a multiple choice question). I think in the excel dataset, one may have been entered with a space at the beginning. Is there a way I can correct this in SPSS without having to go back to the original data and change one at a time?

Thanks for any help on this.

Hilda.

[Gary Rosin]

I'm not an SPSS maven, but it would be easy to write
a macro to "Trim" leading (RTrim) and trailing (LTrim)
spaces in the cells of you worksheet.

Gary

At 11:49 AM 6/6/2007, Nyougo Omae. wrote:

>I have read some excel data onto an SPSS file and when I
>run the frequencies I notice that what should be similar
>observations show up as different entries. For instance,
>I end up with 2 entries for B (one of the choices on a
>multiple choice question). I think in the excel dataset,
>one may have been entered with a space at the beginning.
>Is there a way I can correct this in SPSS without having
>to go back to the original data and change one at a time?
>
>Thanks for any help on this.
>
>Hilda.

[Oliver, Richard]

Rtrim is for trailing blanks/characters; Ltrim is for leading blanks/characters.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Gary Rosin
Sent: Wednesday, June 06, 2007 12:07 PM
To: [hidden email]
Subject: Re: Reading Data

I'm not an SPSS maven, but it would be easy to write
a macro to "Trim" leading (RTrim) and trailing (LTrim)
spaces in the cells of you worksheet.

Gary

At 11:49 AM 6/6/2007, Nyougo Omae. wrote:

>I have read some excel data onto an SPSS file and when I
>run the frequencies I notice that what should be similar
>observations show up as different entries. For instance,
>I end up with 2 entries for B (one of the choices on a
>multiple choice question). I think in the excel dataset,
>one may have been entered with a space at the beginning.
>Is there a way I can correct this in SPSS without having
>to go back to the original data and change one at a time?
>
>Thanks for any help on this.
>
>Hilda.

[Marks, Jim]

Hilda:

Not tested.

** for a two-character string variable into a one-character variable.

STRING new_score (A1).

DO IF substr(old_score,1,1) = ' '.
COMPUTE  new_score = substr(old_score,2,1).
ELSE.
COMPUTE  new_score = substr(old_score,1,1).
END IF.

** put an execute or data reading command at the appropriate place.

This can be extended using DO REPEAT.

STRING new_q1 to new_q10 (10A1).

DO REPEAT x = q1 TO q10  /y= new_q1 TO new_q10.

DO IF substr(x,1,1) = ' '.
COMPUTE  y = substr(x,2,1).
ELSE.
COMPUTE  y = substr(x,1,1).
END IF.


--jim



-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Nyougo Omae.
Sent: Wednesday, June 06, 2007 11:50 AM
To: [hidden email]
Subject: Reading Data

Hello,

I have read some excel data onto an SPSS file and when I run the
frequencies I notice that what should be similar observations show up as
different entries. For instance, I end up with 2 entries for B (one of
the choices on a multiple choice question). I think in the excel
dataset, one may have been entered with a space at the beginning. Is
there a way I can correct this in SPSS without having to go back to the
original data and change one at a time?

Thanks for any help on this.

Hilda.

[Gary Rosin]

Got my left & my right mixed up!

If she has cell entries that are double entries, wouldn't
cleaning it up in Excell make more sense?

Gary
At 12:00 PM 6/6/2007, Oliver, Richard wrote:

>Compute stringvarname=ltrim(stringvarname).
>
>-----Original Message-----
>From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
>Nyougo Omae.
>Sent: Wednesday, June 06, 2007 11:50 AM
>To: [hidden email]
>Subject: Reading Data
>
>Hello,
>
>I have read some excel data onto an SPSS file and when I run the
>frequencies I notice that what should be similar observations show up as
>different entries. For instance, I end up with 2 entries for B (one of the
>choices on a multiple choice question). I think in the excel dataset, one
>may have been entered with a space at the beginning. Is there a way I can
>correct this in SPSS without having to go back to the original data and
>change one at a time?
>
>Thanks for any help on this.
>
>Hilda.

[Richard Ristow]

At 12:49 PM 6/6/2007, Nyougo Omae. wrote:

>I have read some excel data onto an SPSS file and when I run the
>frequencies I notice that what should be similar observations show up
>as different entries. For instance, I end up with 2 entries for B (one
>of the choices on a multiple choice question). I think in the excel
>dataset, one may have been entered with a space at the beginning.

For the problem of leading blanks, see Richard Oliver's solution, the
most direct (and accurate) among the several responses. As Jim Marks
wrote, you can use DO REPEAT to do this for several variables; you
should probably do so for all of your string variables. However, in
this case transform the original variables, as Richard Oliver
suggested, rather than creating new ones as Jim Marks suggested.

Now, when data is entered in an undisciplined system like Excel, it's
very common for invalid codes to be entered, and the invalidity may not
be limited to adding leading spaces. After you've applied 'ltrim' to
all your string variables, take FREQUENCIES again and see what you've
got.

Then, WHETHER YOU SEE ANY INVALID VALUES OR NOT, I recommend recoding
your string variables into different, numeric variables. Attach VALUE
LABELS to the numeric variables, identifying the categories.

When you're writing the RECODE, the RECODE clauses for the valid input
values are clear enough.

For the invalid values, decide whether they can be identified with one
of your valid values. If so, put in RECODE clauses to map them to the
appropriate numeric value. If you can't tell which valid value they're
supposed to be, RECODE them to some appropriate value, maybe 8 for a
variable with fewer than 8; give it VALUE LABEL 'Unrecognized', and
make it a MISSING VALUE.

Then you'll have both your original data in the string variable and its
'cleaned' counterpart in the numeric variable. The RECODE statement
documents the decisions you've made; the cases where you've made them
can be selected for inspection.

If, now, you have only values that are valid on your list, fine.

[Luca Meyer]

Try with:

COMPUTE VAR=LTRIM(RTRIM(VAR)).
EXE.

That should eliminate leading and trailing spaces.

Luca

Mr. Luca MEYER
Market research, data analysis & more
www.lucameyer.com - Tel: [phone]


-----Messaggio originale-----
Da: SPSSX(r) Discussion [mailto:[hidden email]] Per conto di
Nyougo Omae.
Inviato: mercoledì 6 giugno 2007 18.50
A: [hidden email]
Oggetto: Reading Data

Hello,

I have read some excel data onto an SPSS file and when I run the frequencies
I notice that what should be similar observations show up as different
entries. For instance, I end up with 2 entries for B (one of the choices on
a multiple choice question). I think in the excel dataset, one may have been
entered with a space at the beginning. Is there a way I can correct this in
SPSS without having to go back to the original data and change one at a
time?

Thanks for any help on this.

Hilda.

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.472 / Virus Database: 269.8.9/834 - Release Date: 05/06/2007
14.38
 

No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.472 / Virus Database: 269.8.9/834 - Release Date: 05/06/2007
14.38
 

[Richard Ristow]

At 05:49 PM 6/6/2007, Luca Meyer wrote:

>Try with:
>
>COMPUTE VAR=LTRIM(RTRIM(VAR)).
>
>That should eliminate leading and trailing spaces.

Not quite. It does eliminate leading spaces. But since string variables
have a fixed length, there's no way to eliminate trailing spaces in a
*variable*. If you RTRIM them off, they'll just be padded back on to
the value assigned to the variable.

[Oliver, Richard]

That's right -- but for purposes of evaluating string values, trailing spaces are irrelevant, while leading spaces are very relevant. For example:

select if "value        " = "value"

evaluates as true, whereas:

select if "   value" = "value"

evaluates as false.



-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Richard Ristow
Sent: Thursday, June 07, 2007 1:52 AM
To: [hidden email]
Subject: Re: Reading Data

At 05:49 PM 6/6/2007, Luca Meyer wrote:

>Try with:
>
>COMPUTE VAR=LTRIM(RTRIM(VAR)).
>
>That should eliminate leading and trailing spaces.

Not quite. It does eliminate leading spaces. But since string variables
have a fixed length, there's no way to eliminate trailing spaces in a
*variable*. If you RTRIM them off, they'll just be padded back on to
the value assigned to the variable.