Date:         Wed, 18 Dec 2002 11:29:17 -0600
Reply-To:     "Marks, Jim" <jim.marks@lodgenet.com>
Sender:       "SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>
From:         "Marks, Jim" <jim.marks@lodgenet.com>
Subject:      Macro with GET DATA /ODBC
Content-Type: text/plain; charset="iso-8859-1"

Hi List:

I am trying to create a macro to run multiple SQL queries via an ODBC connection into an Oracle database. The syntax I have created to extract the data works, but I can't get the macro to expand properly.

Here is a sample of the Macro:

DEFINE !cycle (varnam=!CMDEND).

SET MPRINT = YES.
!DO !var !IN (!varnam).

GET DATA /TYPE=ODBC /CONNECT=
'DSN=database;UID=JMARKS;PWD=hidden'
 /SQL =
 'SELECT  "T4"."ID" AS "id",  "T4"."ARENA" AS '
  '"arena", "T9"."TIME_STAMP" AS '
 '"dt_time"  FROM  "DB"."TABLE" '
  '"T4" WHERE ("T4"."ID" = ''!var'' AND  {fn '
  'MONTH("T4"."TIME_STAMP")} = 1) '
.
CACHE.
EXECUTE.

SAVE OUTFILE=!QUOTE(!CONCAT("C:\data\",!var,"-1.sav"))
   /COMPRESSED.
!DOEND.
!ENDDEFINE.

!cycle varnam = 010 020.

The goal is to create 1 file per id.

Note: that referencing a string requires two single apostrohpes { '' }, while referencing database elements requires a double apostrophe { " }. Each line of SQL requires a single apostrophe { ' } to begin and end, without the string continuation { + }.

The macro does not expand !var in the GET DATA command, but it does expand the SAVE command. It looks for the string {!var} twice,  instead of looking for the string {010} and then the string {020}. The result is two data files with no rows of data.

Do I need to write the /SELECT subcommand as a string? I so, how?

Any ideas/ help will be gratefully appreciated.

Jim Marks
Senior Market Analyst
LodgeNet Entertainment Corporation
605.988.1616

IMPORTANT NOTICE: This communication (including any attached files) is intended for the entity to which it is directed and may contain confidential and proprietary information of LodgeNet Entertainment Corporation.  The communication is provided under the terms of the nondisclosure agreement between LodgeNet and the intended recipient.  If you are not acting solely on behalf of the intended recipient with respect to the receipt of this communication, you are not authorized to receive, retain, print or forward this communication.  In that event, you are directed to destroy this communication (including any attached files), and to advise ipmanager@lodgenet.com that you received this communication in error.  If you are authorized to receive this communication on behalf of the intended recipient, you are obligated to treat it as confidential and proprietary information of LodgeNet Entertainment Corporation, in accordance with the nondisclosure agreement mentioned above.