Help using Base SAS procedures

Missing keyword in PROC SQL

Accepted Solution Solved
Reply
Contributor
Posts: 67
Accepted Solution

Missing keyword in PROC SQL

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!


Accepted Solutions
Solution
‎03-27-2015 07:58 PM
Respected Advisor
Posts: 3,899

Re: Missing keyword in PROC SQL

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.

View solution in original post


All Replies
Respected Advisor
Posts: 3,899

Re: Missing keyword in PROC SQL

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.

Contributor
Posts: 67

Re: Missing keyword in PROC SQL

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.

Respected Advisor
Posts: 3,899

Re: Missing keyword in PROC SQL

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

....


Contributor
Posts: 67

Re: Missing keyword in PROC SQL

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.

Solution
‎03-27-2015 07:58 PM
Respected Advisor
Posts: 3,899

Re: Missing keyword in PROC SQL

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.

Super User
Posts: 5,260

Re: Missing keyword in PROC SQL

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.

Data never sleeps
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 642 views
  • 6 likes
  • 3 in conversation