topic Re: Function DHMS requires a numeric expression in New SAS User

Function DHMS requires a numeric expression

Sandeep77 — Mon, 26 Jun 2023 08:44:52 GMT

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

Re: Function DHMS requires a numeric expression

Amir — Mon, 26 Jun 2023 09:02:38 GMT

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.

Re: Function DHMS requires a numeric expression

JosvanderVelden — Mon, 26 Jun 2023 09:07:48 GMT

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

Re: Function DHMS requires a numeric expression

Kurt_Bremser — Mon, 26 Jun 2023 10:33:27 GMT

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.

Re: Function DHMS requires a numeric expression

Sandeep77 — Mon, 26 Jun 2023 10:43:47 GMT

@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?

Re: Function DHMS requires a numeric expression

Kurt_Bremser — Mon, 26 Jun 2023 11:06:29 GMT

Convert the time value with

input(ccreatedtime,time8.)

to the numeric value required by the DHMS function.

Re: Function DHMS requires a numeric expression

Tom — Mon, 26 Jun 2023 12:03:31 GMT

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