by Thanks to Prassad and Sigund. Much to my surprise Prassad's suggestion that
multiple equal signs are not allowed seems to be the key. When I replace:
WHERE bene_migration.state_code=WHERE bene_migration.state_code=
182 coverage.state_code='GA'
182 coverage.state_code='GA'
With
WHERE bene_migration.state_code='GA'
182 AND coverage.state_code='GA'
Then everything seems to work fine. That error message never would have led
me to suspect this was the cause of the problem.
Thanks again,
Rodney
From: Prasad S Ravi [mailto:prasad.s.r...@household.com]
Sent: Tuesday, July 08, 2003 1:43 PM
To: rpresley
Cc: sa...@listserv.uga.edu
Subject: RE: PROC SQL pass through to Oracle
Rodney:
You are right if it is the last variable you don't need one, oh! man
so many comments it is confusing,
hey I noticed another thing in your code, I don't think this is allowed.
WHERE bene_migration.state_code=
coverage.state_code='GA'
^^^^^^^^^^
Prasad Ravi
rpresley
<rpres...@gmcf.org> To: 'Prasad S Ravi'
<prasad.s.r...@HOUSEHOLD.COM>
cc:
07/08/2003 10:35 AM Subject: RE: PROC SQL pass
through to Oracle
Prasad,
Thanks for responding.
I have commented out the comma because TERMINDT is the last variable that is
being used in the last submission of PROC SQL. Is a comma needed after the
last variable in a select statement?
Rodney
-----Original Message-----
From: Prasad S Ravi [mailto:prasad.s.r...@HOUSEHOLD.COM]
Sent: Tuesday, July 08, 2003 1:10 PM
To: SA...@LISTSERV.UGA.EDU
Subject: Re: PROC SQL pass through to Oracle
Rodney:
One problem with the last run is the ',' which you have commented out.
Try putting ',' after termindt and run it again.
Prasad Ravi.
proc sql;
170 connect to oracle(user='rpresley'
171 orapw=XXXXXXXXXX path='@tns:c1s80_SDPS');
172 create table dispptb as
173 select * from connection to oracle
174 (SELECT coverage.cvrage_type as covtype,
175 coverage.cvrage_start_dt as startdt,
176 coverage.cvrage_termination_dt as termindt/*,
^^^^^^^^
177 bene_migration.bene_unique_id as uniqid,
178 bene_migration.state_code as state,
179 bene_migration.bene_county_cd as county */
Prasad Ravi
rpresley
<rpres...@GMCF.ORG> To:
SA...@LISTSERV.UGA.EDU
Sent by: "SAS(r) cc:
Discussion" Subject: PROC SQL pass
through to Oracle
<SA...@LISTSERV.UGA.E
DU>
07/08/2003 10:02 AM
Please respond to
rpresley
SAS-L,
SAS version 8.2, Oracle version 8.x. Desktop: Windoze 2000. I do not know
what OS is on the remote Oracle server but probably Unix.
A section of code follows. In the first submission I have commented out all
references to one table and the code runs correctly. In the second section
I have commented out the references to the second table. This code also runs
correctly. In the third section I have removed the comments so the two
tables can be joined. The error message, which I have not seen before,
indicates that the SQL command was not properly ended. What does this mean
and how can I repair it.
Thanks for you help,
Rodney
121 proc sql;
122 connect to oracle(user='rpresley'
123 orapw=XXXXXXXXXX path='@tns:c1s80_SDPS');
124 create table dispptb as
125 select * from connection to oracle
126 (SELECT coverage.cvrage_type as covtype,
127 coverage.cvrage_start_dt as startdt,
128 coverage.cvrage_termination_dt as termindt/*,
129 bene_migration.bene_unique_id as uniqid,
130 bene_migration.state_code as state,
131 bene_migration.bene_county_cd as county */
132 FROM /*bene_migration,*/ coverage
133 WHERE /*bene_migration.state_code= */
134 coverage.state_code='GA'
135 /* and coverage.bene_unique_id = bene_migration.bene_unique_id
136 and bene_migration.resid_chg_dt <= '03/31/2002' */
137 /* and (bene_migration.resid_end_dt IS NULL or
bene_migration.resid_end_dt >= '03/31/2002')
137! */
138 /* and (beneficiary.bene_death_dt >'03/31/2002' or
beneficiary.bene_death_dt is null) */
139 /* and beneficiary.bene_race_cd = '2' */
140 and coverage.cvrage_start_dt = '04/01/2001'
141 and (coverage.cvrage_termination_dt is null or
coverage.cvrage_termination_dt
141! >='03/31/2002')
142 and coverage.cvrage_type IN ('PTB') )
143 ;
NOTE: Table WORK.DISPPTB created, with 5080 rows and 3 columns.
144 quit;
NOTE: PROCEDURE SQL used:
real time 52.29 seconds
cpu time 0.10 seconds
*******************************************************
145 proc sql;
146 connect to oracle(user='rpresley'
147 orapw=XXXXXXXXXX path='@tns:c1s80_SDPS');
148 create table dispptb as
149 select * from connection to oracle
150 (SELECT /*coverage.cvrage_type as covtype,
151 coverage.cvrage_start_dt as startdt,
152 coverage.cvrage_termination_dt as termindt, */
153 bene_migration.bene_unique_id as uniqid,
154 bene_migration.state_code as state,
155 bene_migration.bene_county_cd as county
156 FROM bene_migration /*, coverage */
157 WHERE bene_migration.state_code=
158 /*coverage.state_code=*/ 'GA'
159 /* and coverage.bene_unique_id = bene_migration.bene_unique_id */
160 and bene_migration.resid_chg_dt <= '03/31/2002'
161 and (bene_migration.resid_end_dt IS NULL or
bene_migration.resid_end_dt >= '03/31/2002')
162 /* and (beneficiary.bene_death_dt >'03/31/2002' or
beneficiary.bene_death_dt is null) */
163 /* and beneficiary.bene_race_cd = '2' */
164 /* and coverage.cvrage_start_dt = '04/01/2001'
165 and (coverage.cvrage_termination_dt is null or
coverage.cvrage_termination_dt
165! >='03/31/2002')
166 and coverage.cvrage_type IN ('PTB') */ )
167 ;
NOTE: Table WORK.DISPPTB created, with 994158 rows and 3 columns.
168 quit;
NOTE: PROCEDURE SQL used:
real time 12:03.34
cpu time 4.70 seconds
***********************************************
169 proc sql;
170 connect to oracle(user='rpresley'
171 orapw=XXXXXXXXXX path='@tns:c1s80_SDPS');
172 create table dispptb as
173 select * from connection to oracle
174 (SELECT coverage.cvrage_type as covtype,
175 coverage.cvrage_start_dt as startdt,
176 coverage.cvrage_termination_dt as termindt/*,
177 bene_migration.bene_unique_id as uniqid,
178 bene_migration.state_code as state,
179 bene_migration.bene_county_cd as county */
180 FROM bene_migration, coverage
181 WHERE bene_migration.state_code=
182 coverage.state_code='GA'
183 and coverage.bene_unique_id = bene_migration.bene_unique_id
184 and bene_migration.resid_chg_dt <= '03/31/2002'
185 and (bene_migration.resid_end_dt IS NULL or
bene_migration.resid_end_dt >= '03/31/2002')
186 /* and (beneficiary.bene_death_dt >'03/31/2002' or
beneficiary.bene_death_dt is null)
187 and beneficiary.bene_race_cd = '2' */
188 and coverage.cvrage_start_dt = '04/01/2001'
189 and (coverage.cvrage_termination_dt is null or
coverage.cvrage_termination_dt
189! >='03/31/2002')
190 and coverage.cvrage_type IN ('PTB') )
191 ;
ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended. SQL
statement: SELECT
coverage.cvrage_type as covtype, coverage.cvrage_start_dt as startdt,
coverage.cvrage_termination_dt as termindt FROM bene_migration,
coverage WHERE
bene_migration.state_code= coverage.state_code='GA' and
coverage.bene_unique_id =
bene_migration.bene_unique_id and bene_migration.resid_chg_dt <=
'03/31/2002' and
(bene_migration.resid_end_dt IS NULL or bene_migration.resid_end_dt
>= '03/31/2002') and
coverage.cvrage_start_dt = '04/01/2001' and
(coverage.cvrage_termination_dt is null or
coverage.cvrage_termination_dt >='03/31/2002') and
coverage.cvrage_type IN ('PTB'). 192 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used:
real time 1.31 seconds
cpu time 0.01 seconds
Rodney J. Presley, Ph.D.
Georgia Medical Care Foundation
1455 Lincoln Parkway
suite 800
Atlanta, GA 30346
678 - 527 - 3474 voice
678-527-3574 fax