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
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.
@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?
Convert the time value with
input(ccreatedtime,time8.)
to the numeric value required by the DHMS function.
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.)
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.
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 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.