topic Re: Perform computation (subtraction) on date, one appeared as char while other num in SAS Programming

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

desireatem — Thu, 12 Jan 2023 12:22:37 GMT

Dear Madam?Sir,

Your help is much appreciated.

I have a very large data (marked bold below) that I wish to compute the difference between the two dates below. However, the variable "Arrival_data_time" is a character while LKW is numeric as below:

Arrival_date_time
Char
18
$18.
$18.
Arrival date_time

LKW
Num
8
DATETIME16.
 
LKW

I used the code but the results looks wrong:

data bio;
set bio;
LKWH=Arrival_date_time-LKW;
run;

proc means data=Bio median p25 p75 ;
var LKWH;
run;

OUTPUT

Analysis Variable : LKWH
Median	25th Pctl	75th Pctl
1968891295	1967083516	1970649275

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

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

PaigeMiller — Thu, 12 Jan 2023 12:40:46 GMT

First, you will have to create a numeric variable containing the date/time value from the character variable ARRIVAL_DATE_TIME, then do the subtraction, using a DATA step.

data want;
    set have;
    /* Create numeric date/time variable */
    arrival_date_time_num=input(arrival_date_time,anydtdtm.);
    /* Do subtraction */
    difference=arrival_date_time_num-lkw;
    format difference time.;
run;

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

desireatem — Thu, 12 Jan 2023 14:15:08 GMT

It does not work, the answer is negative. I wish to get the difference in minutes.

data Bio;
Set Bio;
arrival_date_time_num=input(arrival_date_time,anydtdtm.);
difference=arrival_date_time_num-lkw;
format difference time.;
run;

proc means data=Bio median p25 p75 ;
var difference;
run;

Analysis Variable : difference
Median	25th Pctl	75th Pctl
-1810558500	-1810558500	-1810558500

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

PaigeMiller — Thu, 12 Jan 2023 14:39:16 GMT

The numbers for variable DIFFERENCE are in seconds, and I use the proper format, so the values of DIFFERENCE should appear as hours:minutes:seconds. SHOW US a screen capture of (a portion of) the data set BIO so we can see the value of DIFFERENCE for ourselves (not the screen capture from PROC MEANS).

If the numbers are negative, you can change the calculation formula to obtain positive numbers.

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

desireatem — Thu, 12 Jan 2023 15:11:44 GMT

It didnt work:

401 data Bio;
402 Set Bio;
403 arrival_date_time_num=input(arrival_date_time,anydtdtm.);
404 difference=arrival_date_time_num-lkw;
405 format difference time.;
406 run;

NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line):(Column).
10 at 404:36
NOTE: There were 11 observations read from the data set WORK.BIO.
NOTE: The data set WORK.BIO has 11 observations and 58 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

proc print data=bio;
var difference;
run;

Obs	difference
1	.
2	.
3	.
4	.
5	.
6	.
7	.
8	.
9	.
10	.
11	-502932

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

PaigeMiller — Thu, 12 Jan 2023 15:16:41 GMT

Okay, show us all the relevant columns of data set BIO (this would be variable ARRIVAL_DATE_TIME, LKW and ARRIVAL_DATE_TIME_NUM). Something is not as you described it, that is why you are getting missing values.

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

desireatem — Thu, 12 Jan 2023 16:09:29 GMT

The SAS System

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

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

PaigeMiller — Thu, 12 Jan 2023 16:11:53 GMT

This does not use the code that I provided, otherwise the DIFFERENCE would appear as hour:minute:seconds.

Your data is not as you represent it, ARRIVAL_DATE_TIME_NUM is missing for most rows, and you need to also show us the data including ARRIVAL_DATE_TIME

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

ballardw — Thu, 12 Jan 2023 16:18:08 GMT

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.

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

Tom — Thu, 12 Jan 2023 16:20:30 GMT

You are getting a negative number because 1965 is much earlier than 2022.

1908  data _null_;
1909    dt=164999100;
1910    put dt comma17. dt datetime19.;
1911    dt='08AUG22:06:00:00'dt;
1912    put dt comma17. dt datetime19.;
1913  run;

      164,999,100 24MAR1965:17:05:00
    1,975,557,600 08AUG2022:06:00:00

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

