Comparing date values in where clause

[sivaranjani]

Hi,

I used the following query in Toad for Oracle and it works fine without issues.

SELECT BASE.*, K.COL1, K.COL2
FROM MONTH_END_BASE BASE LEFT JOIN K_MASTER K
ON BASE.ACCOUNT_NUM=K.ACCOUNT_NUM AND BASE.SUBSCRIPTION=K.SUBSCRIPTION
WHERE EXTRACT (MONTH FROM KPI.ACTIVITY_DATE)=09 AND EXTRACT (YEAR FROM KPI.ACTIVITY_DATE)=2019 ;

In SAS I changed the query as below

DATA _NULL_;
CALL SYMPUT('MonthStart',TRIM(PUT(INTNX('MONTH',TODAY(),0,'B'),ddmmyys10.)));
CALL SYMPUT('MonthEnd',TRIM(PUT(INTNX('MONTH',TODAY(),0,'E'),ddmmyys10.)));
RUN;

%PUT &MonthStart.;
%PUT &MonthEnd.;

PROC SQL;
CREATE TABLE CURRENT_MONTH_ACTIVITY AS
SELECT BASE.*, K.COL1, K.COL2
FROM MONTH_END_BASE BASE LEFT JOIN K_MASTER K
ON BASE.ACCOUNT_NUM=K.ACCOUNT_NUM AND BASE.SUBSCRIPTION=K.SUBSCRIPTION
WHERE K.ACTIVITY_DATE >= "&MonthStart." AND K.ACTIVITY_DATE <= "&MonthEnd." ;
QUIT;

I am getting the error: Expressions using equals (=) has components that are of different data types for all three conditions, that is, BASE.ACCOUNT_NUM=K.ACCOUNT_NUM, BASE.SUBSCRIPTION=K.SUBSCRIPTION and K.ACTIVITY_DATE>= "&MonthStart".

When this works in Toad, it means the datatypes are not the problem. But its not working in SAS.

Friday when I was trying this, the error message was only for the date comparison, so I thought I am doing something wrong with the SAS date format. But when I comment the where condition, the same error is thrown for the condition on "ON" as well. Not sure what the issue is.

I am a newbie to SAS. Literally my first week with SAS programming. I still haven't got a hang of how SAS works. 

Any help with this issue is much appreciated!

Thanks in advance!

[Astounding]

Single quotes prevent all macro activity.  Begin by putting your references to macro variables in double quotes, then see if any problems remain.

You may need to show us a hard-coded (no macro language) version of a working program to get further advice.

[sivaranjani]

Double quotes didn't help. Also I tried with different date values as well (01Nov2019, 01/11/2019) and I tried the input function to change to different formats in where clause itself. For all the tries, I am getting the same error. No clue whats wrong.

The Activity Date column is of Date datatype in the table. 

[novinosrin]

Hi @sivaranjani   Why bother with a macro for a simple filter/*current month*/

PROC SQL;
CREATE TABLE CURRENT_MONTH_ACTIVITY AS
SELECT * FROM MONTH_END_BASE
WHERE put(ACTIVITY_DATE ,monyy7. -l)=put(today(),monyy7. -l);
QUIT;

[sivaranjani]

Thank you for your suggestion but it didn't work. I am still getting the same error 😞

[Reeza]

---

@sivaranjani wrote:

Thank you for your suggestion but it didn't work. I am still getting the same error 😞

---

SAS doesn't actually have a DATE type so can you post a proc contents on your month end base data set to show what the type and format? Type should be numeric with a format such as mmddy10 

[Reeza]

You have two issues, one is a the double quotes the second is invalid code or format.

What you're generating, assuming the macro does resolve correctly is:

PROC SQL;
CREATE TABLE CURRENT_MONTH_ACTIVITY AS
SELECT * FROM MONTH_END_BASE
WHERE ACTIVITY_DATE >= "2019/10/01" AND ACTIVITY_DATE <= "2019/10/30" ;
QUIT;

That isn't valid SAS code. YOu need to pass dates either in the date9 format or no format. 

No format is easier but harder to read/debug, so use date9 to get your code working. 

Note that I had to add a d to the end to indicate it's a date literal. Using the unformatted value saves those steps. 

DATA _NULL_;
CALL SYMPUT('MonthStart',TRIM(PUT(INTNX('MONTH',TODAY(),0,'B'), date9.)));
CALL SYMPUT('MonthEnd',TRIM(PUT(INTNX('MONTH',TODAY(),0,'E'), date9.)));
RUN;

%PUT &MonthStart.;
%PUT &MonthEnd.;


