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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.