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.
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;
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.
Many thanks!
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.
Thank you!!
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;
Thank you very much. I found MOD function to remove seconds very helpful.
Thanks again!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.