Dear all,
I have a table (SAS EG) with date values (type date) and Time values (type String), and want to create dateTime-values from that.
Date example:
00012_Begindatum deze episode | 00013_Begintijd deze episode | BeginTime | Hours_Numeric | BeginDateTime |
3-11-2016 | 9:48 | 9:48 | 9 | . |
2-5-2014 | 10:45 | 10:45 | 10 | . |
30-9-2014 | 14:35 | 14:35 | 14 | . |
30-9-2014 | 15:00 | 15:00 | 15 | . |
1-10-2014 | 17:25 | 17:25 | 17 | . |
I used the following expression to create the (SAS EG) "Calculated Field":
DHMS(t1.v00012,inputn(SUBSTR(t1.v00013, 1, 2),'2.'),inputn(SUBSTR(t1.v00013, 3, 2),'2.'),0 )
but get "missing values". The Log file shows this:
24 PROC SQL;
25 CREATE TABLE WORK.'Argus with duration'n AS
26 SELECT t1.v00012,
27 t1.v00013,
28 /* BeginTime */
29 (input(t1.v00013,time5.)) FORMAT=HHMM5. LABEL="BeginTime" AS BeginTime,
30 /* Hours_Numeric */
31 (INPUTN(SUBSTR(t1.v00013, 1, 2),'2.')) AS Hours_Numeric,
32 /* BeginDateTime */
33 (DHMS(t1.v00012,inputn(SUBSTR(t1.v00013, 1, 2),'2.'),inputn(SUBSTR(t1.v00013, 3, 2),'2.'),0 ))
34 FORMAT=DATETIME13. LABEL="BeginDateTime" AS BeginDateTime
35 FROM WORK.'ARGUS WITH ADMISSION INFO'n t1;
NOTE: Invalid argument 1 to function INPUTN. Missing values may be generated.
NOTE: Invalid (or missing) arguments to the DHMS function have caused the function to return a missing value.
NOTE: Invalid time value
NOTE: Invalid argument to function INPUT. Missing values may be generated.
NOTE: Table WORK.'ARGUS WITH DURATION'n created, with 7585 rows and 5 columns.
36 QUIT;
Remark: Inputn function works for the HOURS_NUMERICvariable, and copied into the DHMS-funtion it raises these warnings.
Question: what do I do wrong?
Any help will be appreciated,
Regards, Hans Troost
This works;
data want; d="3-11-2016"; t="9:48"; date_time=dhms(input(d,ddmmyy10.),hour(input(t,time5.)),minute(input(t,time5.)),0); format date_time datetime.; run;
You only need the date_time row. Of course, this assumes your data is right, if there are invalid values then it will fail as with any logic.
This works;
data want; d="3-11-2016"; t="9:48"; date_time=dhms(input(d,ddmmyy10.),hour(input(t,time5.)),minute(input(t,time5.)),0); format date_time datetime.; run;
You only need the date_time row. Of course, this assumes your data is right, if there are invalid values then it will fail as with any logic.
v00013 is probably a numeric time variable, so it is wrong to use INPUT function.
You can define:
hh = hour(v00013); mm = minute(v00013); ss = second(v00013);
then:
DateTime = dhms(v00012,hh,mm,ss);
Thanks Shmuel for your fast reply.
The TIME value is a string and not numeric. So I tried RW9's solution and that works fine for me.
Regards, Hans
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 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.