topic Re: How to remove seconds from datetime (not round function) in SAS Programming

How to remove seconds from datetime (not round function)

AMFR — Tue, 24 Jul 2018 15:06:48 GMT

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.

Re: How to remove seconds from datetime (not round function)

RW9 — Tue, 24 Jul 2018 15:15:02 GMT

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.

Re: How to remove seconds from datetime (not round function)

Astounding — Tue, 24 Jul 2018 15:35:20 GMT

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.

Re: How to remove seconds from datetime (not round function)

Tom — Tue, 24 Jul 2018 16:53:07 GMT

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;

Re: How to remove seconds from datetime (not round function)

AMFR — Tue, 24 Jul 2018 17:30:27 GMT

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

Thanks again!

Re: How to remove seconds from datetime (not round function)

AMFR — Tue, 24 Jul 2018 17:30:56 GMT

Thank you!!

Re: How to remove seconds from datetime (not round function)

AMFR — Tue, 24 Jul 2018 17:31:34 GMT

Many thanks!