topic Missing keyword in PROC SQL in SAS Procedures

Missing keyword in PROC SQL

Belle — Fri, 27 Mar 2015 12:41:39 GMT

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!

Re: Missing keyword in PROC SQL

Patrick — Fri, 27 Mar 2015 13:03:35 GMT

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.

Re: Missing keyword in PROC SQL

LinusH — Fri, 27 Mar 2015 13:04:56 GMT

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.

Re: Missing keyword in PROC SQL

Belle — Fri, 27 Mar 2015 16:32:47 GMT

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.

Re: Missing keyword in PROC SQL

Patrick — Fri, 27 Mar 2015 22:56:58 GMT

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')

....

Re: Missing keyword in PROC SQL

Belle — Fri, 27 Mar 2015 23:10:31 GMT

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.

Re: Missing keyword in PROC SQL

Patrick — Fri, 27 Mar 2015 23:58:38 GMT

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.