recoding data

[Scott Roesch]

I have imported a data set from Excel where the values are in percentages (e.g., 98.33%). I would like to remove the % symbol and convert these to numeric. A simple Replace procedure has not worked correctly. Any thoughts would be greatly appreciated.

Scott

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

[Bruce Weaver]

Scott, I just created a small Excel file with one variable (X) formatted as a percentage showing two decimals.  Then I tried the following:

GET DATA /TYPE=XLSX
  /FILE='C:\Temp\Junk.xlsx'
  /SHEET=name 'Sheet1'
  /CELLRANGE=full
  /READNAMES=on
  /ASSUMEDSTRWIDTH=32767.

LIST.
ALTER TYPE X (F5.4).
LIST.

Output from the first LIST:

          X
 
     98.33%
     71.23%
     45.81%

And from the second LIST:

    X
 
98.33
71.23
45.81

Also, the output from ALTER TYPE showed that X was originally formatted as PCT11.2.  So, I expect a simple ALTER TYPE as above will do the trick for you.

HTH.

Scott Roesch wrote

I have imported a data set from Excel where the values are in percentages
(e.g., 98.33%). I would like to remove the % symbol and convert these to
numeric. A simple Replace procedure has not worked correctly. Any thoughts
would be greatly appreciated.

Scott

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

[David Marso]

Looks like a simple FORMATS command will work ;-).

DATA LIST FREE/x (PCT5.2).
BEGIN DATA
89.50%
END DATA.
LIST.
COMPUTE x=REPLACE(x,"%","").
 
/*>Error # 4311 in column 27.  Text: )
/*>The string argument required for the function specified was not supplied.
/*>Execution of this command stops.

FORMATS x (F5.2).
LIST.


The fact that REPLACE didn't work suggests there was an error message about  a missing String argument?  That would have been a useful bit of info from OP!!!!

Bruce Weaver wrote

Scott, I just created a small Excel file with one variable (X) formatted as a percentage showing two decimals.  Then I tried the following:

GET DATA /TYPE=XLSX
  /FILE='C:\Temp\Junk.xlsx'
  /SHEET=name 'Sheet1'
  /CELLRANGE=full
  /READNAMES=on
  /ASSUMEDSTRWIDTH=32767.

LIST.
ALTER TYPE X (F5.4).
LIST.

Output from the first LIST:

          X
 
     98.33%
     71.23%
     45.81%

And from the second LIST:

    X
 
98.33
71.23
45.81

Also, the output from ALTER TYPE showed that X was originally formatted as PCT11.2.  So, I expect a simple ALTER TYPE as above will do the trick for you.

HTH.

Scott Roesch wrote

I have imported a data set from Excel where the values are in percentages
(e.g., 98.33%). I would like to remove the % symbol and convert these to
numeric. A simple Replace procedure has not worked correctly. Any thoughts
would be greatly appreciated.

Scott

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

[Jon K Peck]

Format should be all that is necessary, but bear in mind that you might need to divide the value by 100, since % in Excel implies converting a decimal value to percentage form, but in Statistics, it is just a format.


Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: [phone]




From:        David Marso <[hidden email]>
To:        [hidden email]
Date:        10/14/2014 05:36 PM
Subject:        Re: [SPSSX-L] recoding data
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Looks like a simple FORMATS command will work ;-).

DATA LIST FREE/x (PCT5.2).
BEGIN DATA
89.50%
END DATA.
LIST.
COMPUTE x=REPLACE(x,"%","").

/*>Error # 4311 in column 27.  Text: )
/*>The string argument required for the function specified was not supplied.
/*>Execution of this command stops.

FORMATS x (F5.2).
LIST.


The fact that REPLACE didn't work suggests there was an error message about
a missing String argument?  That would have been a useful bit of info from
OP!!!!


Bruce Weaver wrote
> Scott, I just created a small Excel file with one variable (X) formatted
> as a percentage showing two decimals.  Then I tried the following:
>
> GET DATA /TYPE=XLSX
>   /FILE='C:\Temp\Junk.xlsx'
>   /SHEET=name 'Sheet1'
>   /CELLRANGE=full
>   /READNAMES=on
>   /ASSUMEDSTRWIDTH=32767.
>
> LIST.
> ALTER TYPE X (F5.4).
> LIST.
>
> Output from the first LIST:
>
>           X
>  
>      98.33%
>      71.23%
>      45.81%
>
> And from the second LIST:
>
>     X
>  
> 98.33
> 71.23
> 45.81
>
> Also, the output from ALTER TYPE showed that X was originally formatted as
> PCT11.2.  So, I expect a simple ALTER TYPE as above will do the trick for
> you.
>
> HTH.
> Scott Roesch wrote
>> I have imported a data set from Excel where the values are in percentages
>> (e.g., 98.33%). I would like to remove the % symbol and convert these to
>> numeric. A simple Replace procedure has not worked correctly. Any
>> thoughts
>> would be greatly appreciated.
>>
>> Scott
>>
>> =====================
>> To manage your subscription to SPSSX-L, send a message to

>> LISTSERV@.UGA

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





-----
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
---
"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis."
Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?"
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/recoding-data-tp5727585p5727591.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


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