Change string to date variable

[Rhonda Boorman-2]

Hi all,

My dataset contains a string variable for day, month, and year without any separating symbols, e.g. 130310.  How can I change it into a date format?

Many thanks,

Rhonda

 

[Marta Garcia-Granero]

Rhonda Boorman wrote:
>
> My dataset contains a string variable for day, month, and year without
> any separating symbols, e.g. 130310.  How can I change it into a date
> format?
>
Rhonda:

Take a look at the Date and Time Wizard (inside TRANSFORM). It has an
option to compute dates from strings.

HTH,
Marta GG

--
For miscellaneous SPSS related statistical stuff, visit:
http://gjyp.nl/marta/

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

[John F Hall]

Rhonda

 

I'm not too good with dates, but I think you'll need to generate year, month, day as separate variables first.

 

I did a dummy run with your date 130310 and another date from the last century 071240.  I started with numeric vars, but changed the date to a string so we both start from the same place.

---

 

Tried it first with direct conversion from string:

 

<IMG src="file://C:\DOCUME~1\Owner\LOCALS~1\Temp\msohtml1\01\clip_image026.jpg" width=585 height=132 v:shapes="_x0000_i1037">

 

Transform…

            Date and time wizard

 

Click on 2^nd button down, then next:

 

<IMG src="file://C:\DOCUME~1\Owner\LOCALS~1\Temp\msohtml1\01\clip_image028.jpg" width=414 height=260 v:shapes="_x0000_i1038">

 

Click on variable and on required format, then next

 

<IMG src="file://C:\DOCUME~1\Owner\LOCALS~1\Temp\msohtml1\01\clip_image030.jpg" width=418 height=262 v:shapes="_x0000_i1039">

 

<IMG src="file://C:\DOCUME~1\Owner\LOCALS~1\Temp\msohtml1\01\clip_image032.jpg" width=585 height=132 v:shapes="_x0000_i1040">

 

Click on Data View and you'll see it hasn't worked because the original string does not contain spaces, commas etc to separate elements.

 

<IMG src="file://C:\DOCUME~1\Owner\LOCALS~1\Temp\msohtml1\01\clip_image034.jpg" width=585 height=165 v:shapes="_x0000_i1041">

 

 

* Date and Time Wizard: newdate.

COMPUTE newdate = number(yourdate, EDATE10).

VARIABLE LABEL newdate.

VARIABLE LEVEL newdate (SCALE).

FORMATS newdate (EDATE10).

VARIABLE WIDTH newdate(10).

EXECUTE.

 

>Warning # 1151

>A field to be read under the EDATE format is invalid.  The field must

>contain day, month, and year separated by spaces, dashes, slashes, decimal

>points, or commas.  Note that American style dates (month/day/year) can be

>read under the ADATE format.

 

>Command line: 123  Current case: 1  Current splitfile group: 1

>Field contents: '  130310'

 

C

 

 

 

 

>Warning # 1151

>A field to be read under the EDATE format is invalid.  The field must

>contain day, month, and year separated by spaces, dashes, slashes, decimal

>points, or commas.  Note that American style dates (month/day/year) can be

>read under the ADATE format.

 

>Command line: 123  Current case: 2  Current splitfile group: 1

>Field contents: '   71240'

 

---

 

So back to my original solution, which works, but I don't know what you want to do with the dates afterwards.

 

<IMG src="file://C:\DOCUME~1\Owner\LOCALS~1\Temp\msohtml1\01\clip_image002.jpg" width=585 height=123 v:shapes="_x0000_i1025">

 

1:         Change string to numeric:

 

<IMG src="file://C:\DOCUME~1\Owner\LOCALS~1\Temp\msohtml1\01\clip_image004.jpg" width=311 height=166 v:shapes="_x0000_i1026">

 

<IMG src="file://C:\DOCUME~1\Owner\LOCALS~1\Temp\msohtml1\01\clip_image006.jpg" width=308 height=164 v:shapes="_x0000_i1027">

 

Press OK

 

<IMG src="file://C:\DOCUME~1\Owner\LOCALS~1\Temp\msohtml1\01\clip_image008.jpg" width=623 height=132 v:shapes="_x0000_i1028">

 

Click data View to check:

 

<IMG src="file://C:\DOCUME~1\Owner\LOCALS~1\Temp\msohtml1\01\clip_image010.jpg" width=623 height=168 v:shapes="_x0000_i1029">

 

generate (in syntax) three new variables for day, month, year:

 

compute day = trunc (yourdate/10000).

compute month = trunc (yourdate/100)-day*100.

compute year = mod (yourdate,100) + 2000.

if (year ge 2011) year = year -100.

 

list day month year.

 

<IMG src="file://C:\DOCUME~1\Owner\LOCALS~1\Temp\msohtml1\01\clip_image012.jpg" width=464 height=230 v:shapes="_x0000_i1030">

 

