Help using Base SAS procedures

date format

Reply
Super Contributor
Super Contributor
Posts: 464

date format

 

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

Super User
Posts: 13,584

Re: date format


@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*/

 

Super Contributor
Super Contributor
Posts: 464

Re: date format

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

Super User
Posts: 23,787

Re: date format


@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.

Super Contributor
Super Contributor
Posts: 464

Re: date format

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

Super User
Posts: 23,787

Re: date format

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


 

Super Contributor
Super Contributor
Posts: 464

Re: date format

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" ?

Super User
Posts: 23,787

Re: date 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:

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" ?


 

Super Contributor
Super Contributor
Posts: 464

Re: date format

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 ":"

Frequent Contributor
Posts: 77

Re: date format

[ Edited ]

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;

Super Contributor
Super Contributor
Posts: 464

Re: date format

Posted in reply to MadhuKorni

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

Ask a Question
Discussion stats
  • 10 replies
  • 194 views
  • 4 likes
  • 4 in conversation