Transform/Recode Variable Not Working

[AndrewM]

Hello,

I have a column of 20 cells were 19 are blank and one has text in.  I would like to recode/transform all of the blanks to '0' and the one cell that has text in it to '1'.  I am very new to SPSS.

The problem *I think* is when I'm trying to define old and new values.  For transforming the blanks to '0' i selected "System- or user-missing" for old value and then input '0' for new value.  For transforming the one cell with text in it to '1', I selected "All other values" for old value and then input '1' for new value.  When I run, everything is showing up as '1'.  I'm so confused.

I don't think this matters, but earlier in my syntax I am actually importing this data from an Excel spreadsheet.  In Excel...the blanks are blanks and that one cell with text in Excel has imported correctly to Excel, so I can't imagine the problem. Can any one solve this elementary riddle for me?  

[Rick Oliver-3]

compute numericvar=char.length(stringvar)>0.

Rick Oliver
Senior Information Developer
IBM Business Analytics (SPSS)
E-mail: [hidden email]



From:        AndrewM <[hidden email]>
To:        [hidden email],
Date:        12/06/2012 02:04 PM
Subject:        Transform/Recode Variable Not Working
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Hello,

I have a column of 20 cells were 19 are blank and one has text in.  I would
like to recode/transform all of the blanks to '0' and the one cell that has
text in it to '1'.  I am very new to SPSS.

The problem *I think* is when I'm trying to define old and new values.  For
transforming the blanks to '0' i selected "System- or user-missing" for old
value and then input '0' for new value.  For transforming the one cell with
text in it to '1', I selected "All other values" for old value and then
input '1' for new value.  When I run, everything is showing up as '1'.  I'm
so confused.

I don't think this matters, but earlier in my syntax I am actually importing
this data from an Excel spreadsheet.  In Excel...the blanks are blanks and
that one cell with text in Excel has imported correctly to Excel, so I can't
imagine the problem. Can any one solve this elementary riddle for me?






--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Transform-Recode-Variable-Not-Working-tp5716729.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
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

[Richard Ristow]

At 02:57 PM 12/6/2012, AndrewM wrote:

>I have a column of 20 cells were 19 are blank and one has text
>in.  I would like to recode/transform all of the blanks to '0' and
>the one cell that has text in it to '1'.

Rick Oliver's posted a solution that will work, but it's a little
'cute' and it may not be clear why it works. Here's syntax for a
solution along the lines you were thinking of:

NUMERIC FLAG(F2).
RECODE  LETTER
    ('   ' = 0)
    (ELSE  = 1)
    INTO FLAG.

LIST.
List
|-----------------------------|---------------------------|
|Output Created               |06-DEC-2012 15:50:31       |
|-----------------------------|---------------------------|
LETTER       FLAG

Alpha          1
                0
Beta           1
Gamma          1
                0
                0
Delta          1
Epsilon        1

Number of cases read:  8    Number of cases listed:  8

Now, I wrote the syntax above, as I almost always do; but I also
tried it from the menus, as you did:

>For transforming the blanks to '0' i selected "System- or
>user-missing" for old value and then input '0' for new value.

I can see why you tried that, but it couldn't have worked. Blank is
not as a missing value for string variables, unless it's declared as
a user-missing value using the MISSING VALUES command (or the data
editor). So, nothing, blank or otherwise, would match that first test, and when

>I selected "All other values" for old value and then input '1' for
>new value.  When I run, everything is showing up as '1'.

*everything*, including blank, is an "other value", and will recode into as 1.

The question is, what should you have done? It's not obvious, but you
should have typed a few blanks into "Old Value" and then 0 into "New
Value"; the "Add" button would be activated, and when you click
"Add", you'll see
'   ' --> 0
in the "Old --> New" window. Then, when you select "All other values"
for old value, and '1' for new value and click "Add", you'll get a
second line like this:
ELSE --> 1

And that should work.
====================================================
APPENDIX: Test data, and code (not saved separately)
====================================================
DATA LIST FIXED
    /LETTER(A12).
BEGIN DATA
Alpha

Beta
Gamma


Delta
Epsilon
END DATA.

