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.
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.
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.
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
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.)
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 |
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;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.