Missing keyword in PROC SQL

[Belle]

Hi,

I got an error saying 'Missing Keyword' in proc sql. if I use libname statement to connect Oracle, then I won't get any errors;however when the connect Oracle statement inside proc sql, then error appears.

Here is the program.

%macro setup;

data _null_;

TODAY   = TODAY();

format today date9.;

adm_cur_frm = put(intnx('year',today,0,'b'), date9.);

adm_cur_to  = put(intnx('year',today,0,'end'),date9.);

CALL SYMPUTX('adm_cur_frm',adm_cur_frm);

CALL SYMPUTX('adm_cur_to',adm_cur_to);

RUN;

%mend;

%setup;

/****** The following program runs fine *********/

libname lib1  oracle schema='&schema'  defer=yes path=&path user=&user pw="&pw";

%macro get(period=);

proc sql;

    create table test as

    select *

            from lib1.chrg_itm

            where adm_dt between  "&adm_cur_frm"d and "&adm_cur_to"d

        ;

    quit;

%mend;

%get(period=cur);

/***** Using below program I got Missing Keyword error *****/

/***** if I remove where statement, program runs fine  *****/

PROC SQL;

  CONNECT TO ORACLE

  (USER=&user PASSWORD="&PW" PATH=&path);

CREATE TABLE TEST  AS

  SELECT * FROM CONNECTION TO ORACLE

    (select *

     from lib1.chrg_itm

     where adm_dt between  "&adm_cur_frm"d and "&adm_cur_to"d

    );

DISCONNECT FROM ORACLE;

   quit;

%mend;

%get(period=cur);

I noticed this happened even when I use PC SAS / SAS EG.

I tried the following as Patric suggested, I didn't get error.

data test;

  format dttm date9.;

  dttm="&adm_cur_frm"d;

output;

stop;

run;

For my curiosity, I'm sure both connect Oracle statements are correct, but why one works while the other doesn't work. Any thoughts about this?

Thank you!

[Patrick]

This is the difference between SAS syntax and Oracle syntax, between implicit and explicit (pass-through) SQL.

In the first case you define your statements in SAS syntax and the SAS/Access to Oracle engine then translates this SAS syntax into Oracle syntax, in the latter you send the SQL directly to Oracle in Oracle syntax.

where adm_dt between  "&adm_cur_frm"d and "&adm_cur_to"d   is SAS syntax. You can't use this within the pass-through block as it is not valid Oracle syntax.

Run your code with following options turned on:

options sastrace=",,,d" sastraceloc=saslog nostsuffix;

proc sql;

    create table test as

    select *

            from lib1.chrg_itm

            where adm_dt between  "&adm_cur_frm"d and "&adm_cur_to"d

        ;

    quit;

Then examine the SAS Log as there it will show you to what Oracle SQL syntax the SAS/Access to Oracle engine translated this SAS syntax.

Then use this generated Oracle syntax in your pass-through block.

PROC SQL;

  CONNECT TO ORACLE

  (USER=&user PASSWORD="&PW" PATH=&path);

CREATE TABLE TEST  AS

  SELECT * FROM CONNECTION TO ORACLE

    (<copy here the SQL you got in log from the previous implicit SQL>

    );

  DISCONNECT FROM ORACLE;

quit;

This should work - and it also gives you the valid Oracle SQL syntax so now you know what values you need to generate for you macro variables "&adm_cur_frm" and "&adm_cur_to" if used directly within a pass-through block for Oracle.

[Belle]

Hi Patric,

I followed your instruction, used the where statement from implicit sas log by enter date directly, it worked.

WHERE  ("adm_dt" BETWEEN TO_DATE('01DEC2014','DDMONYYYY')

AND                      TO_DATE('31DEC2014','DDMONYYYY') )

However, when I passed macro variables, I got error saying 'invalid identifier'.

WHERE  ("adm_dt" BETWEEN TO_DATE("&adm_cur_frm",'DDMONYYYY')

AND                      TO_DATE("&adm_cur_to",'DDMONYYYY') )

I tried several ways, such as using single quote instead of double quote, also tried "&adm_cur_frm"d, didn't work either.

How can I pass macro variables?

Thanks again for your help.

[Patrick]

WHERE  ("adm_dt" BETWEEN TO_DATE("&adm_cur_frm",'DDMONYYYY')

- I believe you can't use double quotes in Oracle.

- Why is adm_dt in quotes? This is a variable.

As a next step in testing you could populate your macro variable values with the quotes and then give it a go.

%let adm_cur_frm='01DEC2014';

...

WHERE  (adm_dt BETWEEN TO_DATE(&adm_cur_frm,'DDMONYYYY')

....

[Belle]

Hi Patrick,

I finally found a way to use a where statement in pass through. Here is what I got:

adm_cur_frm = put(intnx('year',today,0,'b'), mmddyy10.);

adm_cur_to  = put(intnx('year',today,0,'end'),mmddyy10.);

%LET nvt_cur_date_frm = %BQUOTE('adm_cur_frm');

%LET nvt_cur_date_to  = %BQUOTE('adm_cur_to');

where adm_dt between  to_date(&adm_cur_frm, 'mm/dd/yyyy')

and                   to_date(&adm_cur_to,  'mm/dd/yyyy')

Now I have two way to deal with date in proc sql, one for implicit, one for explicit pass-thru.

Thanks again for your help.

[Patrick · Accepted solution]

ok - then the last missing piece is for you to mark answers as helpful & correct. That's how you say "thank you" in the SAS Communities.

[LinusH]

When using explicit SQL pass-thru, you can't use SAS syntax.

By looking at your code, you don't benefit from using explicit pass-thru - use implicit.