Problem converting string to numeric
Locked
Problem converting string to numeric
 
|
Dear all,
I'm trying to create a table that provides information on a number of variables. Specifically, I want to compare dictionary information with frequency information (are all values present in the data labelled? are there any labelled values not present in the data? how many different values are present in the data/dictionary?) etcetera. I create a small test data set in order to keep things under control. However, SPSS refuses to convert a string (the values from the frequency table) into numeric (in order to be used as a key variable for matching the datasets). My suspicion is that it may have something to do with the decimal separator being a comma instead of a period but after replacing it, I get the same error messages. Could anyone please have a look at my syntax? Since it's rather long, I've indicated where things turn bad with [hidden email] and [hidden email]. Fortunately, I've a plan B for now (convert the numeric in the other dataset to string) but I am curious why plan A doesn't work. Thanks a lot in advance and thanks once again to those who provided me with such wonderful assistance over the last couple of weeks!! Ruben van den Berg Amsterdam, the Netherlands ***Begin of syntax:. DATAS CLO ALL. CD 'C:\Tests'. SET TNU VAL. SET SEED=123456. NEW FIL. INP PRO. LOOP #I=1 to 60. COMP ID=#I. END CAS. END LOOP. END FIL. END INP PRO. EXE. DATAS NAM Testdata. COMP sex=RND(RV.UNI(1,2)). COMP age=RND(RV.UNI(1,5)). COMP status=RND(RV.UNI(0,3)). EXE. VAL LAB sex 1 Male 2 Female /age 1 18-36 years 2 37-54 years 3 55-72 years 4 73 years and over 99 Unknown /status 1 married 2 bachelor 3 divorced or widowed. DATASET DECLARE Freq. OMS /SELECT TABLES /IF COMMANDS = ["Frequencies"] SUBTYPES = ["Frequencies"] /DESTINATION FORMAT = SAV NUMBERED = TableNumber_ OUTFILE = Freq VIEWER = NO /TAG = "Freq". OMS /SELECT TABLES /IF COMMANDS = ["File Information"] SUBTYPES = ["Variable Labels"] /DESTINATION VIEWER = NO /TAG = "Varlab". DATASET DECLARE Vallab. OMS /SELECT TABLES /IF COMMANDS = ["File Information"] SUBTYPES = ["Variable Values"] /DESTINATION FORMAT = SAV NUMBERED = TableNumber_ OUTFILE = Vallab VIEWER = NO /TAG = "Vallab". DISP DIC. FRE sex age status. OMSEND. DATAS ACT Vallab. REN VAR (Var1 Var2=Variable Value). DATAS ACT Freq. SEL IF Var2 NE "Total". EXE. *@1 Attempt 1:. COMP Value=NUM(Var2,N4). EXE. *@1 "Invalid numeric field" due to comma separator??. *@2 Attempt 2:. STR Var2B(A5). COMP Var2B=REPLACE(Var2,",","."). EXE. COMP Value=NUM(Var2B,N4). EXE. *@2 Doesn't work either :-(. MATC FIL FIL Freq /FIL Vallab /BY Variable Value. DATAS NAM Combi. DATAS CLO Freq. DATAS CLO Vallab. What can you do with the new Windows Live? Find out |
|
For starters,
there is a function, FindUnlabelledValues, in the spssaux2 module that will do
what it says. Here's an
example that checks all categorical variables along with the sample output. begin program. import spss, spssaux, spssaux2 unlabelledvars = spssaux2.FindUnlabelledValues( spssaux.VariableDict(variableLevel=["nominal",
"ordinal"])) print unlabelledvars end program. {u'jobcat': [17.0], u'educ': [25.0], u'minority':
[], u'gender': [u'x', u'z']} The output lists for each
variable the values that are unlabelled. (The u indicates Unicode.) Variables that have no
value labels at all are not checked. To check variables
without the measurement level filter, you could just omit the variableLevel
parameter. E.g., unlabelledvars = spssaux2.FindUnlabelledValues(spssaux.VariableDict()) This could also
be done using the Data Validation option using the rule that prohibits
unlabelled values. If you need to
convert a string to numeric, the easiest way is the ALTER TYPE command. If
your strings have comma decimals, you need to have your SPSS locale set to a
comma decimal locale for the values to be understood, but if you are capturing
a frequency table with OMS, the values in the table would already be numeric. You might also
find the new CODEBOOK command in v 17 helpful. HTH, Jon Peck From: SPSSX(r)
Discussion [mailto:[hidden email]] On
Behalf Of Dear all, What can you do with the new Windows Live? Find
out |
|
|
In reply to this post by Ruben Geert van den Berg
COMP Value=NUM(Var2,N4).
EXE. * 'F' instead of 'N' . compute value=numeric(Var2,f4.0). exe. Does this work? If no th I'm no sur wha to sugg. (Sorry, couldn't resist). -Gary On Sat, May 23, 2009 at 1:17 PM, Ruben van den Berg <[hidden email]> wrote: > Dear all, > > I'm trying to create a table that provides information on a number of > variables. Specifically, I want to compare dictionary information with > frequency information (are all values present in the data labelled? are > there any labelled values not present in the data? how many different values > are present in the data/dictionary?) etcetera. I create a small test data > set in order to keep things under control. > > However, SPSS refuses to convert a string (the values from the frequency > table) into numeric (in order to be used as a key variable for matching the > datasets). My suspicion is that it may have something to do with the decimal > separator being a comma instead of a period but after replacing it, I get > the same error messages. Could anyone please have a look at my syntax? Since > it's rather long, I've indicated where things turn bad with *@1 and *@2. > > Fortunately, I've a plan B for now (convert the numeric in the other dataset > to string) but I am curious why plan A doesn't work. > > Thanks a lot in advance and thanks once again to those who provided me with > such wonderful assistance over the last couple of weeks!! > > Ruben van den Berg > Amsterdam, the Netherlands > > ***Begin of syntax:. > > DATAS CLO ALL. > > CD 'C:\Tests'. > > SET TNU VAL. > SET SEED=123456. > > NEW FIL. > > INP PRO. > LOOP #I=1 to 60. > COMP ID=#I. > END CAS. > END LOOP. > END FIL. > END INP PRO. > EXE. > > DATAS NAM Testdata. > > COMP sex=RND(RV.UNI(1,2)). > COMP age=RND(RV.UNI(1,5)). > COMP status=RND(RV.UNI(0,3)). > EXE. > > VAL LAB sex 1 Male > 2 Female > /age 1 18-36 years > 2 37-54 years > 3 55-72 years > 4 73 years and over > 99 Unknown > /status 1 married > 2 bachelor > 3 divorced or widowed. > > DATASET DECLARE Freq. > > OMS > /SELECT TABLES > /IF COMMANDS = ["Frequencies"] > SUBTYPES = ["Frequencies"] > /DESTINATION FORMAT = SAV NUMBERED = TableNumber_ > OUTFILE = Freq > VIEWER = NO > /TAG = "Freq". > > OMS > /SELECT TABLES > /IF COMMANDS = ["File Information"] > SUBTYPES = ["Variable Labels"] > /DESTINATION VIEWER = NO > /TAG = "Varlab". > > DATASET DECLARE Vallab. > OMS > /SELECT TABLES > /IF COMMANDS = ["File Information"] > SUBTYPES = ["Variable Values"] > /DESTINATION FORMAT = SAV NUMBERED = TableNumber_ > OUTFILE = Vallab > VIEWER = NO > /TAG = "Vallab". > > DISP DIC. > > FRE sex age status. > > OMSEND. > > DATAS ACT Vallab. > REN VAR (Var1 Var2=Variable Value). > > DATAS ACT Freq. > SEL IF Var2 NE "Total". > EXE. > > *@1 Attempt 1:. > > COMP Value=NUM(Var2,N4). > EXE. > > *@1 "Invalid numeric field" due to comma separator??. > > *@2 Attempt 2:. > > STR Var2B(A5). > COMP Var2B=REPLACE(Var2,",","."). > EXE. > COMP Value=NUM(Var2B,N4). > EXE. > > *@2 Doesn't work either :-(. > > MATC FIL FIL Freq > /FIL Vallab > /BY Variable Value. > > DATAS NAM Combi. > DATAS CLO Freq. > DATAS CLO Vallab. > > > ________________________________ > What can you do with the new Windows Live? Find out ===================== 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 |
SOLVED: Problem converting string to numeric
|
|
Dear Gary,
That's it indeed! I've no clue why I'd previously used the N format (I guess it did work in some previous attempt). Anyway, it's done. I'll paste the end result below (creating fake data is included). I guess it's not error free yet (e.g., what will happen if strings are included?) but it will be probably useful for my current practical purposes anyway. If anyone wants to try it out, feel free and any suggestions are more than welcome! And thanks again to Jon as well! Kind regards, Ruben van den Berg OMS /SELECT LOGS WARNINGS /DESTINATION FORMAT = TEXT OUTFILE = "Logs.txt" /TAG = "Logs". DATAS CLO ALL. CD 'C:\Tests'. SET TNU VAL TVA NAM. SET SEED=123456. ***Create fake data. NEW FIL. INP PRO. LOOP #I=1 to 60. COMP ID=#I. END CAS. END LOOP. END FIL. END INP PRO. EXE. DATAS NAM Testdata. COMP sex=RND(RV.UNI(1,2)). COMP age=RND(RV.UNI(1,5)). COMP status=RND(RV.UNI(0,3)). EXE. VAL LAB sex 1 Male 2 Female /age 1 18-36 years 2 37-54 years 3 55-72 years 4 73 years and over 99 Unknown /status 0 none of your business! 1 married 2 bachelor 3 divorced or widowed. MIS VAL status (0). ***End create fake data. ***Create 3 datasets with OMS. DATASET DECLARE Freq. OMS /SELECT TABLES /IF COMMANDS = ["Frequencies"] SUBTYPES = ["Frequencies"] /DESTINATION FORMAT = SAV NUMBERED = TableNumber_ OUTFILE = Freq VIEWER = NO /TAG = "Freq". OMS /SELECT TABLES /IF COMMANDS = ["File Information"] SUBTYPES = ["Variable Labels"] /DESTINATION VIEWER = NO /TAG = "Varlab". DATASET DECLARE Vallab. OMS /SELECT TABLES /IF COMMANDS = ["File Information"] SUBTYPES = ["Variable Values"] /DESTINATION FORMAT = SAV NUMBERED = TableNumber_ OUTFILE = Vallab VIEWER = NO /TAG = "Vallab". DATASET DECLARE Desc. OMS /SELECT TABLES /IF COMMANDS = ["Descriptives"] SUBTYPES = ["Descriptive Statistics"] /DESTINATION FORMAT = SAV NUMBERED = TableNumber_ OUTFILE = Desc VIEWER = NO /TAG = "Desc". DISP DIC. FRE sex age status. DES sex age status. OMSEND TAG=['Desc']. OMSEND TAG=['Freq']. OMSEND TAG=['Vallab']. OMSEND TAG=['Varlab']. DATAS CLO Testdata. ***Edit the datasets before merging. ***Dataset Desc. DATAS ACT Desc. SEL IF RTRIM(Var1) NE "Valid N (listwise)". EXE. MATC FIL FIL * /DRO TableNumber_ Command_ Subtype_ Label_ Mean Std.Deviation. STR Variable (A256). COMP Variable=RPAD(RTRIM(Var1),256). EXE. REN VAR (N Minimum Maximum=N_valid Min_valid Max_valid). SOR CAS by Variable (A). ***Dataset Vallab. DATAS ACT Vallab. STR Variable (A256). COMP Variable=RPAD(RTRIM(Var1),256). EXE. REN VAR (Var2= Value). SOR CAS BY Variable Value. ***Dataset Freq. DATAS ACT Freq. SEL IF Var1 NE "Total" AND Var2 NE "Total". EXE. COMP Value=NUM(Var2,F4.2). EXE. STR Variable (A256). COMP Variable=RPAD(RTRIM(Label_),256). EXE. SOR CAS BY Variable Value. ***Match Freq and Vallab. MATC FIL FIL Freq /FIL Vallab /BY Variable Value /KEE Variable Value Label Frequency. EXE. ***'Table lookup with Desc. MATC FIL FIL * /TAB Desc /BY Variable . EXE. DATAS NAM Ext_infotable. ***Close redundant datasets. DATAS CLO Freq. DATAS CLO Vallab. DATAS CLO Desc. ***Edit combined dataset. DATAS ACT Ext_infotable. COMP Unlabelled=0. COMP Empty_label=0. COMP Max_label=0. COMP N_labels=0. COMP Max_value=0. COMP N_values=0. IF (MIS(Frequency)=0 AND Label EQ "") Unlabelled=1. IF (MIS(Frequency)=1 AND Label NE "") Empty_label=1. IF (MIS(Frequency)=0) Max_value=Value. IF (MIS(Frequency)=1) Max_value=Frequency. IF (Label NE "") Max_label=Value. IF (Label EQ "") Max_label=NUM(Label,F1). IF (Label NE "") N_labels=1. IF (MIS(Frequency)=0) N_values=1. EXE. COMP Min_label=Max_label. COMP Min_value=Max_value. EXE. SAV TRA OUT 'EXT_infotable.xls' /FIE /REP. DATAS DEC Com_Infotable. ***. AGG /OUT Com_Infotable /BRE Variable /Unlabelled=SUM(Unlabelled) /Empty_label=SUM(Empty_label) /Min_in_data=MIN(Max_value) /Min_valid=MEAN(Min_valid) /Min_labelled=MIN(Max_label) /Max_in_data=MAX(Max_value) /Max_valid=MEAN(Max_valid) /Max_labelled=MAX(Max_label) /N_values=SUM(N_values) /N_labels=SUM(N_labels) /N_valid=MEAN(N_valid). EXE. DATAS ACT Com_Infotable. VAR LAB Unlabelled 'Number of unlabelled values in data' Empty_label 'Number of labelled values absent in data' Min_in_data 'The lowest value present in data' Min_labelled 'The lowest labelled value' Max_in_data 'The highest value present in data' Max_labelled 'The highest labelled value' N_values 'Total number of different values in data' N_labels 'Total number of different labels' Min_valid 'Minimal valid value present in data' Max_valid 'Maximum valid value present in data' N_valid 'Number of valid observations in data'. SAV TRA OUT 'COM_infotable.xls' /FIE /REP. OMSEND TAG=['Logs']. DATAS CLO ALL. > Date: Sun, 24 May 2009 17:25:29 -0700 > From: [hidden email] > Subject: Re: Problem converting string to numeric > To: [hidden email] > > COMP Value=NUM(Var2,N4). > EXE. > > * 'F' instead of 'N' . > compute value=numeric(Var2,f4.0). > exe. > > Does this work? If no th I'm no sur wha to sugg. (Sorry, couldn't resist). > -Gary > > On Sat, May 23, 2009 at 1:17 PM, Ruben van den Berg > <[hidden email]> wrote: > > Dear all, > > > > I'm trying to create a table that provides information on a number of > > variables. Specifically, I want to compare dictionary information with > > frequency information (are all values present in the data labelled? are > > there any labelled values not present in the data? how many different values > > are present in the data/dictionary?) etcetera. I create a small test data > > set in order to keep things under control. > > > > However, SPSS refuses to convert a string (the values from the frequency > > table) into numeric (in order to be used as a key variable for matching the > > datasets). My suspicion is that it may have something to do with the decimal > > separator being a comma instead of a period but after replacing it, I get > > the same error messages. Could anyone please have a look at my syntax? Since > > it's rather long, I've indicated where things turn bad with *@1 and *@2. > > > > Fortunately, I've a plan B for now (convert the numeric in the other dataset > > to string) but I am curious why plan A doesn't work. > > > > Thanks a lot in advance and thanks once again to those who provided me with > > such wonderful assistance over the last couple of weeks!! > > > > Ruben van den Berg > > Amsterdam, the Netherlands > > > > ***Begin of syntax:. > > > > DATAS CLO ALL. > > > > CD 'C:\Tests'. > > > > SET TNU VAL. > > SET SEED=123456. > > > > NEW FIL. > > > > INP PRO. > > LOOP #I=1 to 60. > > COMP ID=#I. > > END CAS. > > END LOOP. > > END FIL. > > END INP PRO. > > EXE. > > > > DATAS NAM Testdata. > > > > COMP sex=RND(RV.UNI(1,2)). > > COMP age=RND(RV.UNI(1,5)). > > COMP status=RND(RV.UNI(0,3)). > > EXE. > > > > VAL LAB sex 1 Male > > 2 Female > > /age 1 18-36 years > > 2 37-54 years > > 3 55-72 years > > 4 73 years and over > > 99 Unknown > > /status 1 married > > 2 bachelor > > 3 divorced or widowed. > > > > DATASET DECLARE Freq. > > > > OMS > > /SELECT TABLES > > /IF COMMANDS = ["Frequencies"] > > SUBTYPES = ["Frequencies"] > > /DESTINATION FORMAT = SAV NUMBERED = TableNumber_ > > OUTFILE = Freq > > VIEWER = NO > > /TAG = "Freq". > > > > OMS > > /SELECT TABLES > > /IF COMMANDS = ["File Information"] > > SUBTYPES = ["Variable Labels"] > > /DESTINATION VIEWER = NO > > /TAG = "Varlab". > > > > DATASET DECLARE Vallab. > > OMS > > /SELECT TABLES > > /IF COMMANDS = ["File Information"] > > SUBTYPES = ["Variable Values"] > > /DESTINATION FORMAT = SAV NUMBERED = TableNumber_ > > OUTFILE = Vallab > > VIEWER = NO > > /TAG = "Vallab". > > > > DISP DIC. > > > > FRE sex age status. > > > > OMSEND. > > > > DATAS ACT Vallab. > > REN VAR (Var1 Var2=Variable Value). > > > > DATAS ACT Freq. > > SEL IF Var2 NE "Total". > > EXE. > > > > *@1 Attempt 1:. > > > > COMP Value=NUM(Var2,N4). > > EXE. > > > > *@1 "Invalid numeric field" due to comma separator??. > > > > *@2 Attempt 2:. > > > > STR Var2B(A5). > > COMP Var2B=REPLACE(Var2,",","."). > > EXE. > > COMP Value=NUM(Var2B,N4). > > EXE. > > > > *@2 Doesn't work either :-(. > > > > MATC FIL FIL Freq > > /FIL Vallab > > /BY Variable Value. > > > > DATAS NAM Combi. > > DATAS CLO Freq. > > DATAS CLO Vallab. > > > > > > ________________________________ > > What can you do with the new Windows Live? Find out > > ===================== > 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 Express yourself instantly with MSN Messenger! MSN Messenger |
| Free forum by Nabble | Edit this page |