Syntax for converting string column to numeric column

[Chris A. McGibbon]

Hi folks,

I'm importing an ASCII data file from an on-line survey. Some of the columns
(such as yes/no questions) are automatically formatted as string columns
("Y" or "N") when the data are imported. I want to recode these columns into
numeric format.

Here's what's I've done:

First I recode the column (the variable name is VAR in this example) as
follows:

  RECODE
    var  ('Y'='1')  ('N'='0')  .
  EXECUTE .

Now I have a column that is still string, but with 1's and 0's instead of
Y's and N's. The next step is to convert this column to numeric format.

I've tried using this:

  NUMBER (var, F1.0).

But I get this message: "NUMBERED This command is no longer supported."
(I'm using an old version too, v10, and I have no idea why it says NUMBERED
when the command is NUMBER...).

I've also tried using this:

  FORMAT var (F1.0).

But I get this error message: "Only the formats A and AHEX can be applied to
string variables"

Although I can do the conversion manually by going into the "variables"
window and changing it, I don't want to have to do this everytime I import
the data -- it creates quite a bottleneck in the process.

I also don't want (if possible) to have to create new variables to make this
happen -- I'd prefer simply having the column format of the variable in
question (VAR in this example) changed.

Any help would be appreciated!
Thanks in advance,

Chris

[Peck, Jon]

Variable types are immutable, so use RECODE ... INTO with numeric output codes.

The command you wanted to use was NUMERIC.  The NUMBERed command is completely different and obsolete (think punch cards), but NUMERIC would not work because of the immutability.

You could also use AUTORECODE to do everything automatically.

HTH,
Jon Peck

p.s. You can delete the original variable name and rename the new one back to the original name, but availability of the commands for that (RENAME VARIABLES and DELETE VARIABLES) depends on your SPSS version.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Chris A. McGibbon
Sent: Tuesday, September 19, 2006 7:18 AM
To: [hidden email]
Subject: [SPSSX-L] Syntax for converting string column to numeric column

Hi folks,

I'm importing an ASCII data file from an on-line survey. Some of the columns
(such as yes/no questions) are automatically formatted as string columns
("Y" or "N") when the data are imported. I want to recode these columns into
numeric format.

Here's what's I've done:

First I recode the column (the variable name is VAR in this example) as
follows:

  RECODE
    var  ('Y'='1')  ('N'='0')  .
  EXECUTE .

Now I have a column that is still string, but with 1's and 0's instead of
Y's and N's. The next step is to convert this column to numeric format.

I've tried using this:

  NUMBER (var, F1.0).

But I get this message: "NUMBERED This command is no longer supported."
(I'm using an old version too, v10, and I have no idea why it says NUMBERED
when the command is NUMBER...).

I've also tried using this:

  FORMAT var (F1.0).

But I get this error message: "Only the formats A and AHEX can be applied to
string variables"

Although I can do the conversion manually by going into the "variables"
window and changing it, I don't want to have to do this everytime I import
the data -- it creates quite a bottleneck in the process.

I also don't want (if possible) to have to create new variables to make this
happen -- I'd prefer simply having the column format of the variable in
question (VAR in this example) changed.

Any help would be appreciated!
Thanks in advance,

Chris

[Beadle, ViAnn]

The long-ago deprecated NUMBERED command reserved columns 73 to 80 for a sequential number which was very useful when you dropped that big deck of cards.
 
Perhaps you are thinking of the NUMERIC command which is used to initialize a new numeric variable or the NUMBER function which is used to convert a string to a number which is want you want to do. BUT before you do that, just use the RECODE command with the INTO keyword, as in
 
RECODE var ('Y'=1)('N'=0) into newvar.

________________________________

From: SPSSX(r) Discussion on behalf of Chris A. McGibbon
Sent: Tue 9/19/2006 7:17 AM
To: [hidden email]
Subject: Syntax for converting string column to numeric column



Hi folks,

I'm importing an ASCII data file from an on-line survey. Some of the columns
(such as yes/no questions) are automatically formatted as string columns
("Y" or "N") when the data are imported. I want to recode these columns into
numeric format.

Here's what's I've done:

