topic Re: Using a macro in a where statement in SAS Programming

Using a macro in a where statement

KimoMakano — Wed, 04 Jun 2014 00:48:15 GMT

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

Re: Using a macro in a where statement

Scott_Mitchell — Wed, 04 Jun 2014 01:59:15 GMT

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.

Re: Using a macro in a where statement

KimoMakano — Wed, 04 Jun 2014 02:26:56 GMT

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

Re: Using a macro in a where statement

RichardinOz — Wed, 04 Jun 2014 02:31:03 GMT

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;

Re: Using a macro in a where statement

Scott_Mitchell — Wed, 04 Jun 2014 03:06:45 GMT

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.

Re: Using a macro in a where statement

jakarman — Wed, 04 Jun 2014 07:26:15 GMT

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.

Re: Using a macro in a where statement

KimoMakano — Wed, 04 Jun 2014 08:48:13 GMT

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.

Re: Using a macro in a where statement

KimoMakano — Wed, 04 Jun 2014 08:49:35 GMT

Mahalo Jaap,

DCL_SYS_YMD is a date field.

Re: Using a macro in a where statement

Scott_Mitchell — Wed, 04 Jun 2014 09:15:44 GMT

So you need to change

DATA _NULL_;

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

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

RUN;

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.

Re: Using a macro in a where statement

Tom — Wed, 04 Jun 2014 10:15:22 GMT

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.)'"));

Re: Using a macro in a where statement

jakarman — Wed, 04 Jun 2014 10:32:00 GMT

== 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

Re: Using a macro in a where statement

KimoMakano — Wed, 04 Jun 2014 17:18:15 GMT

Many mahalos Tom,

This worked!!

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

Mahalos to all for your assistance.