topic Re: How to achieve DB2 timestamp format through SAS? in SAS Programming

How to achieve DB2 timestamp format through SAS?

Krisan — Thu, 01 Nov 2012 16:18:03 GMT

Hi every1,

I want to load the current system date and time from SAS to a field (specified in Timestamp format) in DB2. I tried using datetime format for that variable in SAS and using proc append to load data to db2 table. But i am getting format mismatch error. I cannot change the datatype of the field in DB2 as it is already existing table in PROD. Does anyone have an idea of how to achieve this?

Regards,

Krisan

Re: How to achieve DB2 timestamp format through SAS?

shivas — Thu, 01 Nov 2012 16:31:14 GMT

Hi,

Just try these options and check..

SASDATEFMT or DBSASTYPE...

Thanks,

Shiva

Re: How to achieve DB2 timestamp format through SAS?

Linlin — Thu, 01 Nov 2012 17:40:57 GMT

Hi Shiva,

Have not seen you on the forum for a while. Welcome back!

Re: How to achieve DB2 timestamp format through SAS?

ArtC — Thu, 01 Nov 2012 18:34:23 GMT

You might also consider building a picture format. For a datetime value you could use something like the following:

proc format;

picture dbdate

other = '%Y-%0m-%0d:%0H:%0M:%0S' (datatype=datetime);

run;

The %Y, %m, etc. are directives that can be used with picture formats. There are over a dozen of them and they can be very helpful when working with date, time, and datetime values.

Re: How to achieve DB2 timestamp format through SAS?

jthy — Thu, 08 May 2014 09:38:01 GMT

Old fashioned string handling?

data _null_;

myDttm = datetime();

db2ts = cats(put(datepart(myDttm), yymmdd10.), "-", tranwrd(put(timepart(myDttm), tod9.), ":", "."), ".", substr(put(timepart(myDttm), mmss15.6), 10, 6));

put db2ts;

run;

2014-05-08-11.30.12.012000

Re: How to achieve DB2 timestamp format through SAS?

jakarman — Thu, 08 May 2014 12:26:10 GMT

As you are appending the data the existing structure and the dataset you are building should be the same.

Your error-message is telling you something has failed with that. Try to find the reason by comparing those structures.