topic date format in SAS Procedures

date format

Tal — Tue, 29 May 2018 17:13:37 GMT

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

Re: date format

ballardw — Tue, 29 May 2018 20:03:18 GMT

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

Re: date format

Tal — Wed, 30 May 2018 15:34:45 GMT

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

Re: date format

Reeza — Wed, 30 May 2018 16:22:09 GMT

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

Re: date format

Tal — Wed, 30 May 2018 17:27:49 GMT

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

Re: date format

Reeza — Wed, 30 May 2018 18:06:25 GMT

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

Re: date format

Tal — Wed, 30 May 2018 19:16:43 GMT

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

Re: date format

Reeza — Wed, 30 May 2018 20:03:34 GMT

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

Re: date format

Tal — Wed, 30 May 2018 20:11:02 GMT

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

Re: date format

MadhuKorni — Thu, 31 May 2018 09:49:11 GMT

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;

Re: date format

Tal — Thu, 31 May 2018 16:05:11 GMT

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