topic Re: Need to change DB2 timestamp to numeric SAS timestamp in New SAS User

Need to change DB2 timestamp to numeric SAS timestamp

SBKH — Wed, 06 Sep 2023 10:19:05 GMT

In input file have data like this -
2023-09-05-06.15.16.130035
I want to check if this timestamp is less than current timestamp - 14 hours
I am able to get the 14 hours back numeric current timestamp with this code
Data_null_;
File statrep;
/*Get the current timestamp*/;
Current_time = DATETIME();
/* subtract 14 hours (50400 seconds) */
14_hours_ago = current_time -50400;
I need my input file timestamp to change to numeric format so that I can compare these two dates if it's less or greter for my further process.
Or if there any other way to compare dates please let me know

Re: Need to change DB2 timestamp to numeric SAS timestamp

LinusH — Wed, 06 Sep 2023 10:37:29 GMT

So it's DB2 but you are reading it from a file?

If you access DB2 directly the SAS/ACCESS libname engine maps DB2 timestamps to SAS datetime auotmatically.

if you can't find any suitable existing informat, you can create your own:

SAS Help Center: Syntax: PROC FORMAT PICTURE Statement

Re: Need to change DB2 timestamp to numeric SAS timestamp

Patrick — Wed, 06 Sep 2023 11:07:24 GMT

You will need to convert this source string to a pattern for which there is a SAS infomat. Let me know if below conversion is working for you.

data demo;
  input have_string $25.;
  have_string=prxchange('s/^(\d+-\d+-\d+).(\d+).(\d+).(\d+.*)$/$1T$2:$3:$4/oi',1,strip(have_string));
  have_dttm=input(strip(have_string),e8601dt26.6);

  /*Get the current timestamp*/;
  Current_time = DATETIME();

  /* subtract 14 hours (50400 seconds) */
  _14_hours_ago = intnx('dthour',current_time,-14,'s');
  datalines;
2023-09-05-06.15.16.130035
2023-09-05T06:15:16.130035
;

proc print data=demo;
  format have_dttm Current_time _14_hours_ago datetime25.6;
run;

Re: Need to change DB2 timestamp to numeric SAS timestamp

Patrick — Wed, 06 Sep 2023 11:12:47 GMT

@LinusH wrote:

So it's DB2 but you are reading it from a file?

If you access DB2 directly the SAS/ACCESS libname engine maps DB2 timestamps to SAS datetime auotmatically.

if you can't find any suitable existing informat, you can create your own:

SAS Help Center: Syntax: PROC FORMAT PICTURE Statement

@LinusH

Agree with your first point. ...plus rounding the floating point number to the 6th decimal.

If reading a string is required: I don't believe a picture FORMAT will help as it's about the need for an INformat.