Using a macro in a where statement

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Using a macro in a where statement

Aloha,

I am new to this community and have a question.

I am trying to update a current SAS program to automate it as much as possible.

This program extracts data based on selected time periods.

Rather than having to open the program and updating the dates I would like to be able to have it run and automatically determined the "BEGIN" and "END" periods.

Current code (modifiied)

%LET BDATE = '2014-05-01';  /* Sets the beginning of the extract as the first day of the previous month */

%LET EDATE = '2014-06-30';  /* Sets the ending of the extract as the last day of the current month */

PROC SQL ;

  CONNECT TO ODBC(DSN=DB0P UID=&dssuid PWD=&dsspwd);

  CREATE TABLE DATA1 AS SELECT *

  FROM CONNECTION TO ODBC

  (SELECT

  DCL_SYS_YMD                       AS ENTRY_DT,

  DCL_PROC_YMD                    AS SERV_DT,

  DCL_PROV1                            AS PROVIDER,

  DCL_RSN_CODE1                   AS RVW_RSN1,

  DCL_RSN_CODE2                   AS RVW_RSN2

  FROM GETME.DRCLAIMS

  WHERE  DCL_SYS_YMD BETWEEN &BDATE AND &EDATE

   );

  %PUT &SQLXMSG;

  DISCONNECT FROM ODBC;

QUIT;

I tried using :

%LET BPRD = intnx('month',today(),-1,'b');   

%LET EPRD = intnx('month',today(),0,'e'); 

%LET BPR = put(&BPRD,DATE9.);  

%LET EPR = put(&EPRD,DATE9.);

and modified my where statement to

WHERE  DCL_SYS_YMD BETWEEN &BPR AND &EPR

I get the following error message:

ERROR: CLI describe error: [IBM][CLI Driver][DB2] SQL0104N  An unexpected token ")" was found following ")".  Expected

       tokens may include:  "NEXTVAL CURRVAL".  SQLSTATE=42601

5079    %PUT &SQLXMSG;

[IBM][CLI Driver][DB2] SQL0104N  An unexpected token ")" was found following ")".  Expected tokens may include:  "NEXTVAL

CURRVAL".  SQLSTATE=42601

I have tried different ways but cannot get it to recognize the dates, any assistance would be greatly appreciated.

Mahalo, Jim


Accepted Solutions
Solution
‎06-04-2014 06:15 AM
Super User
Super User
Posts: 7,039

Re: Using a macro in a where statement

Posted in reply to KimoMakano

You cannot use the SAS functions INTNX() or PUT() inside of the SQL code that you are manually passing to ODBC (unless perhaps your ODBC connection is to a SAS server).

It looks like your foreign database liked it when you used quoted strings that looked like 'YYYY-MM-DD' so you need to use those types of values in your generated code.

Since you are doing this in macro world you need to use %SYSFUNC() and remove the quotes around the character arguments to INTNX().

You can use the DEQUOTE() function as a easy trick to get the values inside of single quotes instead of double quotes.

%LET BPRD = %sysfunc(dequote("'%sysfunc(intnx(month,%sysfunc(today()),-1,b),yymmdd10.)'"));  

%LET EPRD = %sysfunc(dequote("'%sysfunc(intnx(month,%sysfunc(today()),0,e),yymmdd10.)'"));

View solution in original post


All Replies
Super Contributor
Posts: 297

Re: Using a macro in a where statement

Posted in reply to KimoMakano

DATA _NULL_;

CALL SYMPUT ("BPRD",INTNX('MONTH',TODAY(),-1,'B')); 

CALL SYMPUT ("EPRD",INTNX('MONTH',TODAY(),0,'E'));

RUN;

%PUT BPRD = &BPRD. EPRD = &EPRD.;

You can learn more about the CALL SYMPUT routine at SAS(R) 9.2 Macro Language: Reference.

Occasional Contributor
Posts: 5

Re: Using a macro in a where statement

Posted in reply to Scott_Mitchell

Mahalo Scott,

When I try that I get this error.

ERROR: CLI describe error: [IBM][CLI Driver][DB2] SQL0401N  The data types of the operands for the operation "" are not compatible or comparable.  SQLSTATE=42818 8304   

%PUT &SQLXMSG;

[IBM][CLI Driver][DB2] SQL0401N  The data types of the operands for the operation "" are not compatible or comparable.

SQLSTATE=42818

Super Contributor
Posts: 297

Re: Using a macro in a where statement

Posted in reply to KimoMakano

Firstly you don't have a macro variable called &BDATE or &EDATE.  So you need to create these.


Secondly the error suggests to me that you are attempting to compare a character value with a numeric variable.  You need to ensure that you are comparing like data types.

Occasional Contributor
Posts: 5

Re: Using a macro in a where statement

Posted in reply to Scott_Mitchell

Mahalo Scott,

I created them via

