topic Time stamp format help in SAS Programming

Time stamp format help

forumsguy — Thu, 12 Sep 2013 11:01:14 GMT

Hi All,

I have a Terdata table which has field created_on with timestamp(0) format of Teradata.

I need to select rows from table using my DI Job.. however I am unable to find equivalent SAS format.

E.G.

My entry in TD table is sel * from tab where created_on<='2013-09-12 12:00:00' ---------> This works

In DI JOb's precode , I am writing

%let bus_time=%sysfunc(datetime(),DATETIME23.3);

%let bus_time_f=%unquote(%bquote(')&bus_time%bquote('));

and then selecting in my SQL join as

sel * from tab where created_on<=&bus_time_f--------> This doesnt work.

bus_time_f resolves to '12SEP2013:20:54:14.087' ......

Need some assistance in getting correct format.

Re: Time stamp format help

Scott_Mitchell — Thu, 12 Sep 2013 11:22:28 GMT

Hi ForumsGuy,

I have no experience with the DB to which you refer, so I am not sure if the following will help, but you could use a picture format.

PROC FORMAT ;

PICTURE TERDATE LOW-HIGH = '%Y-%0m-%0d:%0H:%0M:%0S'

(DATATYPE = DATETIME) ;

RUN ;

%LET BUS_TIME=%SYSFUNC(INPUTN(%SYSFUNC(DATETIME()),TERDATE.));

%put &BUS_TIME.;

Re: Time stamp format help

dhana — Thu, 12 Sep 2013 12:19:11 GMT

You can try this also ...!

%LET VAL = %SUBSTR(%SYSFUNC(DATETIME(),DATETIME23),1,9);
%LET BUS_DATE = %SYSFUNC(INPUTN(&VAL,DATE9.),YYMMDD10.);
%LET BUS_TIME=%SUBSTR(%SYSFUNC(DATETIME(),DATETIME23),11,8);
%LET BUS_TIME_F=%UNQUOTE(%BQUOTE(')&BUS_DATE:&BUS_TIME%BQUOTE('));

%PUT #### &VAL #### ;
%PUT #### &BUS_DATE #### ;
%PUT #### &BUS_TIME #### ;
%PUT #### &BUS_TIME_F #### ;

Use the variable BUS_TIME_F in your code.

Thanks

Dhana

Re: Time stamp format help

sandyming — Thu, 12 Sep 2013 13:51:14 GMT

What is the format of CREATED_ON variable?

Re: Time stamp format help

forumsguy — Fri, 13 Sep 2013 02:10:32 GMT

Amazing.. Both of these worked.. Thanks Dhana and Scott

Re: Time stamp format help

Tom — Fri, 13 Sep 2013 03:02:30 GMT

You can tell Teradata how to interpret the string you have given it.

In SAS generate the string in normal DATETIME format.

%let bus_time=%sysfunc(datetime(),DATETIME19.);

%let bus_time_f=%unquote(%bquote(')&bus_time%bquote('));

In Teradata tell what format to use to read that format.

sel *

from tab

where created_on <= &bus_time_f (timestamp, format 'DDMMMYYYY:HH:MI:SS')

Re: Time stamp format help

Peter_C — Sat, 14 Sep 2013 06:56:06 GMT

If the context where the code will run is "explicit pass through" then Tom's use if the Teradata format looks most effective !

If "implicit pass through" then there should be no need for special arrangements as SAS automatically understands and handles the datatypes like datetime constants.

Of course it has been some time since I programmed queries and user transforms in SAS DI.