topic Re: Comparing date values in where clause in SAS Programming

Comparing date values in where clause

sivaranjani — Sun, 10 Nov 2019 18:11:26 GMT

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!

Re: Comparing date values in where clause

Astounding — Fri, 08 Nov 2019 22:58:17 GMT

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.

Re: Comparing date values in where clause

sivaranjani — Fri, 08 Nov 2019 23:25:56 GMT

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.

Re: Comparing date values in where clause

novinosrin — Fri, 08 Nov 2019 23:29:38 GMT

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;

Re: Comparing date values in where clause

Reeza — Fri, 08 Nov 2019 23:35:13 GMT

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!

Re: Comparing date values in where clause

sivaranjani — Fri, 08 Nov 2019 23:47:05 GMT

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

Re: Comparing date values in where clause

sivaranjani — Fri, 08 Nov 2019 23:49:58 GMT

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

Re: Comparing date values in where clause

Patrick — Sat, 09 Nov 2019 00:17:27 GMT

@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;

Re: Comparing date values in where clause

Reeza — Sat, 09 Nov 2019 01:54:58 GMT

@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

Re: Comparing date values in where clause

sivaranjani — Sun, 10 Nov 2019 18:07:49 GMT

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

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.;

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

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!

Re: Comparing date values in where clause

Reeza — Sun, 10 Nov 2019 21:01:42 GMT

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/ta-p/424354

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!

Re: Comparing date values in where clause

Reeza — Sun, 10 Nov 2019 21:03:02 GMT

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.

Re: Comparing date values in where clause

Tom — Sun, 10 Nov 2019 21:33:20 GMT

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.

Re: Comparing date values in where clause

Patrick — Sun, 10 Nov 2019 22:41:33 GMT

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;

Re: Comparing date values in where clause

sivaranjani — Mon, 11 Nov 2019 01:23:32 GMT

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!

Re: Comparing date values in where clause

Tom — Mon, 11 Nov 2019 03:45:57 GMT

So ACTIVITY_DATE does NOT have DATE values. It has DATETIME values. So you want to compare to DATETIME values (number of seconds since 1960) and not a DATE values (number of days since 1960).

If you want to use a macro variable to hold DATETIME values then either leave it with the raw number of seconds since 1960 and don't add the quotes.

CALL SYMPUTX('MonthStart',INTNX('DTMONTH',DATETIME(),0,'B'));
...
WHERE ACTIVITY_DATE>=&MonthStart. ...

or generate it in a format that the DATETIME informat can read and add the quotes and the dt suffix.

CALL SYMPUTX('MonthStart',put(INTNX('DTMONTH',DATETIME(),0,'B')),datetime20.);
...
WHERE ACTIVITY_DATE>="&MonthStart."dt ...

A couple of other things. Don't use the old CALL SYMPUT() instead of the newer CALL SYMPUTX() unless you really need to store leading and/or trailing spaces into the macro variable.

Speaking of leading spaces why does one of your variables appear to have a leading space in the photograph of the output of proc contents? Is that really a space? Is it an underscore that is just blending into the border lines? If it really is a leading space then you will need to use a name literal to reference that variable.

' ACCOUNT_NUM'n

Re: Comparing date values in where clause

Patrick — Mon, 11 Nov 2019 12:05:46 GMT

Given that you're comfortable using Toad to write and test Oracle syntax it's at current stage may be easier for you to use the exactly same Oracle syntax also within a SAS program (=you can copy/paste the syntax from Toad to SAS).

Using SQL this way is called Explicit pass-through SQL in SAS documentation. Explicit SQL gets sent by SAS without any changes directly to the database for execution.

In below code the explicit pass-through SQL is within the brackets of the ...from connection to (<explicit Oracle SQL>).

SAS Macro code gets pre-processed and though you can still use SAS Macros in explicit pass-through SQL. The SAS macro variables get resolved before SAS sends the SQL to the database.

I couldn't test it but below should work based on all the code you've already shared with us. It assumes that libref DB points to the Oracle database.

data _null_;
  call symputx('monthpartition',put(intnx('month',today(),-1,'b'),yymmn6.));
  call symputx('monthstart',cats("'",put(intnx('month',today(),0,'b'),yymmdd10.),"'"));
  call symputx('monthstartPlus1',cats("'",put(intnx('month',today(),1,'b'),yymmdd10.),"'"));
  stop;
run;
%put &=monthpartition;
%put &=monthstart;
%put &=monthstartPlus1;

proc sql;
  connect using db as ora;
  create table current_month_activity as
  select * from connection to ora
  (
    SELECT BASE.*, K.COL1, K.COL2
    FROM 
      MI_MONTH_END_BASE_&MonthPartition BASE 
      LEFT JOIN 
      K_MASTER K
        ON BASE.ACCOUNT_NUM=K.ACCOUNT_NUM AND BASE.SUBSCRIPTION=K.SUBSCRIPTION
           and K.ACTIVITY_DATE >= date &monthstart and K.ACTIVITY_DATE < date &monthstartPlus1
  )
  ;
quit;

The word doc you've posted shows that the data types for the same named variables in the two SAS tables are different. Based on the code you've shared I don't really understand how this could happen and further investigation would be necessary (like: What are the data types in the Oracle tables). But let's park this for now and try making the explicit pass-through approach working.

Should this not work:

Copy the explicit-pass through bit into a text editor, replace the macro variables 1:1 to what they resolve to (=what the %put statements print into the SAS log as values) and then execute the code in Toad. Debug the code in Toad, copy/paste the fully working code back into SAS and replace the fixed strings with the macro variables.