Kurt_Bremser — Thu, 12 Jan 2023 16:29:31 GMT

You will need to do the conversion in multiple steps:

arrival_date_time_num = dhms(input(scan(arrival_date_time,1),mmddyy10.),0,0,input(scan(arrival_date_time,2),time5.));

because somebody chose a completely non-standard format for the datetime value. YYYY-MM-DD HH:MM works with the E8601DT informat, and the "ANY" formats are dependent on your locale setting (MDY dates are only used on the American continent, everybody else uses DMY or (best) YMD).

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

desireatem — Thu, 12 Jan 2023 16:42:22 GMT

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.

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

Tom — Thu, 12 Jan 2023 16:48:10 GMT

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

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

desireatem — Thu, 12 Jan 2023 16:48:10 GMT

Thank you, I tried but it did not work:

data Bio;
Set Bio;
*arrival_date_time_num=input(arrival_date_time,anydtdtm.);
arrival_date_time_num = dhms(input(scan(arrival_date_time,1),mmddyy10.),0,0,input(scan(arrival_date_time,2),time5.));
difference=arrival_date_time_num-lkw;
format difference time.;
run;

424 data Bio;
425 Set Bio;
426 *arrival_date_time_num=input(arrival_date_time,anydtdtm.);
427 arrival_date_time_num =
427! dhms(input(scan(arrival_date_time,1),mmddyy10.),0,0,input(scan(arrival_date_time,2),time5.));
428 difference=arrival_date_time_num-lkw;
429 format difference time.;
430 run;

NOTE: Invalid argument to function INPUT at line 427 column 30.
NOTE: Invalid argument to function INPUT at line 427 column 77.

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

desireatem — Thu, 12 Jan 2023 16:50:53 GMT

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

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

desireatem — Thu, 12 Jan 2023 16:53:32 GMT

A good portion of the data comes out as missing only one convertion is done from char to num

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

Kurt_Bremser — Thu, 12 Jan 2023 16:55:50 GMT

We need to explicitly set the delimiter for SCAN to the blank:

data have;
input arrival_date_time $20.;
datalines;
3/23/22 9:51
4/28/2022 17:39
5/3/2022 11:55
5/9/2022 21:39
5/15/2022 12:47
6/1/2022 6:39
6/8/2022 21:44
6/13/2022 10:33
6/27/2022 19:30
7/18/22 10:37
8/8/2022 15:09
;

data want;
set have;
arrival_date_time_num = dhms(input(scan(arrival_date_time,1," "),mmddyy10.),0,0,input(scan(arrival_date_time,2," "),time5.));
format arrival_date_time_num e8601dt19.;
run;

proc print data=want noobs;
var arrival_date_time_num;
run;

Result:

arrival_date_time_num
2022-03-23T09:51:00
2022-04-28T17:39:00
2022-05-03T11:55:00
2022-05-09T21:39:00
2022-05-15T12:47:00
2022-06-01T06:39:00
2022-06-08T21:44:00
2022-06-13T10:33:00
2022-06-27T19:30:00
2022-07-18T10:37:00
2022-08-08T15:09:00

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

PaigeMiller — Thu, 12 Jan 2023 17:05:30 GMT

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;

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

desireatem — Thu, 12 Jan 2023 17:05:29 GMT

data Bio1;
Set Bio;
*arrival_date_time_num=input(arrival_date_time,anydtdtm.);
arrival_date_time_num = dhms(input(scan(arrival_date_time,1," "),mmddyy10.),0,0,input(scan(arrival_date_time,2," "),time5.));
format arrival_date_time_num e8601dt19.;
run;

proc print data=bio1;
var arrival_date_time_num ;
run;

The SAS System

Obs	arrival_date_time_num
1	.
2	.
3	.
4	.
5	.
6	.
7	.
8	.
9	.
10	.
11	.

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

Tom — Thu, 12 Jan 2023 17:08:47 GMT

It does not help to show the results without showing the inputs.