BookmarkSubscribeRSS Feed
SoozMorg
Calcite | Level 5

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!

7 REPLIES 7
Reeza
Super User

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
Calcite | Level 5

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
Tourmaline | Level 20

The into syntax uses any associated format.

 

Just force another format:

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

 

SoozMorg
Calcite | Level 5

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
Opal | Level 21

@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
Super User
%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
Lapis Lazuli | Level 10

Convert datetime macro variable, in addition,  when compared:

 

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 2301 views
  • 2 likes
  • 5 in conversation