Perform computation (subtraction) on date, one appeared as char while other num

[PaigeMiller]

OK. Full stop right here.

We need your exact data for these 11 rows, provided following these instructions — and not provided via any other method.

[Kurt_Bremser]

Then your data is VERY OBVIOUSLY not what you told us all the time. My code WORKS.

Post example data in a data step with datalines. I will not waste further time until you do this.

[ballardw]

Since I see this code in your example:

data bio;
set bio;

Then you may think you have stuff that is no longer there. That code construct complete replaces the source data set. So a logic error at any of the steps may have drastically changed your data. Have you checked with a version of the original data (i.e. re-read if needed) that the variable values are as you state?

Note: one suspects a reliance on Proc Import or an import wizard to get a character value like that for a datetime.

And it may be time to remind that any operation of minus or plus, - and +, will result in a missing value if any of the arguments are missing. You show at least one record where Arrival_date_time is missing, so any operation involving that is likely to result in a missing value.

[desireatem]

Thank you so much. I have a problem with the data, so reduce the data to this: 

Arrival date_time	LKW
3/23/22 9:51	3/23/2022 9:00
4/28/2022 17:39	4/28/2022 9:00
5/3/2022 11:55	5/2/2022 16:30
5/9/2022 21:39	5/9/2022 17:20
5/15/2022 12:47	5/15/2022 12:20
6/1/2022 6:39	5/31/2022 17:00
6/8/2022 21:44	6/8/2022 21:00
6/13/2022 10:33	6/12/2022 23:00
.	6/27/2022 19:30
7/18/22 10:37	7/17/2022 23:00
8/8/2022 15:09	8/8/2022 6:00

Based on contents: 

Arrival_date_time

Char

18

$18.

$18.

Arrival date_time

29	LKW	Num	8	DATETIME16.		LKW

So based on the data only one cell in the  subtraction should be "." but  based on the results everything but one is ".":

Obs	arrival_date_time_num	LKW	difference
1	.	23MAR22:09:00:00	.
2	.	28APR22:09:00:00	.
3	.	02MAY22:16:30:00	.
4	.	09MAY22:17:20:00	.
5	.	15MAY22:12:20:00	.
6	.	31MAY22:17:00:00	.
7	.	08JUN22:21:00:00	.
8	.	12JUN22:23:00:00	.
9	.	27JUN22:19:30:00	.
10	.	17JUL22:23:00:00	.
11	164999100	08AUG22:06:00:00	-502932

So what this is telling us is that, the code,:"

data Bio;
Set Bio;
arrival_date_time_num=input(arrival_date_time,anydtdtm.);
difference=arrival_date_time_num-lkw;
format difference time.;
run; "   is wrong because it converted all the values to missing but one.

i

[Tom]

The data you posted does not match the variable information you posted.

You show LKW as using the DATETIME16 format, but the string you posted are NOT in that style. (Note do not use a width less than 19 with the DATETIME format, otherwise it does not print the century.)

[desireatem]

data bb;
set bio;
keep arrival_date_time lkw;
run;

proc contents data=bb;
run;

Alphabetic List of Variables and Attributes
#	Variable	Type	Len	Format	Informat	Label
1	Arrival_date_time	Char	18	$18.	$18.	Arrival date_time
2	LKW	Num	8	DATETIME16.		LKW

[PaigeMiller · Accepted solution]

This code seems to work for me. The first data step reproduces your data. The second data step does the differencing. If the second data step does not produce the same results for you that I am getting, then we still do not have a good description of your data.

data have;
input Arrival_date_time : $16. lkw :anydtdtm.;
infile cards delimiter='|';
format lkw datetime16.;
cards;
3/23/22 9:51    |	3/23/2022 9:00
4/28/2022 17:39 |   4/28/2022 9:00
5/3/2022 11:55  |   5/2/2022 16:30
5/9/2022 21:39  |   5/9/2022 17:20
5/15/2022 12:47 |   5/15/2022 12:20
6/1/2022 6:39   |   5/31/2022 17:00
6/8/2022 21:44  |   6/8/2022 21:00
6/13/2022 10:33 |   6/12/2022 23:00
.               |   6/27/2022 19:30
7/18/22 10:37   |   7/17/2022 23:00
8/8/2022 15:09  |   8/8/2022 6:00
;
data want;
    set have;
    arrival_date_time_Num=input(arrival_date_time,anydtdtm.);
    difference = arrival_date_time_num - lkw;
    format arrival_date_time_num datetime16. difference time.;;
run;