PROC SQL;
CREATE TABLE CURRENT_MONTH_ACTIVITY AS
SELECT * FROM MONTH_END_BASE
WHERE ACTIVITY_DATE >= "&MonthStart."d AND ACTIVITY_DATE <= "&MonthEnd."d ;
QUIT;

 

---

@sivaranjani wrote:

Hi,

I am trying to get the current month records from a table. Since this is going to be an automated monthly job, I can't give explicit values in where clause. So I am using the intnx function to get the month's start and end date. Then comparing them with the table value (date) in where clause. I tried different date formats but every time I am getting the same error: "ERROR: Expression using equals (=) has components that are of different data types."

Below is my script. What am I doing wrong?

DATA _NULL_;
CALL SYMPUT('MonthStart',TRIM(PUT(INTNX('MONTH',TODAY(),0,'B'),ddmmyys10.)));
CALL SYMPUT('MonthEnd',TRIM(PUT(INTNX('MONTH',TODAY(),0,'E'),ddmmyys10.)));
RUN;

%PUT &MonthStart.;
%PUT &MonthEnd.;

PROC SQL;
CREATE TABLE CURRENT_MONTH_ACTIVITY AS
SELECT * FROM MONTH_END_BASE
WHERE ACTIVITY_DATE >= '&MonthStart.' AND ACTIVITY_DATE <= '&MonthEnd.' ;
QUIT;

Thanks in advance!

---

[sivaranjani]

Thank you for your suggestion. I tried both your code snippets, with the date value hard-coded and also with the date9. format and date literal. I am still getting the same error. The datatype of Activity Date is "Date" and the values are as below
23/01/2018
24/01/2018
25/01/2018
24/01/2018
15/01/2018

[Patrick]

---

@sivaranjani wrote:

Thank you for your suggestion. I tried both your code snippets, with the date value hard-coded and also with the date9. format and date literal. I am still getting the same error. The datatype of Activity Date is "Date" and the values are as below

---

The code snippet as posted by @Reeza works for me (see code below).

If that's not working for you then please post the first error message you get in the SAS log.

data MONTH_END_BASE;
  input ACTIVITY_DATE :ddmmyy10.;
  format ACTIVITY_DATE ddmmyy10.;
  datalines;
23/01/2018
24/01/2018
25/01/2018
24/01/2018
15/01/2018
15/11/2019
;

DATA _NULL_;
  CALL SYMPUT('MonthStart',TRIM(PUT(INTNX('MONTH',TODAY(),0,'B'), date9.)));
  CALL SYMPUT('MonthEnd',TRIM(PUT(INTNX('MONTH',TODAY(),0,'E'), date9.)));
RUN;

%PUT &MonthStart.;
%PUT &MonthEnd.;

PROC SQL;
  CREATE TABLE CURRENT_MONTH_ACTIVITY AS
    SELECT * FROM MONTH_END_BASE
      WHERE ACTIVITY_DATE >= "&MonthStart."d AND ACTIVITY_DATE <= "&MonthEnd."d;
QUIT;

proc print data=CURRENT_MONTH_ACTIVITY;
run;

[sivaranjani]

I used the following query in Toad for Oracle and it works fine without issues.

SELECT BASE.*, K.COL1, K.COL2
FROM MONTH_END_BASE BASE LEFT JOIN K_MASTER K
ON BASE.ACCOUNT_NUM=K.ACCOUNT_NUM AND BASE.SUBSCRIPTION=K.SUBSCRIPTION
WHERE EXTRACT (MONTH FROM KPI.ACTIVITY_DATE)=09 AND EXTRACT (YEAR FROM KPI.ACTIVITY_DATE)=2019 ;

In SAS I changed the query as below

DATA _NULL_;
CALL SYMPUT('MonthStart',TRIM(PUT(INTNX('MONTH',TODAY(),0,'B'),ddmmyys10.)));
CALL SYMPUT('MonthEnd',TRIM(PUT(INTNX('MONTH',TODAY(),0,'E'),ddmmyys10.)));
RUN;

%PUT &MonthStart.;
%PUT &MonthEnd.;

PROC SQL;
CREATE TABLE CURRENT_MONTH_ACTIVITY AS
SELECT BASE.*, K.COL1, K.COL2
FROM MONTH_END_BASE BASE LEFT JOIN K_MASTER K
ON BASE.ACCOUNT_NUM=K.ACCOUNT_NUM AND BASE.SUBSCRIPTION=K.SUBSCRIPTION
WHERE K.ACTIVITY_DATE >= "&MonthStart." AND K.ACTIVITY_DATE <= "&MonthEnd." ;
QUIT;

I am getting the error: Expressions using equals (=) has components that are of different data types for all three conditions, that is, BASE.ACCOUNT_NUM=K.ACCOUNT_NUM, BASE.SUBSCRIPTION=K.SUBSCRIPTION and K.ACTIVITY_DATE>= "&MonthStart".

