Learning SAS? Welcome to the exclusive online community for all SAS learners.

SAS Unbalanced Quotation Warning and Errors

Reply
Occasional Learner
Posts: 1

SAS Unbalanced Quotation Warning and Errors

Hi All,

 

Can anybody pls help me with the error/warning?

 

I am executing this code in SAS Studio quering Oracle database using Proc sql.

 

Each time I execute, sometimes I get errors and some times warning and sometimes nothing and each time never did the progam execute. I am getting the below erros and warnings.

 

49: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
 
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.
 
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.
WARNING: The quoted string currently being processed has become more than 262 bytes long. You might have unbalanced quotation
marks.

 

WARNING: Apparent symbolic reference _WEBVVN not resolved.
88 %SYMDEL _WEBVVN;
WARNING: Attempt to delete macro variable _WEBVVN failed. Variable not found.

 

 

CODE:


proc sql;
connect to oracle (user=****** password=******" path='****' buffsize=100000);
create table transaction1 as
select * from connection to oracle(
select trans.customer_id,
max(case when trans.transaction_date between '31-Jan-16' and '28-Feb-16' then 1 else 0 end) as shop_TY,
max(case when trans.transaction_date between '01-Feb-15' and '07-Mar-15' then 1 else 0 end) as shop_LY,
max(count(distinct case when trans.transaction_date between '31-Jan-16' and '28-Feb-16' then trans.order_id else null end) as Trans_TY,
max(count(distinct case when trans.transaction_date between '01-Feb-15' and '07-Mar-15' then trans.order_id else null end) as Trans_LY,
sum(case when trans.transaction_date) between '31-Jan-16' and '28-Feb-16' then decode(sales_Credit,1,amount,2,-amount) else 0 end) as netsales_TY,
sum(case when trans.transaction_date) between '01-Feb-15' and '07-Mar-15' then decode(sales_Credit,1,amount,2,-amount)else 0 end) as netsales_LY

from mbs.tt trans
where transaction_date between '01-Feb-15' AND '28-FEB-16'
and transaction_type=1
and sales_credit in (1,2)
and business_unit=1
and customer_id>0

group by trans.customer_id
);
quit;

 

 

I am attaching screenshots of the errors and warning to get a better picture.


Errors and Warnings.PNGNo warning NO errors but doesnt execute.PNGWarning Only.PNG
Super Contributor
Posts: 250

Re: SAS Unbalanced Quotation Warning and Errors

Hi Santosh,

 

Welcome to the community!  I hope you find a lot of useful information here.

 

First question - Are you using SAS Studio (as a part of Base SAS) or SAS University Edition?  If you're using SAS University Edition, I don't know there's a way to connect to an external database through the Virtual Machine.  If you're using Base SAS, then you should run PROC SETINIT; QUIT; to see that you have SAS/ACCESS

Second question - are you able to connect to a single table using SAS?

Third question - if you have SAS/ACCESS, and are unable to connect to a single table, have you seen the SAS Support article http://support.sas.com/documentation/cdl/en/bidsag/61236/HTML/default/viewer.htm#a003098426.htm ?  I have used SAS to connect to hundreds of data tables and I *always* go back to this article.

 

Good luck and post back your findings.  There are a ton of very knowledgeable people on this site, I'm sure if I can't help you someone else will!

Chris

Has my article or post helped? Please mark as Solution or Like the article!
Super User
Posts: 7,824

Re: SAS Unbalanced Quotation Warning and Errors

Could be unbalanced quotes. Your example as posted misses a starting double quote at the oracle password.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,973

Re: SAS Unbalanced Quotation Warning and Errors

Well, there seems to be a few questionable things going on here.  Have you written this query in SQL Developer (free SQL tool for Oracle), or Toad and tested it there, as I think, and can't test this, that it would fail.  Just taking the where clause:

where transaction_date between '01-Feb-15' AND '28-FEB-16'

A Date can't be between to strings, maybe you meant to use the to_date() function to convert that string to a date numeric:

 

TO_DATE('01-JAN-2008', 'DD-MON-YYYY')

 

 Secondly the unbalanced quote per @KurtBremser - yes, that is causing its own issues, but may be masking further problems - i.e. they are hidden in the string.  Some other minor things like missing semicolon after quit.  Also, good idea to standardise capitilisations and indentations so it makes your code easier to read.  As some advice (if you don't have Toad or SQLDeveloper), I would start off by minimising your query and then expanding on it, here is an example with some things corrected - I don't have Oracle so can't check it however:

proc sql;
  connect to oracle (user=****** password="******" path='****' buffsize=100000);  /*<-extra double quote on this line */
  create table TRANSACTION1 as
  select * from connection to oracle (
    select  *
    where   TRANSACTION_DATE between to-num('01-Feb-2015','DD-MM-YYYY') AND to_num('28-FEB-2016','DD-MM-YYYY')
      and   TRANSACTION_TYPE=1
      and   SALES_CREDIT in (1,2)
      and   BUSINESS_UNIT=1
      and   CUSTOMER_ID > 0
    group by TRANS.CUSTOMER_ID
  );
quit;  
Ask a Question
Discussion stats
  • 3 replies
  • 550 views
  • 3 likes
  • 4 in conversation