Run the job to get:

 

C

 

 

 

 

yourdate day month year

 

  130310  13    3  2010

   71240   7   12  1940

 

 

Number of cases read:  2    Number of cases listed:  2

 

So far so good.

 

<IMG src="file://C:\DOCUME~1\Owner\LOCALS~1\Temp\msohtml1\01\clip_image014.jpg" width=585 height=152 v:shapes="_x0000_i1031">

 

The next bit uses the menus:

 

Transform…

            Date and time wizard

 

<IMG src="file://C:\DOCUME~1\Owner\LOCALS~1\Temp\msohtml1\01\clip_image016.jpg" width=414 height=259 v:shapes="_x0000_i1032">

 

Click on the 3^rd button:

 

<IMG src="file://C:\DOCUME~1\Owner\LOCALS~1\Temp\msohtml1\01\clip_image018.jpg" width=418 height=262 v:shapes="_x0000_i1033">

 

<IMG src="file://C:\DOCUME~1\Owner\LOCALS~1\Temp\msohtml1\01\clip_image020.jpg" width=414 height=260 v:shapes="_x0000_i1034">

 

<IMG src="file://C:\DOCUME~1\Owner\LOCALS~1\Temp\msohtml1\01\clip_image022.jpg" width=418 height=263 v:shapes="_x0000_i1035">

 

SPSS generates the following syntax:

 

* Date and Time Wizard: newdate.

COMPUTE newdate = DATE.DMY(day, month, year).

VARIABLE LABEL newdate "New date in date format".

VARIABLE LEVEL newdate (SCALE).

FORMATS newdate (EDATE10).

VARIABLE WIDTH newdate(10).

EXECUTE.

 

..and the dates have been added in the correct format.

 

<IMG src="file://C:\DOCUME~1\Owner\LOCALS~1\Temp\msohtml1\01\clip_image024.jpg" width=585 height=151 v:shapes="_x0000_i1036">

 

First time I've done this with the Windowes version, so I;ve learned something as well.  Hope it helps.

 

John Hall

 

 

 

----- Original Message -----

From: [hidden email]

To: [hidden email]

Sent: Saturday, March 13, 2010 6:04 AM

Subject: Change string to date variable

Hi all,

My dataset contains a string variable for day, month, and year without any separating symbols, e.g. 130310.  How can I change it into a date format?

Many thanks,

Rhonda

 

[Ruben Geert van den Berg]

Hi Rhonda!

The '10' in your date variable refers to 2010, not to 1910 (or 1110 or whatever...)? Assuming all dates are in the 21st century, the syntax below should work.

HTH,

Ruben van den Berg

Methodologist

TNS NIPO

E: [hidden email]

P: [phone]

I: www.tns-nipo.com

data list free/stringdate(a6).

begin data

130310

end data.

comp date=date.dmy((num(sub(stringdate,1,2),F2)),

(num(sub(stringdate,3,2),F2)),(num(con("20",sub(stringdate,5,2)),f4))).

for date (edate10).

exe.

---

Date: Sat, 13 Mar 2010 15:04:26 +1000
From: [hidden email]
Subject: Change string to date variable
To: [hidden email]

Hi all,

My dataset contains a string variable for day, month, and year without any separating symbols, e.g. 130310.  How can I change it into a date format?

Many thanks,

Rhonda

 

---

New Windows 7: Simplify what you do everyday. Find the right PC for you.

[Ruben Geert van den Berg]

Dear Rhonda and John,

