0

I am using the below query in SAS Enterprise Guide to find the count for different offer_ids customers for different dates :

PROC SQL;               
CREATE TABLE test1 as
select offer_id,
       (Count(DISTINCT (case when date between '2016-11-13' and '2016-12-27' then customer_id else 0 end))) as CUSTID
from test
group by offer_id
;QUIT;

ERROR: Expression using IN has components that are of different data types Note: Here, Offer_id is the character variable whereas Custome_id is an numeric variable.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
Anupam Das
  • 11
  • 1
  • 4
  • There's no `IN` in you existing query. If this select is processed by Teradata `date` is a keyword, today. – dnoeth Feb 27 '17 at 12:49

2 Answers2

3

Most likely the error is caused by comparing the numeric variable DATE to the character strings '2016-11-13'. If you want to specify a date literal in SAS you must specify the date in style that is recognized by the DATE informat and append the letter D after the close quote.

date BETWEEN '13NOV2016'd AND '27DEC2016'd

Note that there is no reference to any external database in the posted code. But even if your source table was tdlib.tdtable instead of work.test you still need to use SAS syntax when writing SAS code. Let the Teradata engine figure out how to convert it for you.

Tom
  • 47,574
  • 2
  • 16
  • 29
0

You don't make it clear whether this is being run on SAS or Teradata (via pass through).
I'm guessing SAS, in which case you are missing d after your dates (e.g. '2016-11-13'd). Without this, the dates are being treated as text instead of formatted numbers.
The error statement is slightly misleading, as SAS is treating the between statement as an in statement.

Longfish
  • 7,582
  • 13
  • 19