When this works in Toad, it means the datatypes are not the problem. But its not working in SAS.

Friday when I was trying this, the error message was only for the date comparison, so I thought I am doing something wrong with the SAS date format. But when I comment the where condition, the same error is thrown for the condition on "ON" as well. Not sure what the issue is.

I am a newbie to SAS. Literally my first week with SAS programming. I still haven't got a hang of how SAS works. 

Any help with this issue is much appreciated!

Thanks!

[Reeza]

If you're getting data from a database you likely have a datetime, not a date variable. 

They need to be handled differently. 

Try the following, which uses DATEPART() to convert the datetime to a date variable, which should work now. 

Here's a great, but longer and in depth, reference for dates and times in SAS that you should try reading through.
https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/...

DATA _NULL_;
CALL SYMPUT('MonthStart',TRIM(PUT(INTNX('MONTH',TODAY(),0,'B'), date9.)));
CALL SYMPUT('MonthEnd',TRIM(PUT(INTNX('MONTH',TODAY(),0,'E'), date9.)));
RUN;

%PUT &MonthStart.;
%PUT &MonthEnd.;


PROC SQL;
CREATE TABLE CURRENT_MONTH_ACTIVITY AS
SELECT * FROM MONTH_END_BASE
WHERE datepart(ACTIVITY_DATE) >= "&MonthStart."d AND datepart(ACTIVITY_DATE) <= "&MonthEnd."d ;
QUIT;

---

@sivaranjani wrote:

I used the following query in Toad for Oracle and it works fine without issues.

SELECT BASE.*, K.COL1, K.COL2
FROM MONTH_END_BASE BASE LEFT JOIN K_MASTER K
ON BASE.ACCOUNT_NUM=K.ACCOUNT_NUM AND BASE.SUBSCRIPTION=K.SUBSCRIPTION
WHERE EXTRACT (MONTH FROM KPI.ACTIVITY_DATE)=09 AND EXTRACT (YEAR FROM KPI.ACTIVITY_DATE)=2019 ;

In SAS I changed the query as below

DATA _NULL_;
CALL SYMPUT('MonthStart',TRIM(PUT(INTNX('MONTH',TODAY(),0,'B'),ddmmyys10.)));
CALL SYMPUT('MonthEnd',TRIM(PUT(INTNX('MONTH',TODAY(),0,'E'),ddmmyys10.)));
RUN;

%PUT &MonthStart.;
%PUT &MonthEnd.;

PROC SQL;
CREATE TABLE CURRENT_MONTH_ACTIVITY AS
SELECT BASE.*, K.COL1, K.COL2
FROM MONTH_END_BASE BASE LEFT JOIN K_MASTER K
ON BASE.ACCOUNT_NUM=K.ACCOUNT_NUM AND BASE.SUBSCRIPTION=K.SUBSCRIPTION
WHERE K.ACTIVITY_DATE >= "&MonthStart." AND K.ACTIVITY_DATE <= "&MonthEnd." ;
QUIT;

I am getting the error: Expressions using equals (=) has components that are of different data types for all three conditions, that is, BASE.ACCOUNT_NUM=K.ACCOUNT_NUM, BASE.SUBSCRIPTION=K.SUBSCRIPTION and K.ACTIVITY_DATE>= "&MonthStart".

When this works in Toad, it means the datatypes are not the problem. But its not working in SAS.

Friday when I was trying this, the error message was only for the date comparison, so I thought I am doing something wrong with the SAS date format. But when I comment the where condition, the same error is thrown for the condition on "ON" as well. Not sure what the issue is.

I am a newbie to SAS. Literally my first week with SAS programming. I still haven't got a hang of how SAS works. 

Any help with this issue is much appreciated!

Thanks!

---

[Reeza]

Are all these tables on the server? If you still get errors, you likely do have type mismatches and should run a PROC CONTENTS on each data set and post that information here for each variable in question.

[Tom · Accepted solution]

You still seemed confused about what code you are asking SAS to run and what code you giving to Teradata to run.

You should first try running the Teradata code that worked in the other tool.

proc sql ;
connect to teradata (....);
create table test1 as select * from connection to teradata
(select
  base.*
, k.col1
, k.col2
from month_end_base base
left join k_master k
  on base.account_num=k.account_num
 and base.subscription=k.subscription
where extract (month from kpi.activity_date)=09
  and extract (year from kpi.activity_date)=2019
);

If that works then try changing to the date range:

create table test1 as select * from connection to teradata
(select
  base.*
, k.col1
, k.col2
from month_end_base base
left join k_master k
  on base.account_num=k.account_num
 and base.subscription=k.subscription
where  kpi.activity_date between date '2019-09-01' and date '2019-08-30'
);

