topic Re: Time Variable Difference - hard time in SAS Enterprise Guide

Time Variable Difference - hard time

NareshAbburi — Tue, 04 Nov 2014 17:49:42 GMT

Hi,

I'm trying to find two time variable difference using below query. but it is throwing me the following error. Can someone let me know what mistake I did.

Please help me.

QUERY:

PROC SQL;

CREATE TABLE WIS.TEMP7 AS

SELECT INTCK('MINUTE', 'MAX_TIME'T, 'MIN_TIME'T) FROM (

SELECT MAX(TIME) AS MAX_TIME, MIN(TIME) AS MIN_TIME,FILEID, DT FROM MTG.LOGFILE_WIS_FINAL_UI5_FINAL GROUP BY FILEID, DT);

Result:

5 PROC SQL;

16 CREATE TABLE WIS.TEMP7 AS

17 SELECT INTCK('MINUTE', 'MAX_TIME'T, 'MIN_TIME'T) FROM (

ERROR: Invalid date/time/datetime constant 'MAX_TIME'T.

ERROR: Invalid date/time/datetime constant 'MIN_TIME'T.

18 SELECT MAX(TIME) AS MAX_TIME, MIN(TIME) AS MIN_TIME,FILEID, DT FROM

18 ! MTG.LOGFILE_WIS_FINAL_UI5_FINAL GROUP BY FILEID, DT);

but the table is getting created using second query.

Re: Time Variable Difference - hard time

DBailey — Tue, 04 Nov 2014 17:51:49 GMT

You would use

SELECT INTCK('MINUTE', MAX_TIME, MIN_TIME)

Re: Time Variable Difference - hard time

NareshAbburi — Tue, 04 Nov 2014 18:17:13 GMT

Then I'm getting this error;

ERROR: Function INTCK requires a numeric expression as argument 2.

ERROR: Function INTCK requires a numeric expression as argument 3.

not sure, if i need to convert it.

Re: Time Variable Difference - hard time

NareshAbburi — Tue, 04 Nov 2014 19:06:32 GMT

could someone help with this issue.....?

Re: Time Variable Difference - hard time

PGStats — Tue, 04 Nov 2014 19:15:33 GMT

Post the log, including new code and error(s). - PG

Re: Time Variable Difference - hard time

NareshAbburi — Tue, 04 Nov 2014 19:18:32 GMT

below are the two queries that I've tried:

PROC SQL;

CREATE TABLE WIS.TEMP7 AS

SELECT INTCK('MINUTE', MAX_TIME, MIN_TIME) FROM (

SELECT MAX(TIME) AS MAX_TIME, MIN(TIME) AS MIN_TIME,FILEID, DT FROM MTG.LOGFILE_WIS_FINAL_UI5_FINAL GROUP BY FILEID, DT);

14 GOPTIONS ACCESSIBLE;

15 PROC SQL;

16 CREATE TABLE WIS.TEMP7 AS

17 SELECT INTCK('MINUTE', MAX_TIME, MIN_TIME) FROM (

18 SELECT MAX(TIME) AS MAX_TIME, MIN(TIME) AS MIN_TIME,FILEID, DT FROM

18 ! MTG.LOGFILE_WIS_FINAL_UI5_FINAL GROUP BY FILEID, DT);

ERROR: Function INTCK requires a numeric expression as argument 2.

ERROR: Function INTCK requires a numeric expression as argument 3.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

20 GOPTIONS NOACCESSIBLE;

21 %LET _CLIENTTASKLABEL=;

22 %LET _CLIENTPROJECTPATH=;

23 %LET _CLIENTPROJECTNAME=;

24 %LET _SASPROGRAMFILE=;

26 ;*';*";*/;quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

26 ! run;

27 ODS _ALL_ CLOSE;

QUERY:

PROC SQL;

CREATE TABLE WIS.TEMP7 AS

SELECT INTCK('MINUTE', 'MAX_TIME'T, 'MIN_TIME'T) FROM (

SELECT MAX(TIME) AS MAX_TIME, MIN(TIME) AS MIN_TIME,FILEID, DT FROM MTG.LOGFILE_WIS_FINAL_UI5_FINAL GROUP BY FILEID, DT);

Result:

5 PROC SQL;

16 CREATE TABLE WIS.TEMP7 AS

17 SELECT INTCK('MINUTE', 'MAX_TIME'T, 'MIN_TIME'T) FROM (

ERROR: Invalid date/time/datetime constant 'MAX_TIME'T.

ERROR: Invalid date/time/datetime constant 'MIN_TIME'T.

18 SELECT MAX(TIME) AS MAX_TIME, MIN(TIME) AS MIN_TIME,FILEID, DT FROM

18 ! MTG.LOGFILE_WIS_FINAL_UI5_FINAL GROUP BY FILEID, DT);

Re: Time Variable Difference - hard time

PGStats — Tue, 04 Nov 2014 19:34:21 GMT

Column TIME must be CHARACTER, not NUMERIC. Try converting it with

SELECT MAX(INPUT(TIME, TIME8.)) AS MAX_TIME, MIN(INPUT(TIME, TIME8.)) AS MIN_TIME

then, if you want a positive number of minutes, you should use

INTCK('MINUTE', MIN_TIME, MAX_TIME)