|
Hello,
I have this SQL query: GET DATA /TYPE=ODBC /CONNECT='DSN=Database di Microsoft Access;DBQ= C:\TEMP\TEST1.MDB;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;' /SQL = "SELECT * FROM TABLE1" /ASSUMEDSTRWIDTH=255 . CACHE. EXE. And I would like to have a macro parameter instead of the TEST1.MDB. I have tried: DEFINE !SQL (DB=!TOKENS(1)) !CONCAT ( "GET DATA /TYPE=ODBC /CONNECT='DSN=Database di Microsoft Access;DBQ= C:\TEMP\", !SQL, ";DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;'" ) /SQL = "SELECT * FROM TABLE1" /ASSUMEDSTRWIDTH=255 . CACHE. EXE. !ENDDEFINE. !SQL DB=TEST1.MDB. !SQL DB=TEST2.MDB. But it gives me errors that do not depend on the database, any suggestions on how I could handle this? Thank you, Luca Mr. Luca MEYER Market research, data analysis & more HYPERLINK "http://www.lucameyer.com/"www.lucameyer.com - Tel: +39.339.495.00.21 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.476 / Virus Database: 269.10.2/893 - Release Date: 09/07/2007 17.22 |
|
Colleagues
I am working with a simple ratio (A divided by B). In the past when I have had a divide by zero error in creating a ratio I have converted them into missing values. In a current analysis there will be too many (almost 50%) for the non-missing values to be meaningful. Does anyone know of any commonly acceptable workarounds or more complicated formulas that might be used? The resulting ratios would be used for comparison with one another rather than as absolutely precise results. Warm regards/gary |
|
At 03:19 AM 7/11/2007, Gary Oliver wrote:
>I am working with a simple ratio (A divided by B). In the past when I >have had a divide by zero error in creating a ratio I have converted >them into missing values. In a current analysis there will be too many >(almost 50%) for the non-missing values to be meaningful. > >Does anyone know of any commonly acceptable workarounds or more >complicated formulas that might be used? The resulting ratios would be >used for comparison with one another rather than as absolutely precise >results. There's no answering the question without a sense what you think the ratios with zero divisors *mean*. That's a question about your study, not an SPSS or statistical question. First, you can't do anything unless you can argue convincingly that the 0 denominators represent a non-zero value about which you don't have information, not a value that really is 0, or not there at all. Second, and this can be an exception to the above rule, if the numerator is also 0 when the denominator is, it is fairly common, and often correct, to take the 0/0 values as 0. That can be done with simple tests in SPSS. *However*, include the numerator in the test, so you won't get a completely wrong value if it *isn't* 0/0. DON'T DO THIS WITHOUT CONSIDERING WHETHER THEY REAL *ARE* 0 VALUES. And make the argument in your paper, prepare to defend it to your statistical reviewer. (You'll see that advice again, below.) Third, your project may be dubious altogether. This isn't about 0/0. 50% missing in a crucial variable (and the denominator *is* crucial) virtually guarantees your sample isn't representative of the population. Check with whatever other data you have, and see what you can learn about the pattern of missing values. Consider replacing the ratio altogether with its missing/non-missing dichotomy. Missing-value substitution is considered very unreliable with 50% missing data. If you're going to ignore that and do it anyway, do the missing-value substitution on the denominator, not the ratio. And prepare for a statistical reviewer taking a very dim view. Fourth, consider whether a 0 denominator and non-zero denominator are consistent with what you know about your data source. If not, the non-zero denominators are data errors (which, at 50%, renders your data useless for analysis), or missing values (which may be addressable - see below). Fifth, if the denominator has a very low coefficient of variation (SD/Mean), it may be reasonable to substitute the mean for the missing values. However, you'll have to make a strong argument that the denominator in half with missing data have a low CV as well, and an underlying mean value that's quite close. And this whole argument is not likely to apply to you, since if there is that low CV the proper measure would probably be the numerator in the first place. And see prior comments about your statistical reviewer. Regarding the statistical review: Make the strongest argument you possibly can, in your original paper. Make sure you believe it yourself. If you put an apparently bad analysis in your manuscript, you can depend on the reviewer putting little stock in any argument you make after the fact. And very good luck to you. I'm afraid you simply have a tough problem, and there's no technique that can reliable get around it. Richard Ristow |
|
In reply to this post by Luca Meyer
Try to using:
SET MPRINT=ON. So that you can see what the macro expands to in your output. It will indicate what spss is actually running which might be different from what you thought spss was running. Also, you have "!SQL" in the body of the macro. I think you want "!DB". -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Luca Meyer Sent: Wednesday, July 11, 2007 3:02 AM To: [hidden email] Subject: macro into SQL statement Hello, I have this SQL query: GET DATA /TYPE=ODBC /CONNECT='DSN=Database di Microsoft Access;DBQ= C:\TEMP\TEST1.MDB;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;' /SQL = "SELECT * FROM TABLE1" /ASSUMEDSTRWIDTH=255 . CACHE. EXE. And I would like to have a macro parameter instead of the TEST1.MDB. I have tried: DEFINE !SQL (DB=!TOKENS(1)) !CONCAT ( "GET DATA /TYPE=ODBC /CONNECT='DSN=Database di Microsoft Access;DBQ= C:\TEMP\", !SQL, ";DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;'" ) /SQL = "SELECT * FROM TABLE1" /ASSUMEDSTRWIDTH=255 . CACHE. EXE. !ENDDEFINE. !SQL DB=TEST1.MDB. !SQL DB=TEST2.MDB. But it gives me errors that do not depend on the database, any suggestions on how I could handle this? Thank you, Luca Mr. Luca MEYER Market research, data analysis & more HYPERLINK "http://www.lucameyer.com/"www.lucameyer.com - Tel: +39.339.495.00.21 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.476 / Virus Database: 269.10.2/893 - Release Date: 09/07/2007 17.22 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.476 / Virus Database: 269.10.2/894 - Release Date: 7/10/2007 5:44 PM No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.476 / Virus Database: 269.10.2/894 - Release Date: 7/10/2007 5:44 PM |
|
In reply to this post by Richard Ristow
Try transforming a/b into (a+1)/(b+1)
(or at least substitutingat when b=0) I think thats what Andre Gelman suggests. Gary --- Gary S. Rosin Professor of Law South Texas College of Law 1303 San Jacinto Houston, TX 77002 <[hidden email]> 713-646-1854 ----- Original Message ----- From: Richard Ristow <[hidden email]> Date: Wednesday, July 11, 2007 11:56 am Subject: Re: Creating a ratio that avoids the divide by zero error To: [hidden email] > At 03:19 AM 7/11/2007, Gary Oliver wrote: > > >I am working with a simple ratio (A divided by B). In the past > when I > >have had a divide by zero error in creating a ratio I have converted > >them into missing values. In a current analysis there will be too > many>(almost 50%) for the non-missing values to be meaningful. > > > >Does anyone know of any commonly acceptable workarounds or more > >complicated formulas that might be used? The resulting ratios > would be > >used for comparison with one another rather than as absolutely > precise>results. > > There's no answering the question without a sense what you think the > ratios with zero divisors *mean*. That's a question about your study, > not an SPSS or statistical question. > > First, you can't do anything unless you can argue convincingly > that the > 0 denominators represent a non-zero value about which you don't have > information, not a value that really is 0, or not there at all. > > Second, and this can be an exception to the above rule, if the > numerator is also 0 when the denominator is, it is fairly common, and > often correct, to take the 0/0 values as 0. That can be done with > simple tests in SPSS. *However*, include the numerator in the > test, so > you won't get a completely wrong value if it *isn't* 0/0. DON'T DO > THISWITHOUT CONSIDERING WHETHER THEY REAL *ARE* 0 VALUES. And make the > argument in your paper, prepare to defend it to your statistical > reviewer. (You'll see that advice again, below.) > > Third, your project may be dubious altogether. This isn't about 0/0. > 50% missing in a crucial variable (and the denominator *is* crucial) > virtually guarantees your sample isn't representative of the > population. Check with whatever other data you have, and see what you > can learn about the pattern of missing values. Consider replacing the > ratio altogether with its missing/non-missing dichotomy. Missing-value > substitution is considered very unreliable with 50% missing data. If > you're going to ignore that and do it anyway, do the missing-value > substitution on the denominator, not the ratio. And prepare for a > statistical reviewer taking a very dim view. > > Fourth, consider whether a 0 denominator and non-zero denominator are > consistent with what you know about your data source. If not, the > non-zero denominators are data errors (which, at 50%, renders your > datauseless for analysis), or missing values (which may be > addressable - > see below). > > Fifth, if the denominator has a very low coefficient of variation > (SD/Mean), it may be reasonable to substitute the mean for the missing > values. However, you'll have to make a strong argument that the > denominator in half with missing data have a low CV as well, and an > underlying mean value that's quite close. And this whole argument is > not likely to apply to you, since if there is that low CV the proper > measure would probably be the numerator in the first place. And see > prior comments about your statistical reviewer. > > Regarding the statistical review: Make the strongest argument you > possibly can, in your original paper. Make sure you believe it > yourself. If you put an apparently bad analysis in your > manuscript, you > can depend on the reviewer putting little stock in any argument you > make after the fact. > > And very good luck to you. I'm afraid you simply have a tough problem, > and there's no technique that can reliable get around it. > > Richard Ristow > |
|
Thanks to Richard, Melvin and Gary for their prompt and thoughtful
comments. It was a transformation that I was after and I am now exploring the suggestions by Melvin and Gary. Very much appreciated. Warm regards/gary >>-----Original Message----- >>From: SPSSX(r) Discussion [mailto:[hidden email]] >>On Behalf Of Gary Rosin >>Sent: Thursday, July 12, 2007 7:14 AM >>To: [hidden email] >>Subject: Re: Creating a ratio that avoids the divide by zero error >> >>Try transforming a/b into (a+1)/(b+1) >>(or at least substitutingat when b=0) >>I think thats what Andre Gelman suggests. >> >>Gary >> >> --- >> >>Gary S. Rosin >>Professor of Law >>South Texas College of Law >>1303 San Jacinto >>Houston, TX 77002 >> >><[hidden email]> >>713-646-1854 >> >>----- Original Message ----- >>From: Richard Ristow <[hidden email]> >>Date: Wednesday, July 11, 2007 11:56 am >>Subject: Re: Creating a ratio that avoids the divide by zero error >>To: [hidden email] >> >>> At 03:19 AM 7/11/2007, Gary Oliver wrote: >>> >>> >I am working with a simple ratio (A divided by B). In the past >>> when I >>> >have had a divide by zero error in creating a ratio I have >>converted >>> >them into missing values. In a current analysis there will be too >>> many>(almost 50%) for the non-missing values to be meaningful. >>> > >>> >Does anyone know of any commonly acceptable workarounds or more >>> >complicated formulas that might be used? The resulting ratios >>> would be >>> >used for comparison with one another rather than as absolutely >>> precise>results. >>> >>> There's no answering the question without a sense what you >>think the >>> ratios with zero divisors *mean*. That's a question about >>your study, >>> not an SPSS or statistical question. >>> >>> First, you can't do anything unless you can argue convincingly that >>> the 0 denominators represent a non-zero value about which you don't >>> have information, not a value that really is 0, or not there at all. >>> >>> Second, and this can be an exception to the above rule, if the >>> numerator is also 0 when the denominator is, it is fairly >>common, and >>> often correct, to take the 0/0 values as 0. That can be done with >>> simple tests in SPSS. *However*, include the numerator in >>the test, so >>> you won't get a completely wrong value if it *isn't* 0/0. DON'T DO >>> THISWITHOUT CONSIDERING WHETHER THEY REAL *ARE* 0 VALUES. >>And make the >>> argument in your paper, prepare to defend it to your statistical >>> reviewer. (You'll see that advice again, below.) >>> >>> Third, your project may be dubious altogether. This isn't about 0/0. >>> 50% missing in a crucial variable (and the denominator *is* >>crucial) >>> virtually guarantees your sample isn't representative of the >>> population. Check with whatever other data you have, and >>see what you >>> can learn about the pattern of missing values. Consider >>replacing the >>> ratio altogether with its missing/non-missing dichotomy. >>Missing-value >>> substitution is considered very unreliable with 50% missing >>data. If >>> you're going to ignore that and do it anyway, do the missing-value >>> substitution on the denominator, not the ratio. And prepare for a >>> statistical reviewer taking a very dim view. >>> >>> Fourth, consider whether a 0 denominator and non-zero >>denominator are >>> consistent with what you know about your data source. If not, the >>> non-zero denominators are data errors (which, at 50%, renders your >>> datauseless for analysis), or missing values (which may be >>addressable >>> - see below). >>> >>> Fifth, if the denominator has a very low coefficient of variation >>> (SD/Mean), it may be reasonable to substitute the mean for >>the missing >>> values. However, you'll have to make a strong argument that the >>> denominator in half with missing data have a low CV as well, and an >>> underlying mean value that's quite close. And this whole >>argument is >>> not likely to apply to you, since if there is that low CV >>the proper >>> measure would probably be the numerator in the first place. And see >>> prior comments about your statistical reviewer. >>> >>> Regarding the statistical review: Make the strongest argument you >>> possibly can, in your original paper. Make sure you believe it >>> yourself. If you put an apparently bad analysis in your manuscript, >>> you can depend on the reviewer putting little stock in any argument >>> you make after the fact. >>> >>> And very good luck to you. I'm afraid you simply have a >>tough problem, >>> and there's no technique that can reliable get around it. >>> >>> Richard Ristow >>> >> |
|
In reply to this post by Katkowski, David
hi David,
Yep, using set mprint = on is a must. I think the syntax below should work. You do need to give the extension .mbd in the macro call. Alternatively, you could use DBQ= !QUOTE(!CONCAT("C:\TEMP\",DB!,".mbd")); Cheers!! Albert-Jan GET DATA /TYPE=ODBC /CONNECT='DSN=Database di Microsoft Access; DBQ= !QUOTE(!CONCAT("C:\TEMP\",DB!)); DriverId=281;FIL=MSAccess; MaxBufferSize=2048;PageTimeout=5;'" --- "Katkowski, David" <[hidden email]> wrote: > Try to using: > > SET MPRINT=ON. > > So that you can see what the macro expands to in > your output. It will indicate what spss is actually > running which might be different from what you > thought spss was running. Also, you have "!SQL" in > the body of the macro. I think you want "!DB". > > -----Original Message----- > From: SPSSX(r) Discussion > [mailto:[hidden email]] On Behalf Of Luca > Meyer > Sent: Wednesday, July 11, 2007 3:02 AM > To: [hidden email] > Subject: macro into SQL statement > > Hello, > > I have this SQL query: > > GET DATA /TYPE=ODBC /CONNECT='DSN=Database di > Microsoft Access;DBQ= > C:\TEMP\TEST1.MDB;DriverId=281;FIL=MS > Access;MaxBufferSize=2048;PageTimeout=5;' > /SQL = "SELECT * FROM TABLE1" > /ASSUMEDSTRWIDTH=255 . > CACHE. > EXE. > > And I would like to have a macro parameter instead > of the TEST1.MDB. > > I have tried: > > DEFINE !SQL (DB=!TOKENS(1)) > !CONCAT ( "GET DATA /TYPE=ODBC > /CONNECT='DSN=Database di Microsoft > Access;DBQ= C:\TEMP\", > !SQL, > ";DriverId=281;FIL=MS > Access;MaxBufferSize=2048;PageTimeout=5;'" > ) > /SQL = "SELECT * FROM TABLE1" > /ASSUMEDSTRWIDTH=255 . > CACHE. > EXE. > !ENDDEFINE. > > !SQL DB=TEST1.MDB. > > !SQL DB=TEST2.MDB. > > But it gives me errors that do not depend on the > database, any suggestions > on how I could handle this? > > Thank you, > > Luca > > Mr. Luca MEYER > Market research, data analysis & more > HYPERLINK > "http://www.lucameyer.com/"www.lucameyer.com - Tel: > +39.339.495.00.21 > > > > > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.5.476 / Virus Database: 269.10.2/893 - > Release Date: 09/07/2007 > 17.22 > > > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.5.476 / Virus Database: 269.10.2/894 - > Release Date: 7/10/2007 5:44 PM > > > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.5.476 / Virus Database: 269.10.2/894 - > Release Date: 7/10/2007 5:44 PM > Cheers! Albert-Jan ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Did you know that 87.166253% of all statistics claim a precision of results that is not justified by the method employed? [HELMUT RICHTER] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ____________________________________________________________________________________ Choose the right car based on your needs. Check out Yahoo! Autos new Car Finder tool. http://autos.yahoo.com/carfinder/ |
|
In reply to this post by Gary Oliver
Dear Gary:
I used to work with proportions like that when examining sex ratio among the offspring of rats. Sometimes all the pups in a litter are males, or they are all females. If I divide the number of males by the number of females, I get missing values. Thus, I defined sex ratio as number of males divided by the total number of pups in a litter. In that way, I never get missing values for the ratio. Similarly, you can study A/(A+B), instead of A/B. HTH Frederic Frederic Villamayor, BS Researcher/Study Manager Biostatistics Unit CIDF Ferrer Grupo Juan de Sada, 32 08028 Barcelona Tel +34 935093236 Fax +34 934112764 [hidden email] http://www.ferrergrupo.com Este mensaje, y en su caso, cualquier fichero anexo al mismo, puede contener información confidencial, siendo para uso exclusivo del destinatario, quedando prohibida su divulgación, copia o distribución a terceros sin la autorización expresa del remitente. Si Vd. ha recibido este mensaje erróneamente, se ruega lo notifique al remitente y proceda a su borrado. Gracias por su colaboración. This message and its annexed files may contain confidential information which is exclusively for the use of the addressee. It is strictly forbidden to distribute copies to third parties without the explicit permission of the sender. If you receive this message by mistake, please notify it to the sender and make sure to delete it. Thank you for your kind cooperation. Gary Oliver <[hidden email]> Enviado por: "SPSSX(r) Discussion" <[hidden email]> 11/07/2007 09:19 Por favor, responda a Gary Oliver <[hidden email]> Para [hidden email] cc Asunto [SPSSX-L] Creating a ratio that avoids the divide by zero error Colleagues I am working with a simple ratio (A divided by B). In the past when I have had a divide by zero error in creating a ratio I have converted them into missing values. In a current analysis there will be too many (almost 50%) for the non-missing values to be meaningful. Does anyone know of any commonly acceptable workarounds or more complicated formulas that might be used? The resulting ratios would be used for comparison with one another rather than as absolutely precise results. Warm regards/gary |
|
In reply to this post by Albert-Jan Roskam
Hello,
Please allow me to thank all those that have replied to my July 12th query on- and off-list(Albert-Jan, Mark, David, etc...) and apologize if I do that just now but I have had problems with my main pc during the last couple of weeks. The solution to the matter was apparently related to the wrong parameter call I was doing in the query (!SQL instead o !DB) Thanks again, Luca -----Messaggio originale----- Da: SPSSX(r) Discussion [mailto:[hidden email]] Per conto di Albert-jan Roskam Inviato: giovedì 12 luglio 2007 9.56 A: [hidden email] Oggetto: Re: macro into SQL statement hi David, Yep, using set mprint = on is a must. I think the syntax below should work. You do need to give the extension .mbd in the macro call. Alternatively, you could use DBQ= !QUOTE(!CONCAT("C:\TEMP\",DB!,".mbd")); Cheers!! Albert-Jan GET DATA /TYPE=ODBC /CONNECT='DSN=Database di Microsoft Access; DBQ= !QUOTE(!CONCAT("C:\TEMP\",DB!)); DriverId=281;FIL=MSAccess; MaxBufferSize=2048;PageTimeout=5;'" --- "Katkowski, David" <[hidden email]> wrote: > Try to using: > > SET MPRINT=ON. > > So that you can see what the macro expands to in your output. It will > indicate what spss is actually running which might be different from > what you thought spss was running. Also, you have "!SQL" in the body > of the macro. I think you want "!DB". > > -----Original Message----- > From: SPSSX(r) Discussion > [mailto:[hidden email]] On Behalf Of Luca Meyer > Sent: Wednesday, July 11, 2007 3:02 AM > To: [hidden email] > Subject: macro into SQL statement > > Hello, > > I have this SQL query: > > GET DATA /TYPE=ODBC /CONNECT='DSN=Database di Microsoft Access;DBQ= > C:\TEMP\TEST1.MDB;DriverId=281;FIL=MS > Access;MaxBufferSize=2048;PageTimeout=5;' > /SQL = "SELECT * FROM TABLE1" > /ASSUMEDSTRWIDTH=255 . > CACHE. > EXE. > > And I would like to have a macro parameter instead of the TEST1.MDB. > > I have tried: > > DEFINE !SQL (DB=!TOKENS(1)) > !CONCAT ( "GET DATA /TYPE=ODBC > /CONNECT='DSN=Database di Microsoft > Access;DBQ= C:\TEMP\", > !SQL, > ";DriverId=281;FIL=MS > Access;MaxBufferSize=2048;PageTimeout=5;'" > ) > /SQL = "SELECT * FROM TABLE1" > /ASSUMEDSTRWIDTH=255 . > CACHE. > EXE. > !ENDDEFINE. > > !SQL DB=TEST1.MDB. > > !SQL DB=TEST2.MDB. > > But it gives me errors that do not depend on the database, any > suggestions on how I could handle this? > > Thank you, > > Luca > > Mr. Luca MEYER > Market research, data analysis & more > HYPERLINK > "http://www.lucameyer.com/"www.lucameyer.com - Tel: > +39.339.495.00.21 > > > > > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.5.476 / Virus Database: 269.10.2/893 - Release Date: > 09/07/2007 > 17.22 > > > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.5.476 / Virus Database: 269.10.2/894 - Release Date: > 7/10/2007 5:44 PM > > > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.5.476 / Virus Database: 269.10.2/894 - Release Date: > 7/10/2007 5:44 PM > Cheers! Albert-Jan ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Did you know that 87.166253% of all statistics claim a precision of results that is not justified by the method employed? [HELMUT RICHTER] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ____________________________________________________________________________ ________ Choose the right car based on your needs. Check out Yahoo! Autos new Car Finder tool. http://autos.yahoo.com/carfinder/ No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.476 / Virus Database: 269.11.6/938 - Release Date: 05/08/2007 16.16 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.476 / Virus Database: 269.11.6/938 - Release Date: 05/08/2007 16.16 |
| Free forum by Nabble | Edit this page |