LIST.
NUMERIC FLAG(F2).
RECODE  LETTER
    ('   ' = 0)
    (ELSE  = 1)
    INTO FLAG.

LIST.

=====================
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

[Rick Oliver-3]

I prefer to think of it as "clever" rather than "cute". :)  And it's a construct I use quite frequently.

But it is a bit obscure for a novice; so here's the explanation:

compute numericvar=char.length(stringvar)>0.

The char.length function gets the length of the string value, not including trailing blanks. So if there's any text in the value at all, char.length returns a value greater than 0. If the value is blank, char.length returns 0.

numericvar=char.length(stringvar)>0 basically says: Set the value of numericvar to 1 if char.length returns a value greater than 0; otherwise set numericvar to 0.

Rick Oliver
Senior Information Developer
IBM Business Analytics (SPSS)
E-mail: [hidden email]



From:        Richard Ristow <[hidden email]>
To:        [hidden email],
Date:        12/06/2012 03:36 PM
Subject:        Re: Transform/Recode Variable Not Working
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

At 02:57 PM 12/6/2012, AndrewM wrote:

>I have a column of 20 cells were 19 are blank and one has text
>in.  I would like to recode/transform all of the blanks to '0' and
>the one cell that has text in it to '1'.

Rick Oliver's posted a solution that will work, but it's a little
'cute' and it may not be clear why it works. Here's syntax for a
solution along the lines you were thinking of:

NUMERIC FLAG(F2).
RECODE  LETTER
   ('   ' = 0)
   (ELSE  = 1)
   INTO FLAG.

LIST.
List
|-----------------------------|---------------------------|
|Output Created               |06-DEC-2012 15:50:31       |
|-----------------------------|---------------------------|
LETTER       FLAG

Alpha          1
               0
Beta           1
Gamma          1
               0
               0
Delta          1
Epsilon        1

Number of cases read:  8    Number of cases listed:  8

Now, I wrote the syntax above, as I almost always do; but I also
tried it from the menus, as you did:

>For transforming the blanks to '0' i selected "System- or
>user-missing" for old value and then input '0' for new value.

I can see why you tried that, but it couldn't have worked. Blank is
not as a missing value for string variables, unless it's declared as
a user-missing value using the MISSING VALUES command (or the data
editor). So, nothing, blank or otherwise, would match that first test, and when

>I selected "All other values" for old value and then input '1' for
>new value.  When I run, everything is showing up as '1'.

*everything*, including blank, is an "other value", and will recode into as 1.

The question is, what should you have done? It's not obvious, but you
should have typed a few blanks into "Old Value" and then 0 into "New
Value"; the "Add" button would be activated, and when you click
"Add", you'll see
'   ' --> 0
in the "Old --> New" window. Then, when you select "All other values"
for old value, and '1' for new value and click "Add", you'll get a
second line like this:
ELSE --> 1

And that should work.
====================================================
APPENDIX: Test data, and code (not saved separately)
====================================================
DATA LIST FIXED
   /LETTER(A12).
BEGIN DATA
Alpha

Beta
Gamma


Delta
Epsilon
END DATA.

LIST.
NUMERIC FLAG(F2).
RECODE  LETTER
   ('   ' = 0)
   (ELSE  = 1)
   INTO FLAG.

LIST.

=====================
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

[AndrewM]

Yes, that totally worked.  I'm really new to SPSS so I am still using the menus/tables as opposed to writing my own code.  I had no idea that simply hitting the space bar once would convey to SPSS that I mean "blank cell".  I will definitely be remembering that for the future.  My syntax looked like this and did exactly what I wanted.  Thank you!

DATASET ACTIVATE DataSet5.
RECODE notcontinuing ('  '=0) (ELSE=1) INTO notcontinuingRECODE.
VARIABLE LABELS  notcontinuingRECODE 'noncontinuingRECODE'.
EXECUTE.

[AndrewM]

Thanks very much for the additional info, Rick.  Great learning for me today!

[Cleland, Patricia (EDU)]

I am out of the office until December 11  and will not be checking my emails. If your issue is urgent, please contact Caroline Rodrigues ([hidden email]) Otherwise I will reply when I return.

=====================
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