If that works then make your macro variables have that EXACT same formatting.  YYMMDD10 formatted date values quotes with SINGLE quote characters.  You can add single quotes using the QUOTE() function by including the optional second argument.

data _null_;
  call symputx('monthstart',quote(put(intnx('month',today(),0,'b'),yymmdd10.),"'");
  call symputx('monthend',quote(put(intnx('month',today(),0,'e'),yymmdd10.),"'");
run;

proc sql ;
connect to teradata (....);
create table test1 as select * from connection to teradata
(select
  base.*
, k.col1
, k.col2
from month_end_base base
left join k_master k
  on base.account_num=k.account_num
 and base.subscription=k.subscription
where  kpi.activity_date between date &monthstart. and date &monthend.
);

If your version of Teradata really wants date literals in that confusing day-month-year order that your SAS code was trying to use then use the DDMMYY format instead of the YYMMDD format in the PUT() function calls.  Or make your Teradata code more complex so that it can convert whatever string you do create in the macro variable into a value that will match the data type of the variable the the Teradata table.  Like you used the EXTRACT() function in your other Teradata code snippet.

[Patrick]

The Oracle query as posted can't work because you're using alias KPI in KPI.ACTIVITY_DATE but you've never defined this alias for a table. If you post code where you state it's working then try to post the actually tested code. ..but let's assume things would work if you'd use K.ACTIVITY_DATE

If you're that new to SAS then we need to take a step-by-step approach to get to the bottom of things (plus you need to do SAS training! There is some free-of charge training available to give you a start).

If the different data types Error is the first one in your SAS log then that's exactly what it is: Within the SAS SQL the data types are different.

You're using one level table names in the Oracle SQL you've posted. That means the tables you're using are under the Oracle schema to which you've connected via Toad.

You're also using one level names in the SAS SQL. SAS has also a two level syntax: <libref>.<table name>

A libref is a logical name which points to the connection to be used for accessing a table. The connection itself gets defined via a SAS LIBNAME statement. If you omit the libref then SAS uses WORK as libref. This is a special library for a SAS session specific Work area on disk which gets created when you invoke a SAS session.

What this means: The source tables used in your Oracle SQL are not the same than the ones used in the SAS SQL. There must be some prior processing which copies the Oracle tables into SAS WORK.

First steps:

1. Please share the upstream code which creates the tables in SAS Work (as this doesn't happen automatically)

2. Please share the upstream code which loads the tables from Oracle into SAS (that's eventually the same code bit like under 1 )

3. Execute the following code and share the result with us:

proc contents data=MONTH_END_BASE;
run;
proc contents data=K_MASTER;
run;

[sivaranjani]

Hi,

Thank you for your help. Please find the code and the proc content details below.

Also, thank you for suggesting the SAS course. I am doing an online SAS course.

DATA _NULL_;
CALL SYMPUT('MonthPartition',TRIM(PUT(INTNX('MONTH',TODAY(),-1,'B'),yymmn6.)));
RUN;

%PUT &MonthPartition.;

PROC SQL;
CREATE TABLE lib.MONTH_END_BASE AS
SELECT *
FROM DB.MI_MONTH_END_BASE_&MonthPartition;
QUIT;

DATA _NULL_;
CALL SYMPUT('MonthStart',TRIM(PUT(INTNX('MONTH',TODAY(),0,'B'),ddmmyy10.)));
CALL SYMPUT('MonthEnd',TRIM(PUT(INTNX('MONTH',TODAY(),0,'E'),ddmmyy10.)));

/* CALL SYMPUT('MonthStart',TRIM(PUT(INTNX('MONTH',TODAY(),0,'B'), date9.)));*/
/* CALL SYMPUT('MonthEnd',TRIM(PUT(INTNX('MONTH',TODAY(),0,'E'), date9.)));*/
RUN;

%PUT &MonthStart.;
%PUT &MonthEnd.;

PROC SQL;
CREATE TABLE lib.K_MASTER AS
SELECT * FROM DB.DAILY_K_MASTER;

QUIT;

PROC CONTENTS DATA=lib.MONTH_END_BASE;
RUN;
PROC CONTENTS DATA=lib.K_MASTER;
RUN;

PROC SQL;
CREATE TABLE CURRENT_MONTH_ACTIVITY AS
SELECT B.*, M.*
FROM lib.MONTH_END_BASE B
LEFT JOIN lib.K_MASTER M
ON B.ACCOUNT_NUM=M.ACCOUNT_NUM and B.SUBSCRIBER_NO=M.SUBSCRIBER_NO;
WHERE ACTIVITY_DATE>="&MonthStart." and ACTIVITY_DATE<="&MonthEnd.";
QUIT;

Thanks!