BookmarkSubscribeRSS Feed
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

 

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

10 REPLIES 10
ballardw
Super User

@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
Pyrite | Level 9 Tal
Pyrite | Level 9

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
Super User

@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
Pyrite | Level 9 Tal
Pyrite | Level 9

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
Super User

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
Pyrite | Level 9 Tal
Pyrite | Level 9

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
Super User

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
Pyrite | Level 9 Tal
Pyrite | Level 9

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
Quartz | Level 8

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
Pyrite | Level 9 Tal
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1165 views
  • 4 likes
  • 4 in conversation