DATETIME variable stored as a string

[SoozMorg]

I am using select into to store the most recent datetime record from my table as such:

  select max(MODSTAMP) 
  into   :max_ts
  from   my_table;
  %put max_ts:      &maxt_s;

This produces a value like 29JUN2017:23:59:52.000000.  The MODSTAMP in my Teradata table is stored as a TIMESTAMP(6).

I do this twice:  once to get the current max_ts, then I insert some records, and then again to get the new_maxts.

My problem is this:  when I compare max_ts to new_maxts, the values are stored and evaluated as strings:

%IF &new_maxts > &max_ts %THEN %DO;
  %goto start;
  %END;

When it rolls into a new month, the evaluation produces a fales result:

SYMBOLGEN:  Macro variable NEW_MAXTS resolves to 04MAR2017:00:39:08.000000

SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for printing.

SYMBOLGEN:  Macro variable MAX_TS resolves to 28FEB2017:17:23:56.000000

SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for printing.

MLOGIC(MYLOOP):  %IF condition &new_maxts > &max_ts is FALSE

The IF condition should be TRUE, but this happens because zero comes before two in sting fields.

I have tried tons of different formatting, including adding tickies and "dt" like '04MAR2017:00:39:08.000000'dt.

and the result was the same.

Any suggestions would be greatly appreciated!

[Reeza]

Convert it to a datetime variable then. 

Input(max(modstamp), anydtdtm.) into :max_dt

---

@SoozMorg wrote:

I am using select into to store the most recent datetime record from my table as such:

  select max(MODSTAMP) 
  into   :max_ts
  from   my_table;
  %put max_ts:      &maxt_s;

This produces a value like 29JUN2017:23:59:52.000000.  The MODSTAMP in my Teradata table is stored as a TIMESTAMP(6).

I do this twice:  once to get the current max_ts, then I insert some records, and then again to get the new_maxts.

My problem is this:  when I compare max_ts to new_maxts, the values are stored and evaluated as strings:

%IF &new_maxts > &max_ts %THEN %DO;
  %goto start;
  %END;

When it rolls into a new month, the evaluation produces a fales result:

SYMBOLGEN:  Macro variable NEW_MAXTS resolves to 04MAR2017:00:39:08.000000

SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for printing.

SYMBOLGEN:  Macro variable MAX_TS resolves to 28FEB2017:17:23:56.000000

SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for printing.

MLOGIC(MYLOOP):  %IF condition &new_maxts > &max_ts is FALSE

The IF condition should be TRUE, but this happens because zero comes before two in sting fields.

I have tried tons of different formatting, including adding tickies and "dt" like '04MAR2017:00:39:08.000000'dt.

and the result was the same.

Any suggestions would be greatly appreciated!

---

[SoozMorg]

Sorry, this did not work:

38                   select Input(max(modstamp), anydtdtm.)
39                   into   :test
40                   from   my_table
41                   where CNTCT_METH_TX = 'Email';
ERROR: INPUT function requires a character argument.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

[ChrisNZ]

The into syntax uses any associated format.

Just force another format:

put(max(MODSTAMP) ,9. -l) into :max_ts

[SoozMorg]

Sorry, but that didn't work:

38                   select put(max(MODSTAMP) ,9. -l)
39                   into   :test
40                   from   my_table
41                   where CNTCT_METH_TX = 'Email';
WARNING: The format F was not located on the database.  In-database processing will proceed without it.
WARNING: Syntax error or access violation Syntax error: expected something between '(' and the string 'F'.
42                   %put test:      &test;
SYMBOLGEN:  Macro variable TEST resolves to 1.8144E9

[Patrick]

@SoozMorg

I would be using the SQL code you've initially posted to ensure that your SQL executes in-database. Using SAS functions which can't get converted into database functions will cause the SQL to pull the data into SAS for execution.

For dealing with the resulting datetime strings in SAS macro code I'd be going for the approach @Reeza already posted.

If fractional seconds are of importance for what you're doing then I'd be using INPUTN() instead of "..."dt syntax within your macro code. Have a look at the result from below code sample which illustrates the differences.

%macro test(max_ts,new_maxts);
  %put using ""dt syntax;
  %if %sysevalf("&new_maxts"dt >"&max_ts"dt) %then %put RESULT: GT;
  %else %if %sysevalf("&new_maxts"dt = "&max_ts"dt) %then %put RESULT: EQ;
  %else %put RESULT: LT;

  %put using inputn() syntax;
  %if %sysevalf(%sysfunc(inputn("&new_maxts",datetime26.)) > %sysfunc(inputn("&max_ts",datetime26.))) %then %put RESULT: GT;
  %else %if %sysevalf(%sysfunc(inputn("&new_maxts",datetime26.)) = %sysfunc(inputn("&max_ts",datetime26.))) %then %put RESULT: EQ;
  %else %put RESULT: LT;

%mend;

%test(29JUN2017:23:59:52.000000,01JUL2017:23:59:52.000000);
%test(29JUN2017:23:59:52.000000,29JUN2017:23:59:52.000000);

%test(29JUN2017:23:59:52.000001,29JUN2017:23:59:52.000000);
%test(29JUN2017:23:59:52.000010,29JUN2017:23:59:52.000000);
%test(29JUN2017:23:59:52.000100,29JUN2017:23:59:52.000000);
%test(29JUN2017:23:59:52.001000,29JUN2017:23:59:52.000000);

42         %test(29JUN2017:23:59:52.000000,29JUN2017:23:59:52.000000);
using ""dt syntax
RESULT: EQ
using inputn() syntax
RESULT: EQ
43         
44         %test(29JUN2017:23:59:52.000001,29JUN2017:23:59:52.000000);
using ""dt syntax
RESULT: EQ
using inputn() syntax
RESULT: EQ
45         %test(29JUN2017:23:59:52.000010,29JUN2017:23:59:52.000000);
using ""dt syntax
RESULT: EQ
using inputn() syntax
RESULT: LT
46         %test(29JUN2017:23:59:52.000100,29JUN2017:23:59:52.000000);
using ""dt syntax
RESULT: EQ
using inputn() syntax
RESULT: LT
47         %test(29JUN2017:23:59:52.001000,29JUN2017:23:59:52.000000);
using ""dt syntax
RESULT: EQ
using inputn() syntax
RESULT: LT

[Reeza]

%IF %sysevalf("&new_maxts"dt >"&max_ts"dt) %THEN %DO;
  %goto start;
  %END;

That means your variable is numeric. All macro variables are character.

[slchen]

Convert datetime macro variable, in addition,  when compared:

%IF %sysevalf( "&new_maxts"dt > "&max_ts"dt ) %THEN %DO;