First I recode the column (the variable name is VAR in this example) as
follows:

  RECODE
    var  ('Y'='1')  ('N'='0')  .
  EXECUTE .

Now I have a column that is still string, but with 1's and 0's instead of
Y's and N's. The next step is to convert this column to numeric format.

I've tried using this:

  NUMBER (var, F1.0).

But I get this message: "NUMBERED This command is no longer supported."
(I'm using an old version too, v10, and I have no idea why it says NUMBERED
when the command is NUMBER...).

I've also tried using this:

  FORMAT var (F1.0).

But I get this error message: "Only the formats A and AHEX can be applied to
string variables"

Although I can do the conversion manually by going into the "variables"
window and changing it, I don't want to have to do this everytime I import
the data -- it creates quite a bottleneck in the process.

I also don't want (if possible) to have to create new variables to make this
happen -- I'd prefer simply having the column format of the variable in
question (VAR in this example) changed.

Any help would be appreciated!
Thanks in advance,

Chris

[Art Kendall]

numeric newvar (f1).
recode oldvar ('Y' =1)('N'=0)('  '=-1) into newvar.
value labels newvar 1 'yes'  0 'no' -1 'blank'.
missing values newvar (-1).

This avoids the dangerous practice of writing over original input data.

Newvar and oldvar can be list of variables.

Art Kendall
Social Research Consultants

Chris A. McGibbon wrote:

>Hi folks,
>
>I'm importing an ASCII data file from an on-line survey. Some of the columns
>(such as yes/no questions) are automatically formatted as string columns
>("Y" or "N") when the data are imported. I want to recode these columns into
>numeric format.
>
>Here's what's I've done:
>
>First I recode the column (the variable name is VAR in this example) as
>follows:
>
>  RECODE
>    var  ('Y'='1')  ('N'='0')  .
>  EXECUTE .
>
>Now I have a column that is still string, but with 1's and 0's instead of
>Y's and N's. The next step is to convert this column to numeric format.
>
>I've tried using this:
>
>  NUMBER (var, F1.0).
>
>But I get this message: "NUMBERED This command is no longer supported."
>(I'm using an old version too, v10, and I have no idea why it says NUMBERED
>when the command is NUMBER...).
>
>I've also tried using this:
>
>  FORMAT var (F1.0).
>
>But I get this error message: "Only the formats A and AHEX can be applied to
>string variables"
>
>Although I can do the conversion manually by going into the "variables"
>window and changing it, I don't want to have to do this everytime I import
>the data -- it creates quite a bottleneck in the process.
>
>I also don't want (if possible) to have to create new variables to make this
>happen -- I'd prefer simply having the column format of the variable in
>question (VAR in this example) changed.
>
>Any help would be appreciated!
>Thanks in advance,
>
>Chris
>
>
>
>

[Bob Schacht-3]

At 02:37 AM 9/19/2006, Peck, Jon wrote:
>Variable types are immutable, . . .


??? Has this changed in recent versions? In version 12, you can easily
change the variable type in Variable View simply by clicking on the "Type"
cell, and changing it. Of course, you will lose any "values" you have
defined in the process.

For example, I've done this to change the type of  "SSN" from numeric to
string, and vice versa.

Or do you mean something else by "immutable"?

Bob

[Peck, Jon]

I mean only that you cannot change the fundamental type of a variable through syntax.  The Data Editor has always had a sneaky non-syntax way of doing this, but since changing the type (including the width of a string) has broad implications, it doesn’t work with syntax.  (Maybe someday).



-----Original Message-----
From: Bob Schacht [mailto:[hidden email]]
Sent: Tuesday, September 19, 2006 2:31 PM
To: Peck, Jon; "SPSSX(r) Discussion"
Subject: Re: Syntax for converting string column to numeric column

At 02:37 AM 9/19/2006, Peck, Jon wrote:
>Variable types are immutable, . . .


??? Has this changed in recent versions? In version 12, you can easily
change the variable type in Variable View simply by clicking on the "Type"
cell, and changing it. Of course, you will lose any "values" you have
defined in the process.

For example, I've done this to change the type of  "SSN" from numeric to
string, and vice versa.

Or do you mean something else by "immutable"?

Bob

[Stevan Nielsen]

