BookmarkSubscribeRSS Feed
Sandeep77
Lapis Lazuli | Level 10

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
6 REPLIES 6
Amir
Ammonite | Level 13

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
Lapis Lazuli | Level 10

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

Tom
Super User Tom
Super User

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
SAS Super FREQ
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
Super User

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.

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

From SAS Users blog
Want more? Visit our blog for more articles like these.
5 Steps to Your First Analytics Project Using SAS

For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 550 views
  • 2 likes
  • 5 in conversation