Eliminating

[Charley Trimble]

When reading an excel .xlsx file into 19.0, an id variable reflected in the excel file is also read with an ' in the SPSS data file, e.g. '123456.  How do I eliminate the ' from the id variable so that I can use id variable as a numeric variable type (without the ', i.e. 123456)?  Your help, please and thanks.
Charley Trimble

[David Marso]

Charley,
If it is present in the Excel file then do a find/replace before reading it into SPSS ;-)
Ideally, one will clean up source data prior to importing into SPSS.  That sames the hassle of having to do it all again when you reread the same file in the future.

OR.. within SPSS
If it occurs as first character in every instance then use the CHAR.SUBSTR function .
COMPUTE var=CHAR.SUBSTR(var,2).

If not in every instance then do it conditionally with IF.
IF CHAR.SUBSTR(var,1,1) EQ "'" var=CHAR.SUBSTR(var,2).
YMMV depending upon the specifics...
----
If all goes well then you can create the mumeric as
COMPUTE numvar=NUMBER(var,N6).
or
COMPUTE numvar=NUMBER(var,F6).
I leave you to RTFM to determine possible nuances and specifics .
HTH, David
-

Charley Trimble wrote

When reading an excel .xlsx file into 19.0, an id variable reflected in the excel file is also read with an ' in the SPSS data file, e.g. '123456.  How do I eliminate the ' from the id variable so that I can use id variable as a numeric variable type (without the ', i.e. 123456)?  Your help, please and thanks.
Charley Trimble

[Robert Walker]

Charley,

Alternatively (example data)...

DATA LIST FREE /FUNKY (A15).
BEGIN DATA
'12345
'23456
'34567
END DATA.
ALTER TYPE FUNKY(F6.0).

Regards,

Bob Walker
Surveys & Forecasts, LLC

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Charley Trimble
Sent: Sunday, June 26, 2011 11:59 AM
To: [hidden email]
Subject: Eliminating

When reading an excel .xlsx file into 19.0, an id variable reflected in the
excel file is also read with an ' in the SPSS data file, e.g. '123456.  How
do I eliminate the ' from the id variable so that I can use id variable as a
numeric variable type (without the ', i.e. 123456)?  Your help, please and
thanks.
Charley Trimble



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Eliminating-tp4525950p4525950.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