%LET BDATE = '2014-05-01';  /* Sets the beginning of the extract as the first day of the previous month */

%LET EDATE = '2014-06-30';  /* Sets the ending of the extract as the last day of the current month */

In the PROC SQL statement it reads these and works well.

Super Contributor
Posts: 297

Re: Using a macro in a where statement

Posted in reply to KimoMakano

So you need to change

DATA _NULL_;

CALL SYMPUT ("BPRD",INTNX('MONTH',TODAY(),-1,'B'));

CALL SYMPUT ("EPRD",INTNX('MONTH',TODAY(),0,'E'));

RUN;

to

DATA _NULL_;

CALL SYMPUTX ("BDATE",QUOTE(PUT(INTNX('MONTH',TODAY(),-1,'B'),YYMMDDD10.)));  

CALL SYMPUTX ("EDATE",QUOTE(PUT(INTNX('MONTH',TODAY(),0,'E'),YYMMDDD10.)));

RUN;

%PUT &BDATE. &EDATE.;

This produces "2014-05-01" "2014-06-30", which replicate what you have been entering manually.

Super Contributor
Posts: 644

Re: Using a macro in a where statement

Posted in reply to Scott_Mitchell

Alternately, use the %Sysfunc() macro function in a %Let statement to permit use of non macro functions such as INTNX().  Note that the macro language is essentially a text language so wuoting of constants is not required - cf month, b and e below.  Also, you need to use putn() or putc() with %Sysfunc - I have added quotes and a trailing D to convert BPR and EPR to SAS date literals (which do require quotes - double in this instance to allow macro resolution.

Richard

%LET BPRD = %Sysfunc(intnx(month, %Sysfunc(today()),-1,b));   

%LET EPRD = %Sysfunc(intnx(month, %Sysfunc(today()),0,e)); 

%LET BPR = "%Sysfunc(putn(&BPRD,DATE9))"D;  

%LET EPR = "%Sysfunc(putn(&EPRD,DATE9))"D;

Trusted Advisor
Posts: 3,212

Re: Using a macro in a where statement

Posted in reply to KimoMakano

The macro-vars bdate and edate are created first but defined as strings.
The SAS environment is having well defined conventions for date time and datatime.  DB2 is not using those conventions, but having his own.

In the SQL you  are coding: "WHERE  DCL_SYS_YMD BETWEEN &BDATE AND &EDATE"

The definition of DCL_SYS_YMD is not clear is it char or numeric how is it converted between DB2 ODBC into SAS vice versa.

A string (character) using a between is not really logical.
That is the first action you should do, investigate in researching of the datatypes and conversions.

When you have the types in SAS been converted to date-processing (validated DCL_SYS_YMD) you can proceed to SQL coding.
For the more standardizing you could thing of a standard macro setting al those kind of needed date references.

    

---->-- ja karman --<-----
Occasional Contributor
Posts: 5

Re: Using a macro in a where statement

Mahalo Jaap,

DCL_SYS_YMD is a date field.

Trusted Advisor
Posts: 3,212

Re: Using a macro in a where statement

Posted in reply to KimoMakano

==  DCL_SYS_YMD is a date field ==     Also seen this by SAS as data-type I assume, not only being a DB2 definition
Then use the d (date) modifier behind those strings "2014-05-01"d    "2014-06-30"d  as SAS will convert them to SAS-dates in those cases (not being strings).

For better performance look at the sqlconstdatetime option SAS(R) 9.3 SQL Procedure User's Guide
To follow what is happening think about using sastrace option SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition It will show you what is happening when translating SAS-ansi SQL to native SQL statements  

---->-- ja karman --<-----
Solution
‎06-04-2014 06:15 AM
Super User
Super User
Posts: 7,039

Re: Using a macro in a where statement

Posted in reply to KimoMakano

You cannot use the SAS functions INTNX() or PUT() inside of the SQL code that you are manually passing to ODBC (unless perhaps your ODBC connection is to a SAS server).

It looks like your foreign database liked it when you used quoted strings that looked like 'YYYY-MM-DD' so you need to use those types of values in your generated code.

Since you are doing this in macro world you need to use %SYSFUNC() and remove the quotes around the character arguments to INTNX().

You can use the DEQUOTE() function as a easy trick to get the values inside of single quotes instead of double quotes.

%LET BPRD = %sysfunc(dequote("'%sysfunc(intnx(month,%sysfunc(today()),-1,b),yymmdd10.)'"));  

%LET EPRD = %sysfunc(dequote("'%sysfunc(intnx(month,%sysfunc(today()),0,e),yymmdd10.)'"));

Occasional Contributor
Posts: 5

Re: Using a macro in a where statement

Many mahalos Tom,

This worked!!

I am very greatful for your support, this is just what I needed.

Mahalos to all for your assistance.


🔒 This topic is solved and locked.

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

Discussion stats
  • 11 replies
  • 890 views
  • 2 likes
  • 5 in conversation