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
@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*/
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
@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.
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
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
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" ?
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" ?
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 ":"
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;
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
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 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.