Thursday, March 28, 2013

SAS: Careful when using functions in PROC SQL with Teradata!

This is a complicated issue to introduce:
  • I need to retrieve data from a Teradata table.
  • It will take several iterations due to size of the resulting table.
  • So I use macro variables to indicate the FIRST_DAY and LAST_DAY to be retrieved.
  • These variables are calculated (within a %DO loop) so they resolve to numeric values, not date constants.
  • The object of the WHERE clause is a Teradata timestamp (let's call it TS).
  • So I need to use the SAS' DATEPART function: WHERE datepart(TS) between &FIRST_DAY and &LAST_DAY
  • No error message, but when I ran the query, I got nothing back.
  • Turns out, that the FIRST_DAY and LAST_DAY variables must resolve to date constants for this to work.
  • So I added the following: %let FIRST_DAY_AS_DATE_CONSTANT = "%sysfunc(putn(&FIRST_DAY, date9.))"d;
  • and: %let LAST_DAY_AS_DATE_CONSTANT = "%sysfunc(putn(&LAST_DAY, &date9.))"d;
  • And then I used: WHERE datepart(TS) between &FIRST_DAY_AS_DATE_CONSTANT and &LAST_DAY_AS_DATE_CONSTANT
  • Success!
This is discussed at http://support.sas.com/kb/42/491.html

This is why you have to pay for experience!

No comments:

Post a Comment