How to query dates in SAS

2
votes

I need to export some data from SAS to CSV, so that I can move it to a SQL Server and load it into there. (The servers can't see each other.)

In the data is a field with the following definitions:

  • Type = Number
  • Length = 8
  • Format = DATETIME18.

For now I'm just trying to see how many records exist in a date range:

proc sql;

SELECT COUNT(*)
FROM BNA_BASE.base_agent_bna_cust_date
WHERE bna_outcome_ts >= '04Jun12:00:00:00'd
  AND bna_outcome_ts <  '11Jun12:00:00:00'd
;

quit;

But I always get 0, even though I can see in the table that there are records which match what I thought I was querying, such as 06JUN12:12:42:57.

Can anyone point out my stupid mistake?

2
datesas

2 Answers

5
votes

I should have used dt not just d...

WHERE bna_outcome_ts >= '04Jun12:00:00:00'dt
  AND bna_outcome_ts <  '11Jun12:00:00:00'dt
3
votes

An alternative to Dems answer is to use the datepart() function,

e.g.

where datepart(bna_outcome_ts) >= '04jun2012'd
and datepart(bna_outcoume_ts) < '11jun2012'd