BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Belle
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

6 REPLIES 6
Patrick
Opal | Level 21

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
Obsidian | Level 7

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
Opal | Level 21

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
Obsidian | Level 7

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
Opal | Level 21

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
Tourmaline | Level 20

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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