dttm variable with DATETIME20. format

[bhca60]

Hi,

I have been working on this all week.  I have a program I am converting and having issues with a variable called ai_dttm with format DATETIME20. LEN=8, Numeric.  This is how it is set up in the program for the &start. and &end. macro which I use in a date filter -> (datepart(a.ai_dttm) between &start. and &end.) 

and 

This is the error I get due to the date:  ERROR: Expression using IN has components that are of different data types.

Setup:
We = intnx("week",td-1,1)-1; 
mb13 = intnx('month',td-1,-12); 

(*I tried changing THE 10. TO 20., but still get the IN ERROR; I even changed it to DATETIME20. and still get the error*)

call symputx('start',"'"||put(mb13,yymmddd10.)||"'",'G');
call symputx('end',"'"||put(we,yymmddd10.)||"'",'G');

%put start = &start.;
%put end = &end.;

The column  "ai_dttm" looks like this when output: 

01FEB2016:00:00:00

My proc sql step runs fine without this date filter but I need it in the step.  Thanks

[PaigeMiller]

  ERROR: Expression using IN has components that are of different data types.

You didn't show us any code that could cause this error. However, it indicates that somewhere a variable is character when it should be numeric, or vice versa. Please show us the entire log, including the code and all NOTES, ERRORS and WARNINGS as it appears in the log, down to and including this error message. Please copy this log as text, and paste it into the window that appears when you click on the </> icon. (For your future benefit,never show us ERROR messages detached from the code that caused it. Also do not show us tiny sections of code. We need the entire log down to the error.)

(*I tried changing THE 10. TO 20., but still get the IN ERROR; I even changed it to DATETIME20. and still get the error*)

Changing a format doesn't address the problem that causes this ERROR.

[Quentin · Accepted solution]

When posting questions, it's helpful to provide example data, so that we can replicate your problem.  Based on your description, I was able to replicate your error message with below example data:

data _null_ ;
  td=today() ;

  We = intnx("week",td-1,1)-1; 
  mb13 = intnx('month',td-1,-12); 

  call symputx('start',"'"||put(mb13,yymmddd10.)||"'",'G');
  call symputx('end',"'"||put(we,yymmddd10.)||"'",'G');
run ;

%put start = &start.;
%put end = &end.;

data have ;
  input ai_dttm datetime. ;
  format ai_dttm datetime. ;
  cards ;
28FEB2023:00:00:00
05MAR2024:00:00:00
;
run ;

options symbolgen ;

proc sql ;
   select * 
   from have
   where  (datepart(ai_dttm) between &start and &end) 
 ;
quit ;

You said you have a WHERE clause in your SQL with a clause like:

where  (datepart(ai_dttm) between &start and &end) 

Note your macro variables START and END result to a text literals, so above will resolve to code like:

where  (datepart(ai_dttm) between '2023-03-01' and  '2024-03-16' ) 

That WHERE clause can't work.  It's trying to compare a numeric value on the left which results from DATEPART, to a range of character values on the BETWEEN operator.  That will generate the error message.  Instead of text literals, you need date values for the BETWEEN operator.  One way to get these would be to change your CALL SYMPUTX statements to:

  call symputx('start',"'"||put(mb13,date9.)||"'d",'G');
  call symputx('end',"'"||put(we,date9.)||"'d",'G');

I changed the format to DATE9, and added a d after the quote marks. This will make the START and END resolve to valid date literals:

179  %put start = &start.;
start = '01MAR2023'd
180  %put end = &end.;
end = '16MAR2024'd

Date literals are numeric values, so they will work on your WHERE clause.

[bhca60]

That did it! Thank you very much.

[Patrick]

The main bit you're missing is that when using intnx with SAS datetime values the name of the directives need to start with DT... (like: DTweek). The other option would be to first convert the datetime to a date value using datepart().

Given that your main table contains datetime values I would stick to working with datetime values using  a.ai_dttm between &start. and &end. because:

- Should there be an index on ai_dttm then using the datepart() function will not allow to use this index

- Should your table reside in a database then using the datepart() function could cause SAS to pull the data to the SAS side for sub-setting.

Below some sample code how you could go about this.

Option 2 and 3 are preferable because they populate &start and &end with a string in the datetime20 format. The conversion to a datetime value happens in the SQL itself via "<datetime string"DT In doing so SAS "knows" that this is a SAS datetime value and if pushing the query to a database will convert the SAS Datetime value to the matching representation in the database (like: timestamp). This wouldn't happen with Option 1 where SAS wouldn't know that this is a SAS Datetime value but "think" it's just a number and push it as a number to the DB. Option 1 works only for SAS tables. 

data have;
  format ai_dttm datetime20.;
  do ai_dttm=intnx('dtday',datetime(),-40,'b') to intnx('dtday',datetime(),40,'b') by 100012;
    var+1;
    output;
  end;
run;

/* option 1 */
data _null_;
  start1=intnx('dtweek',datetime(),-1,'b');
  end1 =intnx('dtmonth',datetime(),0,'e');
  call symputx('start1',start1,'g');
  call symputx('end1',end1,'g');
run;

proc sql;
  select 
    ai_dttm,
    count(*) as n_rows,
    &start1 as start format=datetime20.,
    &end1   as stop  format=datetime20.
  from have
  where ai_dttm between &start1 and &end1;
  ;
quit;

/* option 2 */
data _null_;
  start2=intnx('dtweek',datetime(),-1,'b');
  end2 =intnx('dtmonth',datetime(),0,'e');
  call symputx('start2',put(start2,datetime20.),'g');
  call symputx('end2',put(end2,datetime20.),'g');
run;

proc sql;
  select 
    ai_dttm,
    count(*) as n_rows,
    "&start2"dt as start format=datetime20.,
    "&end2"dt   as stop  format=datetime20.
  from have
  where ai_dttm between "&start2"dt and "&end2"dt;
  ;
quit;

/* option 3 */
%let start3=%sysfunc(intnx(dtweek,%sysfunc(datetime()),-1,b),datetime20.);
%let end3  =%sysfunc(intnx(dtmonth,%sysfunc(datetime()),0,e),datetime20.);
proc sql;
  select 
    ai_dttm,
    count(*) as n_rows,
    "&start3"dt as start format=datetime30.,
    "&end3"dt   as stop  format=datetime30.
  from have
  where ai_dttm between "&start3"dt and "&end3"dt;
  ;
quit;

 And last but not least: If using intnx() and aligning SAS datetime value to the end of the directive intnx('dtmonth',datetime(),0,'e') you get the last second of the boundary - like 31MAR2024:23:59:59 

If in your source you've also got fractional seconds then using the between operator you risk to miss the last last second before midnight. Below option 4 avoids this potential issue.

/* option 4 */
data _null_;
  start4=intnx('dtweek',datetime(),-1,'b');
  end4 =intnx('dtmonth',datetime(),0,'e');
  end4=end4+1;
  call symputx('start4',put(start4,datetime20.),'g');
  call symputx('end4',put(end4,datetime20.),'g');
run;

proc sql;
  select 
    ai_dttm,
    count(*) as n_rows,
    "&start4"dt as start format=datetime20.,
    "&end4"dt   as stop  format=datetime20.
  from have
  where "&start4"dt <= ai_dttm < "&end4"dt;
  ;
quit;

[ballardw]

Why did you format the dates at all? That is only a good idea when you expect people to read some.

Plus the only form of a date literal value that SAS will use is something in the date format such as "01JAN2024"D

You apparently are creating a value that looks like '2024-01-01'  So will only ever be consider a character value by SAS.

See what happens with:

call symputx('start',mb13,'G');
call symputx('end',we,'G');