CODEWITHSUNDEEP
sassas-macro
samrat
samrat

Reputation: 43

convert timestamp into SAS macro variable

how to convert the timestamp into numeric value without microseconds value getting rounded up and store it in a macro variable

data temp;

ts='2022-05-18:10:11:12.796429';

ts1 = input(ts,anydtdtm26.);

putlog 'ts1-->' ts1;

call symput('new_ts1',ts1);

run;

%put new_ts1: &new_ts1;

ts1-->1968487872.8

new_ts1: 1968487872.8

Is it possible to store the actual value instead of the round value?

Upvotes: 0

Views: 636

Answers (1)

Tom
Tom

Reputation: 51566

You keep asking this same question.

Correction

NO. It is impossible to store datetime (aka timestamp) values to the micro second in SAS. The values will exceed the limits of precision for storing decimal values with fractions as floating point numbers.

You can store that large a number as an integer number of microseconds. But not as a number of seconds with microseconds represented as fractions of a second.

Example:

836  data test;
837    second = '18MAY2022:10:11:12'dt + 0.796429;
838    micros = '18MAY2022:10:11:12'dt*1E6 + 796429;
839    put second= comma30.6 / micros = comma30. ;
840  run;

second=1,968,487,872.796420
micros=1,968,487,872,796,429

Notice how the microseconds place digit is lost when stored in number of seconds but when stored as an integer count of microseconds instead the value is still there.

NO. You cannot use the ANYDTDTM informat to convert that specific string into a datetime value with microsecond precision. Use a different method. And store the microseconds in a separate variable from the datetime.

date test;
  ts='2022-05-18:10:11:12.796429';
  dt=dhms(input(ts,yymmdd10.),0,0,input(substr(ts,12),time8.));
  microseconds = input(scan(ts,-1,'.'),6.);
  format dt datetime19.  microseconds z6. ;
run;

enter image description here

NO. You cannot use implicit passthru to DB2 to move datetime values and retain the precision to the micro second.

To move the values from DB2 to SAS convert them to a string in DB2 and move the string. You can then use a method like above to convert into an actual datetime value if you need.

To move the values back to DB2 you reverse the process and move the value as a string and use DB2 code to convert it to a DB2 timestamp value. Or generate code to run in DB2 via explicit pass thru (perhaps by building the text of a DB2 timestamp literal into a macro variable) that will transfer the value.

So perhaps something like:

%let myts=%put<what ever the heck syntax works for timestamp value in DB2>;
proc sql;
 connect to db2 ... ;
 execute by db2 
   (insert into myschema.mytable (myts)
    values ( &myts. )
   )
  ;

You could try to build such a string be concatenating the date, time and microsecond pieces. Make sure the use the Z format when converting the microseconds into a string so that values less than a tenth of a second will have the needed leading zeros.

So if you want to generate a string like '2022-05-18:10:11:12.796429' you might do this:

data _null_;
  set test;
  call symputx('myts',cats(
     "'",put(datepart(dt),yymmddd10.)
    ,":',put(dt,tod8.)
    ,".",put(milliseconds,z6.)
    ,"'"));
run;

Upvotes: 1

Related Questions