I'm delighted to hear of someone who remembers NUMBERED.  Did you
actually ever drop a big deck of cards.  My dissertation data consisted
of big decks of cards which were, indeed, dropped once and shuffled by
gravity -- Newton's revenge on a social scientist!  NUMBERED saved my
mental health.

Stevan Lars Nielsen, Ph.D.
Clinical Professor
Clinical Psychologist
2518 WSC, BYU
Provo, UT 84602

801-422-3035; fax [phone]


-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Beadle, ViAnn
Sent: Tuesday, September 19, 2006 6:43 AM
To: [hidden email]
Subject: Re: Syntax for converting string column to numeric column

The long-ago deprecated NUMBERED command reserved columns 73 to 80 for a
sequential number which was very useful when you dropped that big deck
of cards.

Perhaps you are thinking of the NUMERIC command which is used to
initialize a new numeric variable or the NUMBER function which is used
to convert a string to a number which is want you want to do. BUT before
you do that, just use the RECODE command with the INTO keyword, as in

RECODE var ('Y'=1)('N'=0) into newvar.

________________________________

From: SPSSX(r) Discussion on behalf of Chris A. McGibbon
Sent: Tue 9/19/2006 7:17 AM
To: [hidden email]
Subject: Syntax for converting string column to numeric column



Hi folks,

I'm importing an ASCII data file from an on-line survey. Some of the
columns
(such as yes/no questions) are automatically formatted as string columns
("Y" or "N") when the data are imported. I want to recode these columns
into
numeric format.

Here's what's I've done:

First I recode the column (the variable name is VAR in this example) as
follows:

  RECODE
    var  ('Y'='1')  ('N'='0')  .
  EXECUTE .

Now I have a column that is still string, but with 1's and 0's instead
of
Y's and N's. The next step is to convert this column to numeric format.

I've tried using this:

  NUMBER (var, F1.0).

But I get this message: "NUMBERED This command is no longer supported."
(I'm using an old version too, v10, and I have no idea why it says
NUMBERED
when the command is NUMBER...).

I've also tried using this:

  FORMAT var (F1.0).

But I get this error message: "Only the formats A and AHEX can be
applied to
string variables"

Although I can do the conversion manually by going into the "variables"
window and changing it, I don't want to have to do this everytime I
import
the data -- it creates quite a bottleneck in the process.

I also don't want (if possible) to have to create new variables to make
this
happen -- I'd prefer simply having the column format of the variable in
question (VAR in this example) changed.

Any help would be appreciated!
Thanks in advance,

Chris

[Richard Ristow]

At 11:32 PM 9/19/2006, Stevan Nielsen wrote:

>I'm delighted to hear of someone who remembers NUMBERED.  Did you
>actually ever drop a big deck of cards.  My dissertation data
>consisted of big decks of cards which were, indeed, dropped once and
>shuffled by gravity -- Newton's revenge on a social
>scientist!  NUMBERED saved my mental health.

Yeah; hurray for serial numbers on data cards. (And for card sorters,
that could put the deck back in order by serial number.)

Econometricians I worked with were always worrying about autocorrelated
residuals in estimating time-series models - they'd probably be handled
by a mixed-model analysis, today. Anyway, I remember one who suggested
how to handle autocorrelated residuals: "Drop your data deck."

But it wasn't NUMBERED that saved you. NUMBERED (I looked it up - I
still have the maroon manual) didn't refer to data cards, but to
program cards: DATA LIST, COMPUTE and so on. Its effect was to have the
SPSS parser ignore columns 73-80 of program (then called 'control'
cards).

We didn't use serial numbers on program cards - SPSS, FORTRAN, whatever
- very much. They were theoretically a good idea, but too much trouble
when you were punching your program onto the cards. If you did, of
course you didn't number 1,2,3... but at least 10,20,30... so if you
needed to insert a statement, you could give it a number that fit into
the existing sequence. I guess with a very large program, you'd run
your deck through a program that duplicated the cards, adding the
serial numbers. But then you'd have to run the new deck through an
'interpreter' that typed the character in each column at the head of
the column, or you couldn't read the cards and know where to make
changes. Mostly, it felt like more trouble than was worth taking.

Those were the days, I guess. I'd hate to go back to not having MATCH
FILES.