Function DHMS requires a numeric expression

[Sandeep77]

Hi all,

I am using DHMS function to get the date time values but I am getting some error. Can you please let me know what is wrong in the code? This is the first code that I am using in the program so I don't have a sample dataset. I am pulling the information from debtsupport table. Thanks

proc sql;
create table ctrigger_id as 
	select distinct
			debt_code,
			ctrigger,
			dcreateddate,
			ccreatedtime,
			dhms(datepart(dcreateddate),0,0,ccreatedtime) as Trigger_Date format datetime22.3,
			ccreateduser,
			datepart(dcreateddate) as dcreateddate_DT format date9.
		from p2scflow.debtsupport
			where ctrigger like '%15%'
				order by dcreateddate;
quit;
Error log:
30         proc sql;
31         create table ctrigger_id as
32         	select distinct
33         			debt_code,
34         			ctrigger,
35         			dcreateddate,
36         			ccreatedtime,
37         			dhms(datepart(dcreateddate),0,0,ccreatedtime) as Trigger_Date format datetime22.3,
38         			ccreateduser,
39         			datepart(dcreateddate) as dcreateddate_DT format date9.
40         		from p2scflow.debtsupport
41         			where ctrigger like '%15%'
42         				order by dcreateddate;
ERROR: Function DHMS requires a numeric expression as argument 4.
ERROR: Character expression requires a character format.
ERROR: Character expression requires a character format.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
43         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.09 seconds
      user cpu time       0.00 seconds
      system cpu time     0.03 seconds
      memory              5786.03k
      OS Memory           29532.00k
      Timestamp           06/26/2023 09:17:45 AM
      Step Count                        3  Switch Count  13

[Amir]

Hi @Sandeep77,

If you try executing the below, what does it show for ccreatedtime?

Also, is there maybe a variable (column) named createdtime, without the extra 'c' at the beginning which might indicate a character version of a numeric variable?

proc contents data = p2scflow.debtsupport;
run;

Kind regards,

Amir.

[Sandeep77]

@Amir  ccreated time shows the time and dcreateddate shows the date as below:

dcreateddate	ccreatedtime
02MAY2019:00:00:00.000	10:14:31

dcreateddate is numeric and format is Datetime22.3

ccreatedtime is Character value.

What can I do to correct the error?

[Kurt_Bremser]

Convert the time value with

input(ccreatedtime,time8.)

to the numeric value required by the DHMS function.

[Tom]

So the first variable is already in seconds (number of seconds since 1960) so use the INPUT() function to convert the second variable to the number of seconds since midnight and add them together.

dcreateddate + input(ccreatedtime,time8.)

[JosvanderVelden]

Please read the documentation on dhms. The 4th argument needs to be numeric. For examples you can also read: https://support.sas.com/resources/papers/proceedings20/4801-2020.pdf

[Kurt_Bremser]

Maxim 3: Know Your Data.

Inspect your dataset (e.g. with PROC CONTENTS) to see the attributes of ccreatedtime; if there is no other variable in the dataset which stores this value as a true SAS time, you need to convert from character to a numeric value with the INPUT function. Use an informat which fits the notation of the time.