Time Variable Difference - hard time

[NareshAbburi]

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.

[DBailey]

You would use

SELECT INTCK('MINUTE', MAX_TIME, MIN_TIME)

[NareshAbburi]

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.

[NareshAbburi]

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

[PGStats]

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

[NareshAbburi]

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.

19        

20         GOPTIONS NOACCESSIBLE;

21         %LET _CLIENTTASKLABEL=;

22         %LET _CLIENTPROJECTPATH=;

23         %LET _CLIENTPROJECTNAME=;

24         %LET _SASPROGRAMFILE=;

25        

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

[PGStats · Accepted solution]

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)

PG