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
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;
@Amir ccreated time shows the time and dcreateddate shows the date as below:
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
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.
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.
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.