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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

11 REPLIES 11
Scott_Mitchell
Quartz | Level 8

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.

KimoMakano
Calcite | Level 5

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

Scott_Mitchell
Quartz | Level 8

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.

KimoMakano
Calcite | Level 5

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.

Scott_Mitchell
Quartz | Level 8

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.

RichardinOz
Quartz | Level 8

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;

jakarman
Barite | Level 11

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 --<-----
KimoMakano
Calcite | Level 5

Mahalo Jaap,

DCL_SYS_YMD is a date field.

jakarman
Barite | Level 11

==  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 --<-----
Tom
Super User Tom
Super User

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

KimoMakano
Calcite | Level 5

Many mahalos Tom,

This worked!!

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

Mahalos to all for your assistance.


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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 11 replies
  • 2559 views
  • 2 likes
  • 5 in conversation