Formula for filtering dates

0
votes

I have an issue with my formula to select a date, using PROc SQL in SAS. What do I do wrong? Thank you all. Regards, Geoff

I use this coder:

FROM
  VTXBUSS.s
WHERE
  t1.ifrs_stage_date_at_start IS ’ 31DEC2017% ’
ORDER BY
  t1.customer_id,

I get a syntax error back. Dates are in this format: 31DEC2017:00:00:00.000000

SAS gives as log:

ERROR: The value '’'n is not a valid SAS name. WARNING: Apparent invocation of macro ’ not resolved. 45 WHERE t1.ifrs_stage_at_start NOT = t1.ifrs_stage_PROV AND t1.ifrs_stage_date_at_start = ’31DEC2017%’, _ 76 ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, AND, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT, HAVING, INTERSECT, LE, LET, LT, LTT, NE, NET, NOT, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~, ~=.

2
sasproc

2 Answers

0
votes

Try this:

FROM
  VTXBUSS.s
WHERE
  t1.ifrs_stage_date_at_start = '31DEC2017'd
ORDER BY
  t1.customer_id,

If you're looking for an specific "datetime", then you should use, for example:

FROM
  VTXBUSS.s
WHERE
  t1.ifrs_stage_date_at_start = '31DEC2017:00:00:00.000000'dt
ORDER BY
  t1.customer_id,

That is because, when dealing with dates in PROC SQL, you must specify the type of data you're looking for; putting "d" if DATE or "dt" if DATETIME

0
votes

If you want to filter DATETIME values by what DATE they represent you could convert the values to a DATE value.

WHERE datepart(t1.ifrs_stage_date_at_start) = '31DEC2017’d

Or convert to a specific DATETIME value

WHERE intnx('dtday',t1.ifrs_stage_date_at_start,0) = '31DEC2017:00:00’dt

Use a range of DATETIME values

WHERE t1.ifrs_stage_date_at_start between '31DEC2017:00:00’dt and '01JAN2018:00:00’dt

Or possibly convert to a character string

WHERE put(t1.ifrs_stage_date_at_start,datetime20.-L) like '31DEC2017:%'