BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
forumsguy
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
dhana
Fluorite | Level 6

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

View solution in original post

6 REPLIES 6
Scott_Mitchell
Quartz | Level 8

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.;

dhana
Fluorite | Level 6

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

sandyming
Calcite | Level 5

What is the format of CREATED_ON variable?

forumsguy
Fluorite | Level 6

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

Peter_C
Rhodochrosite | Level 12

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.

Tom
Super User Tom
Super User

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')

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 6 replies
  • 3474 views
  • 3 likes
  • 6 in conversation