topic Re: Enterprise Guide DHMS Date Time concatenation in SAS Programming

Enterprise Guide DHMS Date Time concatenation

HansTroost — Tue, 14 Feb 2017 10:51:08 GMT

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

Re: Enterprise Guide DHMS Date Time concatenation

RW9 — Tue, 14 Feb 2017 11:04:30 GMT

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.

Re: Enterprise Guide DHMS Date Time concatenation

Shmuel — Tue, 14 Feb 2017 11:09:04 GMT

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);

Re: Enterprise Guide DHMS Date Time concatenation

HansTroost — Tue, 14 Feb 2017 11:42:23 GMT

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

Re: Enterprise Guide DHMS Date Time concatenation

HansTroost — Tue, 14 Feb 2017 11:43:16 GMT

Thanks RW9. Works fine for me. Hans