date format

[Tal]

Hello  guys,

I have 2 tables that I need to match on date field but the format in both tables is a  bit different

1st table :   2018-04-06 11:59:55

2nd table:   2018-4-6 11:59:55:377

This field is a string  in both tables

How  can I  convert 2018-4-6 11:59:55:377   to 2018-04-06 11:59:55. any ideas please?

Thx

[ballardw]

---

@Tal wrote:

Hello  guys,

I have 2 tables that I need to match on date field but the format in both tables is a  bit different

1st table :   2018-04-06 11:59:55

2nd table:   2018-4-6 11:59:55:377

This field is a string  in both tables

How  can I  convert 2018-4-6 11:59:55:377   to 2018-04-06 11:59:55. any ideas please?

Thx

---

After getting SAS datetime valued variables if you need to match these variables for some reason then one way would be to round or truncate the values which ever makes more sense for your projectd. Since time and datetime values are numbers of seconds if you round or truncate to integer values then you have a value in whole seconds.

So for that explicit value either of these would work:

datetime= round(datetime,1); /* nearest second*/

datetime= floor (datetime);  /*largest integer less than or equal to the datetime value*/

[Tal]

thanks  for your  response ballardw..

the problem is that I cannot  convert this date 2018-4-6 11:59:55:377 to a  SAS date value. Looks like SAS does not recognize it

if it was  2018-4-6 11:59:55.377  would have been fine   but  2018-4-6 11:59:55:377  no good.

Any way SAS can convert the second ":" into "."? or remove the second ":" and everything  after it?

I tried with  substr but the length of  the date is not the same

[Reeza]

---

@Tal wrote:

thanks  for your  response ballardw..

the problem is that I cannot  convert this date 2018-4-6 11:59:55:377 to a  SAS date value. Looks like SAS does not recognize it

if it was  2018-4-6 11:59:55.377  would have been fine   but  2018-4-6 11:59:55:377  no good.

Any way SAS can convert the second ":" into "."? or remove the second ":" and everything  after it?

I tried with  substr but the length of  the date is not the same

---

Show your code, SAS reads it fine for me.

[Tal]

data test;

y='2018-5-9 13:18:31:377';

dd=input(y,anydtdtm32.);run;

SAS displays dd as missing

when running  this

data test;

y='2018-5-9 13:18:31.377';

dd=input(y,anydtdtm32.);run;

SAS displays dd=1841491111.4

[Reeza]

The second one is correct, you just didn't apply a format. 

You can also just take the date part using SCAN() and then convert that to a date. You could do that directly in the JOIN as well. 

---

@Tal wrote:

data test;

y='2018-5-9 13:18:31:377';

dd=input(y,anydtdtm32.);run;

SAS displays dd as missing

when running  this

data test;

y='2018-5-9 13:18:31.377';

dd=input(y,anydtdtm32.);run;

SAS displays dd=1841491111.4

---

[Tal]

right , the second one is correct  but  what I am saying is the  date in one of the  tables has  value of this  type :2018-5-9 13:18:31:377 not 2018-5-9 13:18:31.377 and  seems like the 1st one is not  a valid date format so no way I can  convert  it

can  SAS remove  the  ":377" ?

[Reeza]

You can also just take the date part using SCAN() and then convert that to a date. You could do that directly in the JOIN as well. 

---

@Tal wrote:

right , the second one is correct  but  what I am saying is the  date in one of the  tables has  value of this  type :2018-5-9 13:18:31:377 not 2018-5-9 13:18:31.377 and  seems like the 1st one is not  a valid date format so no way I can  convert  it

can  SAS remove  the  ":377" ?

---

[Tal]

there will be more records on a  given date  in both tables so  I will need to keep the hh mm and ss  from the time part to be able to match correctly

I was  wondering  if  the  last ":" can be replaced  with "."  or truncate  the  string,   remove everything after the  last ":"  including the ":"

[MadhuKorni]

data test;
y='2018-5-9 13:18:31:377';
if count(y,':') = 3 then do;
ind = find(y,':',-1);
rep = trim(substr(y,1,ind-1))||'.'||substr(y,ind+1); *For Replacing;
trun = substr(y,1,ind-1); *For Truncating;
end;
run;

[Tal]

sorry, I don't see  how  your code  replaces ":"  with "."  but  I found  a  way how to remove  the last part of the date and  that would be good enough for me

y='2018-5-9 13:18:31:377';

n = length(y) - length(scan(y, -1, ':') );

y=substr(y,1,n-1);

Thank you guys for  your help