How to remove seconds from datetime (not round function)

[AMFR]

Hi all,

I have a datetime variable in two files that I need to merge, issue is datetime variable in both files are different in seconds for few observations. For example in one file datetime is 06/11/2018 17:48:29 and in other file it is 17:48:30

I just want to remove seconds from datetime so I can merge both files. how can I do that?

I cannot round it because it is not working out.

Let me know, thanks in advance.

[RW9]

proc sql;
  create table want as
  select...
  from   havea a
  left join haveb b
  on     date(a.dtvar)=date(b.dtvar)
  and    time(a.dtvar)=time(b.dtvar);
quit;

You can use the time and date parts, might need to put time into time5 format or something.  You could also put both datetimes into text e8601dt replace the seconds part and read back in as datetime.

[AMFR]

Many thanks!

[Astounding]

You could create your own variable for matching purposes:

matchvar = int(datetimevar/60);

The merge (or join if using SQL) on matchvar.

Don't print matchvar.  It's neither a date nor a datetime.  It's the number of minutes.

In practice, you may need to experiment with using ROUND instead of INT.  It's possible you need to match based on the closest minute rather than removing the seconds.

[AMFR]

Thank you!!

[Tom · Accepted solution]

Perhaps you just want to use the MOD() function to remove the seconds?

data _null_;
 dt1='11JUN2018:17:48:29'dt;
 dt2='11JUN2018:17:48:30'dt;
 rdt1 = dt1 - mod(dt1,60);
 rdt2 = dt2 - mod(dt2,60);
 test1= dt1=dt2;
 test2= rdt1=rdt2 ;
 format dt: rdt: datetime20. ;
 put (_all_) (=/);
run;

Or better still test that the difference is less than a minute?

proc sql;
create table want as
  select * 
  from ds1 A, ds2 B
  where abs(A.datetime - B.datetime) <= 60 
;
quit;

[AMFR]

Thank you very much. I found MOD function to remove seconds very helpful.

Thanks again!