GET DATA /TYPE = ODBC macro with curious error msg

[Marks, Jim]

I use macros to define dates for a query of this type:

GET DATA /TYPE = ODBC /connect =!connstr /
  SQL =
  'SELECT * FROM table1 WHERE '
  'month =
  !mth
  'and '
  'year =
  !yr.

The macros for !mth and !yr are defned as
DEFINE !yr ( )
!QUOTE ( 2007 )
!ENDDEFINE.

DEFINE !mth ( )
!QUOTE ( 1 )
!ENDDEFINE.

This works, but it generates an error msg:

>Warning # 208 in column 2.  Text: MONTH
>A text string is not correctly enclosed in quotation marks on the
command
>line.  Literals may not be continued across command lines without the
use
>of the continuation symbol '+'.

Any ideas?

--jim

[Frederic Villamayor Forcada]

Jim,

The error message is not curious. It describes what it is happening: When
the macro is expanded, an incorrectly quoted string is found.

When designing macros it is useful to use

SET MPRINT ON.

before the macro definition. This allows to see the expanded macro
commands in the output, and helps to undestand what is happening when an
error occurs.

Now, try this syntax for you macro

GET DATA /TYPE = ODBC /connect =!connstr /
  SQL =
  'SELECT * FROM table1 WHERE '
  !QUOTE(!CONCAT('month = ', !mth, ' '))
  'and '
  !QUOTE(!CONCAT('year = ', !yr))
.

Once the macro works properly, you can use

SET MPRINT OFF.

to avoid unnecessary output.

Greetings


Frederic

Frederic Villamayor, BS
Researcher
Biostatistics Unit
Juan de Sada, 32
08028 Barcelona
Tel    [phone]
Fax   [phone]
[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.




"Marks, Jim" <[hidden email]>
Enviado por: "SPSSX(r) Discussion" <[hidden email]>
12/04/2007 14:40
Por favor, responda a
"Marks, Jim" <[hidden email]>


Para
[hidden email]
cc

Asunto
[SPSSX-L] GET DATA /TYPE = ODBC macro with curious error msg






I use macros to define dates for a query of this type:

GET DATA /TYPE = ODBC /connect =!connstr /
  SQL =
  'SELECT * FROM table1 WHERE '
  'month =
  !mth
  'and '
  'year =
  !yr.

The macros for !mth and !yr are defned as
DEFINE !yr ( )
!QUOTE ( 2007 )
!ENDDEFINE.

DEFINE !mth ( )
!QUOTE ( 1 )
!ENDDEFINE.

This works, but it generates an error msg:

>Warning # 208 in column 2.  Text: MONTH
>A text string is not correctly enclosed in quotation marks on the
command
>line.  Literals may not be continued across command lines without the
use
>of the continuation symbol '+'.

Any ideas?

--jim

[Frederic Villamayor Forcada]

Jim,

I've just noticed that this was not an ERROR message, but a WARNING
message, that does not stop the macro run. I think that SPSS tries to
correct the origin of the warning (in fact it has identified it: as an
incorrectly quoted string).

I insist you should try the SET MPRINT ON command before executing the
macro. Then you will see which is the actual command that is running.

Greetings


Frederic




Frederic Villamayor, BS
Researcher
Biostatistics Unit
Juan de Sada, 32
08028 Barcelona
Tel    [phone]
Fax   [phone]
[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.




"Marks, Jim" <[hidden email]>
12/04/2007 18:58

Para
"Frederic Villamayor Forcada" <[hidden email]>
cc

Asunto
RE: [SPSSX-L] GET DATA /TYPE = ODBC macro with curious error msg






The curious thing is that the macro works as typed-- it gets the expected
data AND produces the error message.
 
--jim
 

From: Frederic Villamayor Forcada
[mailto:[hidden email]]
Sent: Thursday, April 12, 2007 7:57 AM
To: Marks, Jim
Cc: [hidden email]
Subject: Re: [SPSSX-L] GET DATA /TYPE = ODBC macro with curious error msg


Jim,

The error message is not curious. It describes what it is happening: When
the macro is expanded, an incorrectly quoted string is found.

When designing macros it is useful to use

SET MPRINT ON.

before the macro definition. This allows to see the expanded macro
commands in the output, and helps to undestand what is happening when an
error occurs.

Now, try this syntax for you macro

GET DATA /TYPE = ODBC /connect =!connstr /
 SQL =
 'SELECT * FROM table1 WHERE '
 !QUOTE(!CONCAT('month = ', !mth, ' '))
 'and '
 !QUOTE(!CONCAT('year = ', !yr))
.

Once the macro works properly, you can use

SET MPRINT OFF.

to avoid unnecessary output.

Greetings


Frederic

Frederic Villamayor, BS
Researcher
Biostatistics Unit
Juan de Sada, 32
08028 Barcelona
Tel    [phone]
Fax   [phone]
[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.




"Marks, Jim" <[hidden email]>
Enviado por: "SPSSX(r) Discussion" <[hidden email]>
12/04/2007 14:40

Por favor, responda a
"Marks, Jim" <[hidden email]>



Para
[hidden email]
cc

Asunto
[SPSSX-L] GET DATA /TYPE = ODBC macro with curious error msg








I use macros to define dates for a query of this type:

GET DATA /TYPE = ODBC /connect =!connstr /
 SQL =
 'SELECT * FROM table1 WHERE '
 'month =
 !mth
 'and '
 'year =
 !yr.

The macros for !mth and !yr are defned as
DEFINE !yr ( )
!QUOTE ( 2007 )
!ENDDEFINE.

DEFINE !mth ( )
!QUOTE ( 1 )
!ENDDEFINE.

This works, but it generates an error msg:

>Warning # 208 in column 2.  Text: MONTH
>A text string is not correctly enclosed in quotation marks on the
command
>line.  Literals may not be continued across command lines without the
use
>of the continuation symbol '+'.

Any ideas?

--jim