topic Re: DATETIME variable stored as a string in SAS Programming

DATETIME variable stored as a string

SoozMorg — Fri, 30 Jun 2017 23:24:12 GMT

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!

Re: DATETIME variable stored as a string

Reeza — Fri, 30 Jun 2017 23:52:32 GMT

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!

Re: DATETIME variable stored as a string

ChrisNZ — Sat, 01 Jul 2017 00:48:30 GMT

The into syntax uses any associated format.

Just force another format:

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

Re: DATETIME variable stored as a string

SoozMorg — Sat, 01 Jul 2017 01:14:02 GMT

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

Re: DATETIME variable stored as a string

SoozMorg — Sat, 01 Jul 2017 01:16:31 GMT

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.

Re: DATETIME variable stored as a string

Reeza — Sat, 01 Jul 2017 01:24:12 GMT

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

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

Re: DATETIME variable stored as a string

slchen — Sat, 01 Jul 2017 01:43:39 GMT

Convert datetime macro variable, in addition, when compared:

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

Re: DATETIME variable stored as a string

Patrick — Sat, 01 Jul 2017 04:23:56 GMT

@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