The syntax I posted is very basic and almost all of it can be found under the 'Universals' section in the Command Syntax Reference (especially string manipulations and string to numeric conversions). I just crammed a lot of commands into a single 'compute'. I stole (uh...borrowed) the basic idea from Raynald Levesque (here http://www.spsstools.net/LearningSyntax.htm#DateTutorial ). 

If you break it down (see below) and run it in steps, you'll see it's very basic.

Best regards,

Ruben van den Berg

Methodologist

TNS NIPO

E: [hidden email]

P: [phone]

I: www.tns-nipo.com

*Start.

data list free/stringdate(a6).

begin data

130310

end data.

string stringday stringmonth stringyear(a2).

compute stringday=substring(stringdate,1,2).

compute stringmonth=substring(stringdate,3,2).

compute stringyear=substring(stringdate,5,2).

execute.

string stringyearfull (a4).

compute stringyearfull =concatenate("20",stringyear).

execute.

compute day=numeric(stringday,f2).

compute month=numeric(stringmonth,f2).

compute year=numeric(stringyearfull,f4).

execute.

compute date=date.dmy(day,month,year).

execute.

format date(edate10).

*End.

---

From: [hidden email]
To: [hidden email]
Subject: RE: Change string to date variable
Date: Sun, 14 Mar 2010 11:00:34 +1000

Hi Ruben

 

Magic solution!  I don’t have a clue how/why it worked, but it did.  Your knowledge of spss syntax is impressive, mine is quite limited but I am adding to it gradually.

Thank you for your help.

 

Rhonda

 

From: Ruben van den Berg [mailto:[hidden email]]
Sent: Sunday, 14 March 2010 6:24 AM
To: [hidden email]; SPSS mailing list
Subject: RE: Change string to date variable

 

Hi Rhonda!

 

The '10' in your date variable refers to 2010, not to 1910 (or 1110 or whatever...)? Assuming all dates are in the 21st century, the syntax below should work.

 

HTH,

 

Ruben van den Berg

Methodologist

TNS NIPO

E: [hidden email]

P: [phone]

I: www.tns-nipo.com

 

data list free/stringdate(a6).

begin data

130310

end data.

 

comp date=date.dmy((num(sub(stringdate,1,2),F2)),

(num(sub(stringdate,3,2),F2)),(num(con("20",sub(stringdate,5,2)),f4))).

 

for date (edate10).

 

exe.

 

 

 

---

Date: Sat, 13 Mar 2010 15:04:26 +1000
From: [hidden email]
Subject: Change string to date variable
To: [hidden email]

Hi all,

My dataset contains a string variable for day, month, and year without any separating symbols, e.g. 130310.  How can I change it into a date format?

Many thanks,

Rhonda

 

 

---

New Windows 7: Simplify what you do everyday. Find the right PC for you.

---

New Windows 7: Find the right PC for you. Learn more.

[Jarrod Teo-2]

Dear Rhonda,
 
The next time your date is in the following string format, 13/03/10, you can consider the following. Be warn that the original string variable will be replaced with the new date variable. 
 
data list free/stringdate(a8).
begin data
13/03/10
end data.
 
ALTER TYPE stringdate (A8=DATE8).

Regards
Dorraj Oet
 

---

Date: Sun, 14 Mar 2010 11:16:04 +0000
From: [hidden email]
Subject: Re: Change string to date variable
To: [hidden email]

Dear Rhonda and John,

The syntax I posted is very basic and almost all of it can be found under the 'Universals' section in the Command Syntax Reference (especially string manipulations and string to numeric conversions). I just crammed a lot of commands into a single 'compute'. I stole (uh...borrowed) the basic idea from Raynald Levesque (here http://www.spsstools.net/LearningSyntax.htm#DateTutorial ). 

If you break it down (see below) and run it in steps, you'll see it's very basic.

Best regards,

Ruben van den Berg

Methodologist

TNS NIPO

E: [hidden email]

P: [phone]

I: www.tns-nipo.com

*Start.

data list free/stringdate(a6).

begin data

130310

end data.

string stringday stringmonth stringyear(a2).

compute stringday=substring(stringdate,1,2).

compute stringmonth=substring(stringdate,3,2).

compute stringyear=substring(stringdate,5,2).

execute.

string stringyearfull (a4).

compute stringyearfull =concatenate("20",stringyear).

execute.

compute day=numeric(stringday,f2).

compute month=numeric(stringmonth,f2).

compute year=numeric(stringyearfull,f4).

execute.

compute date=date.dmy(day,month,year).

execute.

format date(edate10).

*End.

---

From: [hidden email]
To: [hidden email]
Subject: RE: Change string to date variable
Date: Sun, 14 Mar 2010 11:00:34 +1000

Hi Ruben

 

Magic solution!  I don’t have a clue how/why it worked, but it did.  Your knowledge of spss syntax is impressive, mine is quite limited but I am adding to it gradually.

Thank you for your help.

 

Rhonda

 

From: Ruben van den Berg [mailto:[hidden email]]
Sent: Sunday, 14 March 2010 6:24 AM
To: [hidden email]; SPSS mailing list
Subject: RE: Change string to date variable

 

Hi Rhonda!

 

The '10' in your date variable refers to 2010, not to 1910 (or 1110 or whatever...)? Assuming all dates are in the 21st century, the syntax below should work.

 

HTH,

 

Ruben van den Berg

Methodologist

TNS NIPO

E: [hidden email]

P: [phone]

I: www.tns-nipo.com

 

data list free/stringdate(a6).

begin data

130310

end data.

 

comp date=date.dmy((num(sub(stringdate,1,2),F2)),

(num(sub(stringdate,3,2),F2)),(num(con("20",sub(stringdate,5,2)),f4))).

 

for date (edate10).

 

exe.

 

 

 

---

Date: Sat, 13 Mar 2010 15:04:26 +1000
From: [hidden email]
Subject: Change string to date variable
To: [hidden email]

Hi all,

My dataset contains a string variable for day, month, and year without any separating symbols, e.g. 130310.  How can I change it into a date format?

Many thanks,

Rhonda

 

 

---

New Windows 7: Simplify what you do everyday. Find the right PC for you.

---

New Windows 7: Find the right PC for you. Learn more.

---

Hotmail: Trusted email with powerful SPAM protection. Sign up now.

[Richard Ristow]

At 01:34 PM 3/14/2010, DorraJ Oet wrote:

If your date is in the string format [dd/mm/yy, e.g.], 13/03/10, consider the following.

data list free/stringdate(a8).
begin data
13/03/10
end data.
 
ALTER TYPE stringdate (A8=DATE8).

Be warned that the original string variable will be replaced with the new date variable.

For that reason, I strongly advise against ALTER TYPE: you do replace the original, and lose information that may be crucial.

To start with, if you make a mistake about the format (if, say, it's actually "mm/dd/yy"), you lose the values and can't recover.

Then, you lose the original values of any strings that can't be converted because of syntax errors; you can neither diagnose the problem, nor recover if, say, the date value can be recognized by eye.

Instead (not tested),

NUMERIC RealDate (DATE11) /* recommended format */.
COMPUTE RealDate=NUMBER(stringdate,DATE8).

-Best of luck,
 Richard
===================== 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

[Zdaniuk, Bozena-3]

Could someone be so kind and remind me briefly what is the main reason for the adjusted R in multivariate regression to be considerably smaller than the regular R? Does it have to do with a number of variables? I have 9 predictors in my model and 190 people in my sample and my adjusted R is half the size of my regular R (.06 vs .12).

Thanks so much in advance.

Bozena

 

Bozena Zdaniuk, Ph.D.

Human Early Learning Partnership (HELP)
College for Interdisciplinary Studies
UBC - UVic - SFU - UNBC - TRU - UBCO
440 - 2206 East Mall, Vancouver BC  V6T 1Z3
Tel: [phone] / Fax: [phone]
www.earlylearning.ubc.ca

[Bruce Weaver]

In "Statistical Methods for Psychology", Dave Howell puts it this way:  Multiple regression "does its best to account for every bump and wiggle (including sampling error) in the data" (Howell 1997, p. 543). Therefore, R-squared is biased high, and the adjustment to R-squared is an attempt to fix this.

Looking at it another way:

"People often assume that if there is no relation between the criterion and the predictors, R should come out near 0.  In fact, the expected value of R for random data is [the number of predictor variables] / (N – 1).” (Howell, 2007, p. 506)

So if p = 9 predictors and N = 190 cases, the expected value of R (when there is no relationship between predictors and outcome) is 9 / 189 = .048, not 0.  That's not too far off of the .06 difference you're seeing between R^2 and Adjusted R^2.

HTH.

Bozena Zdaniuk wrote

Could someone be so kind and remind me briefly what is the main reason for
the adjusted R in multivariate regression to be considerably smaller than
the regular R? Does it have to do with a number of variables? I have 9
predictors in my model and 190 people in my sample and my adjusted R is
half the size of my regular R (.06 vs .12).

Thanks so much in advance.

Bozena



Bozena Zdaniuk, Ph.D.

Human Early Learning Partnership (HELP)
College for Interdisciplinary Studies
UBC - UVic - SFU - UNBC - TRU - UBCO
440 - 2206 East Mall, Vancouver BC  V6T 1Z3
Tel: [phone] / Fax: [phone]
 <http://www.earlylearning.ubc.ca> www.earlylearning.ubc.ca

[statisticsdoc]

Adjusted r tries to take into consideration the fact that your regression model is overfitted to a particular sample and would likely be smaller across samples. The potential for overfittng is greater as the number of variables in the model increases, so the adjustment is greater for models with vmore variables.
HTH
Steve Brand

www.StatisticsDoc.com

---

From: Bozena Zdaniuk <[hidden email]>

Date: Tue, 16 Mar 2010 13:49:44 -0700

To: <[hidden email]>

Subject: adjusted r vs regular r

Could someone be so kind and remind me briefly what is the main reason for the adjusted R in multivariate regression to be considerably smaller than the regular R? Does it have to do with a number of variables? I have 9 predictors in my model and 190 people in my sample and my adjusted R is half the size of my regular R (.06 vs .12).

Thanks so much in advance.

Bozena

 

Bozena Zdaniuk, Ph.D.

Human Early Learning Partnership (HELP)
College for Interdisciplinary Studies
UBC - UVic - SFU - UNBC - TRU - UBCO
440 - 2206 East Mall, Vancouver BC  V6T 1Z3
Tel: [phone] / Fax: [phone]
www.earlylearning.ubc.ca

[Art Kendall]

Excellent point about not writing over data.

Art Kendall
Social Research Consultants


On 3/16/2010 4:40 PM, Richard Ristow wrote:

At 01:34 PM 3/14/2010, DorraJ Oet wrote:

If your date is in the string format [dd/mm/yy, e.g.], 13/03/10, consider the following.

data list free/stringdate(a8).
begin data
13/03/10
end data.
 
ALTER TYPE stringdate (A8=DATE8).

Be warned that the original string variable will be replaced with the new date variable.

For that reason, I strongly advise against ALTER TYPE: you do replace the original, and lose information that may be crucial.

To start with, if you make a mistake about the format (if, say, it's actually "mm/dd/yy"), you lose the values and can't recover.

Then, you lose the original values of any strings that can't be converted because of syntax errors; you can neither diagnose the problem, nor recover if, say, the date value can be recognized by eye.

Instead (not tested),

NUMERIC RealDate (DATE11) /* recommended format */.
COMPUTE RealDate=NUMBER(stringdate,DATE8).

-Best of luck,
 Richard
===================== 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

[Jon K Peck]

Jon Peck
SPSS, an IBM Company
[hidden email]
312-651-3435

From:	Richard Ristow <[hidden email]>
To:	[hidden email]
Date:	03/16/2010 02:44 PM
Subject:	Re: [SPSSX-L] Change string to date variable
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

---

At 01:34 PM 3/14/2010, DorraJ Oet wrote:

If your date is in the string format [dd/mm/yy, e.g.], 13/03/10, consider the following.

data list free/stringdate(a8).
begin data
13/03/10
end data.

ALTER TYPE stringdate (A8=DATE8).

Be warned that the original string variable will be replaced with the new date variable.

For that reason, I strongly advise against ALTER TYPE: you do replace the original, and lose information that may be crucial.
>>>It's simple enough to make a copy of the string before altering the type if an error will not be recoverable.  ALTER TYPE has a number of functional properties hard to duplicate with other commands.

Regards,
Jon Peck

To start with, if you make a mistake about the format (if, say, it's actually "mm/dd/yy"), you lose the values and can't recover.

Then, you lose the original values of any strings that can't be converted because of syntax errors; you can neither diagnose the problem, nor recover if, say, the date value can be recognized by eye.

Instead (not tested),

NUMERIC RealDate (DATE11) /* recommended format */.
COMPUTE RealDate=NUMBER(stringdate,DATE8).

-Best of luck,
Richard

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

[<R. Abraham>]

Hello List,
AAA Life Insurance Co is looking to hire a statistician for a temporary job position (1 month). The candidate must possess strong skills and experience in predictive response modeling,  and in using 'SPSS' and 'Modeler' tools. The responsibilities would include building response models for insurance products, and also imparting knowledge and skills in modeling.

AAA Life Insurance Company is located in Livonia, MI. If you are interested please respond to me with your resume.

Thank you.

Renji Abraham

[Art Kendall]

Making a copy of a variable and transforming the copy is indeed a good way to avoid writing over original data.  I concur that ALTER TYPE is a very useful command when used in such a way that one can go back and revise one's procedure.  If my years at the General Accounting Office did nothing else they reinforced my ideas of the importance of the "audit trail" and the iterative nature of all kinds of writing including syntax.

Art Kendall
Social Research Consultants

On 3/17/2010 8:57 AM, Jon K Peck wrote:

Jon Peck
SPSS, an IBM Company
[hidden email]
312-651-3435

From:	Richard Ristow [hidden email]
To:	[hidden email]
Date:	03/16/2010 02:44 PM
Subject:	Re: [SPSSX-L] Change string to date variable
Sent by:	"SPSSX(r) Discussion" [hidden email]

---

At 01:34 PM 3/14/2010, DorraJ Oet wrote:

If your date is in the string format [dd/mm/yy, e.g.], 13/03/10, consider the following.

data list free/stringdate(a8).
begin data
13/03/10
end data.

ALTER TYPE stringdate (A8=DATE8).

Be warned that the original string variable will be replaced with the new date variable.

For that reason, I strongly advise against ALTER TYPE: you do replace the original, and lose information that may be crucial.
>>>It's simple enough to make a copy of the string before altering the type if an error will not be recoverable.  ALTER TYPE has a number of functional properties hard to duplicate with other commands.

Regards,
Jon Peck

To start with, if you make a mistake about the format (if, say, it's actually "mm/dd/yy"), you lose the values and can't recover.

Then, you lose the original values of any strings that can't be converted because of syntax errors; you can neither diagnose the problem, nor recover if, say, the date value can be recognized by eye.

Instead (not tested),

NUMERIC RealDate (DATE11) /* recommended format */.
COMPUTE RealDate=NUMBER(stringdate,DATE8).

-Best of luck,
Richard

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

[Mike Pritchard]

I’m banging my head against the wall on this – unfortunately after my earlier attempts using NVALID on a different variable solved some of my problems with DO IF.

 

I have a string variable that has some text, and also numbers as strings.  The variable has missing values too, or perhaps I should say blank strings.   I want to extract the text values and place into another variable, so that I can then convert the source variable to a numeric. The syntax I’m trying is like this

 

STRING VarStringTarget (A250).

EXECUTE.

    DO IF (NVALID(VarStringSource)>0).

       DO IF (SYSMIS(NUMBER(VarStringSource,F5.0))).

           COMPUTE VarStringTarget = VarStringSource.

       END IF.

    END IF.

EXECUTE.

 

This generates Error 4310 for the NVALID line – “The numeric argument required for the function specified was not supplied.  This command not executed.”

Then the first text value (that doesn’t convert to a number) generates Warning 1102, but the Target variable is written.

Then the empty strings generate Warning 635s

 

I’ve tried several other approaches, but nothing is working, so if someone can point me in the right direction I’d be grateful.

 

Thanks

Mike

[Bruce Weaver]

Mike Pritchard wrote

I'm banging my head against the wall on this - unfortunately after my
earlier attempts using NVALID on a different variable solved some of my
problems with DO IF.

I have a string variable that has some text, and also numbers as strings.
The variable has missing values too, or perhaps I should say blank strings.
I want to extract the text values and place into another variable, so that I
can then convert the source variable to a numeric. The syntax I'm trying is
like this

--- snip ---

Mike, please post some examples of the string variable.  

Q. Do you need the text portions that you are trying to extract for something else, or are you just trying to get rid of them?  

If you only want to get rid of them, I would try something like this:

1. Convert the string variable to uppercase with the UPCASE function.  If you want to preserve the original variable as is, make the uppercase version as a new variable.
2. Use REPLACE to replace all of the possible non-numeric characters with null strings.  This will leave only numeric characters.
3. Convert from string to numeric.

HTH.

[Mike Pritchard]

Thanks Bruce.

Examples of the variable content:

'Need for reimbursement','8','5','4'.....    (separate records between
commas)
The numeric values are within a set of known values - 1 to up to 10 or so
depending on which variable.

I've just discovered a new wrinkle.  The variable type depends on the
content when imported, so if there are no text values the variable is
numeric.  I presume this is a result of the import from Excel.  I don't know
if I could fix this if I was doing something similar later, but I have quite
a bit of work into the SPSS file so I'd rather not import again.  I suppose
I could merge if necessary, but I hope I'll be able to do what I need within
SPSS syntax.  But it points out my ignorance.  I don't know how to test
within syntax for variable types, or to properly test for content - which
was the original point.

Back to your suggestions.  I do need both the original text if it was a
string and the converted numbers.  But they can go into new variables, and
that's what I'm experimenting with.  I also need to do more processing
afterwards.  At the risk of getting into too much detail, the way these
questions worked was with question showing a set of options for reasons on a
particular topic, followed by a question for the top reason - but only if
there was more than one reason for the first question.  The variable that
I'm trying to manipulate is the top reason, so after I've got that figured
out I need to add back the single options.  It worked great for the
respondents, but it is a little trickier for setting up to analyze.

Thanks
Mike

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Bruce Weaver
Sent: Thursday, September 09, 2010 4:50 AM
To: [hidden email]
Subject: Re: Syntax to extract/convert numbers and strings from string
variable

Mike Pritchard wrote:

>
> I'm banging my head against the wall on this - unfortunately after my
> earlier attempts using NVALID on a different variable solved some of my
> problems with DO IF.
>
> I have a string variable that has some text, and also numbers as strings.
> The variable has missing values too, or perhaps I should say blank
> strings.
> I want to extract the text values and place into another variable, so that
> I
> can then convert the source variable to a numeric. The syntax I'm trying
> is
> like this
>
> --- snip ---
>

Mike, please post some examples of the string variable.

Q. Do you need the text portions that you are trying to extract for
something else, or are you just trying to get rid of them?

If you only want to get rid of them, I would try something like this:

1. Convert the string variable to uppercase with the UPCASE function.  If
you want to preserve the original variable as is, make the uppercase version
as a new variable.
2. Use REPLACE to replace all of the possible non-numeric characters with
null strings.  This will leave only numeric characters.
3. Convert from string to numeric.

HTH.


-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
View this message in context:
http://spssx-discussion.1045642.n5.nabble.com/Change-string-to-date-variable
-tp1075756p2827328.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

[Maguin, Eugene]

Mike,

I'd like to spectate a bit on your problem. I saw your posting yesterday and
then Bruce's request for more info and now your reply. Let me say that I
don't quite understand why you are trying to do what you are trying to.
Therefore, my comment may be useless. But. Why can't you read this file
using either the data list or get data commands. It looks like you have a
comma delimited file with the added pain of apostrophes surrounding text
data, which, if you are exporting from something I'd get rid of those
apostrophes. I'd open the file in word, for example, and strip out the
apostrophes (I don't recall that spss can handle them correctly but I've
never needed to read in to spss this kind of data structure) and resave it a
text. The either get data or data list. So the result, I think, would be a
file like this

Varname (A30, say) val1 to val10(F2.0, say)

You could use varstocases to reform the file if you need that done.

If any of this is useful to you, then good; otherwise, skip it.

Gene Maguin




-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Mike Pritchard
Sent: Thursday, September 09, 2010 12:49 PM
To: [hidden email]
Subject: Re: Syntax to extract/convert numbers and strings from string
variable

Thanks Bruce.

Examples of the variable content:

'Need for reimbursement','8','5','4'.....    (separate records between
commas)
The numeric values are within a set of known values - 1 to up to 10 or so
depending on which variable.

I've just discovered a new wrinkle.  The variable type depends on the
content when imported, so if there are no text values the variable is
numeric.  I presume this is a result of the import from Excel.  I don't know
if I could fix this if I was doing something similar later, but I have quite
a bit of work into the SPSS file so I'd rather not import again.  I suppose
I could merge if necessary, but I hope I'll be able to do what I need within
SPSS syntax.  But it points out my ignorance.  I don't know how to test
within syntax for variable types, or to properly test for content - which
was the original point.

Back to your suggestions.  I do need both the original text if it was a
string and the converted numbers.  But they can go into new variables, and
that's what I'm experimenting with.  I also need to do more processing
afterwards.  At the risk of getting into too much detail, the way these
questions worked was with question showing a set of options for reasons on a
particular topic, followed by a question for the top reason - but only if
there was more than one reason for the first question.  The variable that
I'm trying to manipulate is the top reason, so after I've got that figured
out I need to add back the single options.  It worked great for the
respondents, but it is a little trickier for setting up to analyze.

Thanks
Mike

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Bruce Weaver
Sent: Thursday, September 09, 2010 4:50 AM
To: [hidden email]
Subject: Re: Syntax to extract/convert numbers and strings from string
variable

Mike Pritchard wrote:

>
> I'm banging my head against the wall on this - unfortunately after my
> earlier attempts using NVALID on a different variable solved some of my
> problems with DO IF.
>
> I have a string variable that has some text, and also numbers as strings.
> The variable has missing values too, or perhaps I should say blank
> strings.
> I want to extract the text values and place into another variable, so that
> I
> can then convert the source variable to a numeric. The syntax I'm trying
> is
> like this
>
> --- snip ---
>

Mike, please post some examples of the string variable.

Q. Do you need the text portions that you are trying to extract for
something else, or are you just trying to get rid of them?

If you only want to get rid of them, I would try something like this:

1. Convert the string variable to uppercase with the UPCASE function.  If
you want to preserve the original variable as is, make the uppercase version
as a new variable.
2. Use REPLACE to replace all of the possible non-numeric characters with
null strings.  This will leave only numeric characters.
3. Convert from string to numeric.

HTH.


-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
View this message in context:
http://spssx-discussion.1045642.n5.nabble.com/Change-string-to-date-variable
-tp1075756p2827328.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

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

[Art Kendall]

  perhaps you could
-- write the string out into a new file.
-- read it in.
I put this in a notepad file.
'Need for reimbursement','8','5','4'
'something','1','2','3','4','5'

then using <file> <get text data>
I generated this.
GET DATA
   /TYPE=TXT
   /FILE="C:\Users\Art\Desktop\demo.txt"
   /DELCASE=LINE
   /DELIMITERS=","
   /QUALIFIER="'"
   /ARRANGEMENT=DELIMITED
   /FIRSTCASE=1
   /IMPORTCASE=ALL
   /VARIABLES=
   V1 A22
   V2 F1.0
   V3 F1.0
   V4 F1.0
   V5 F1.0
   V6 F1.0.
CACHE.
EXECUTE.
DATASET NAME DataSet1 WINDOW=FRONT.

I obtained this variable view
V1    String    22    0        None    None    22    Left    Nominal
Input
V2    Numeric    1    0        None    None    8    Right    Nominal
Input
V3    Numeric    1    0        None    None    8    Right    Nominal
Input
V4    Numeric    1    0        None    None    8    Right    Nominal
Input
V5    Numeric    1    0        None    None    8    Right    Nominal
Input
V6    Numeric    1    0        None    None    8    Right    Nominal
Input

and this data view
Need for reimbursement    8    5    4
something                 1    2    3    4    5

The wizard figured out string vs numeric even though the fields were all
single quoted.

You probably want to write and ID so you can safely MATCH the new file
with the original file.


Art Kendall
Social research consultants,

On 9/9/2010 12:49 PM, Mike Pritchard wrote:

> Thanks Bruce.
>
> Examples of the variable content:
>
> 'Need for reimbursement','8','5','4'.....    (separate records between
> commas)
> The numeric values are within a set of known values - 1 to up to 10 or so
> depending on which variable.
>
> I've just discovered a new wrinkle.  The variable type depends on the
> content when imported, so if there are no text values the variable is
> numeric.  I presume this is a result of the import from Excel.  I don't know
> if I could fix this if I was doing something similar later, but I have quite
> a bit of work into the SPSS file so I'd rather not import again.  I suppose
> I could merge if necessary, but I hope I'll be able to do what I need within
> SPSS syntax.  But it points out my ignorance.  I don't know how to test
> within syntax for variable types, or to properly test for content - which
> was the original point.
>
> Back to your suggestions.  I do need both the original text if it was a
> string and the converted numbers.  But they can go into new variables, and
> that's what I'm experimenting with.  I also need to do more processing
> afterwards.  At the risk of getting into too much detail, the way these
> questions worked was with question showing a set of options for reasons on a
> particular topic, followed by a question for the top reason - but only if
> there was more than one reason for the first question.  The variable that
> I'm trying to manipulate is the top reason, so after I've got that figured
> out I need to add back the single options.  It worked great for the
> respondents, but it is a little trickier for setting up to analyze.
>
> Thanks
> Mike
>
> -----Original Message-----
> From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
> Bruce Weaver
> Sent: Thursday, September 09, 2010 4:50 AM
> To: [hidden email]
> Subject: Re: Syntax to extract/convert numbers and strings from string
> variable
>
> Mike Pritchard wrote:
>> I'm banging my head against the wall on this - unfortunately after my
>> earlier attempts using NVALID on a different variable solved some of my
>> problems with DO IF.
>>
>> I have a string variable that has some text, and also numbers as strings.
>> The variable has missing values too, or perhaps I should say blank
>> strings.
>> I want to extract the text values and place into another variable, so that
>> I
>> can then convert the source variable to a numeric. The syntax I'm trying
>> is
>> like this
>>
>> --- snip ---
>>
> Mike, please post some examples of the string variable.
>
> Q. Do you need the text portions that you are trying to extract for
> something else, or are you just trying to get rid of them?
>
> If you only want to get rid of them, I would try something like this:
>
> 1. Convert the string variable to uppercase with the UPCASE function.  If
> you want to preserve the original variable as is, make the uppercase version
> as a new variable.
> 2. Use REPLACE to replace all of the possible non-numeric characters with
> null strings.  This will leave only numeric characters.
> 3. Convert from string to numeric.
>
> HTH.
>
>
> -----
> --
> Bruce Weaver
> [hidden email]
> http://sites.google.com/a/lakeheadu.ca/bweaver/
>
> "When all else fails, RTFM."
>
> NOTE: My Hotmail account is not monitored regularly.
> To send me an e-mail, please use the address shown above.
>
> --
> View this message in context:
> http://spssx-discussion.1045642.n5.nabble.com/Change-string-to-date-variable
> -tp1075756p2827328.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
>

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

[Mike Pritchard]

Thanks to Gene, Art, and Bruce for suggestions.  I'm not sure that I
understood everything suggested (some ideas seemed to need me to get up to
speed on some deeper aspects of SPSS syntax), but I wanted to give a quick
update on the eventual resolution.

I changed all the source variables to string type.  Then ran a piece of
syntax that had earlier caused problems because of the different variable
types.   I still wish there was an easy way to detect the variable type in
syntax (I'm using V16).

The eventual code looks like this - including the punch of the top option
chosen.  I'm sure it could be more elegant/efficient, but it worked.

DELETE VARIABLES Q4aCategory_Top.
STRING Q4aCategory_Top (A250).
COMPUTE Q4aCategory_Top = Q4aCategory.
EXECUTE.
ALTER TYPE Q4aCategory_Top (F5.0).
DO IF (CHAR.LENGTH(Q4aCategory)>2).
     COMPUTE Q4aCategory_Top = 97.
END IF.
EXECUTE.
COUNT #temp =  Q4Category_1 Q4Category_2 Q4Category_3 Q4Category_4
Q4Category_5 Q4Category_6 Q4Category_7 Q4Category_8 Q4Category_9
Q4Category_10 Q4Category_97 (1).
DO IF (#temp=1).
   IF (Q4Category_1=1) Q4aCategory_Top=1.
   IF (Q4Category_2=1) Q4aCategory_Top=2.
   IF (Q4Category_3=1) Q4aCategory_Top=3.
   IF (Q4Category_4=1) Q4aCategory_Top=4.
   IF (Q4Category_5=1) Q4aCategory_Top=5.
   IF (Q4Category_6=1) Q4aCategory_Top=6.
   IF (Q4Category_7=1) Q4aCategory_Top=7.
   IF (Q4Category_8=1) Q4aCategory_Top=8.
   IF (Q4Category_9=1) Q4aCategory_Top=9.
   IF (Q4Category_10=1) Q4aCategory_Top=10.
   IF (Q4Category_97=1) Q4aCategory_Top=97.
END IF.
EXECUTE.

The test with CHAR.LENGTH is a feeble attempt to create a code for text in
the original variable.  It worked, but only because respondents who entered
text used more than 2 characters - the maximum for the original codes.

There was one variable that still caused problems because all cases passed
this test.   I wasn't able to see what the issue was caused by, but when I
cleared the contents (they already looked clear to me) the syntax worked.

Thanks again!
Mike

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